New to vba and stuck on DIM

[mginty]

Hi, I'm very new to VBA and have been thrown in the deep end a little.
I've had a request that we export our stock list and provide them to a 3rd party on a daily basis.
I've written and tested the basic query and so on but now I need to fine tune it.
I have created a module that will save a text file output:
'------------------------------------------------------------
' StockExport
'
'------------------------------------------------------------
Function StockExport()
On Error GoTo StockExport_Err

' Export Stock Query
DoCmd.TransferText acExportDelim, "StockExportSpec", "qryStockLoc4", "E:\Snow Valley\StockExport.txt", True, ""


StockExport_Exit:
Exit Function

StockExport_Err:
MsgBox Error$
Resume StockExport_Exit

End Function

However, I'd now like to add the date to the end of the filename so I have done:
'------------------------------------------------------------
' StockExport2
'
'------------------------------------------------------------
Function StockExport2()
On Error GoTo StockExport2_Err

' Export Stock Query
Dim txtLocation As
Set txtLocation = "E:\Snow Valley\" & Now() & ".txt"
DoCmd.TransferText acExportDelim, "StockExportSpec", "qryStockLoc4", txtLocation, True, ""


StockExport2_Exit:
Exit Function

StockExport2_Err:
MsgBox Error$
Resume StockExport2_Exit

End Function

My problem is I don't know what to Dim txtLocation As....
What would it be? An object or a data type? Nothing seemed obvious.
I'm also reading up on this so will post if I find the answer.

Many thanks

 

[PHV]

Dim txtLocation As String
Set txtLocation = "E:\Snow Valley\" & Now() & ".txt"

But this will not work as Now() returns illegal characters for pathname.
So, you may try something like this:
Dim txtLocation As
Set txtLocation = "E:\Snow Valley\" & Format(Now(), "yyyymmdd_hhnnss") & ".txt"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mginty]

Thanks, I have tried the:
Set txtLocation = "E:\Snow Valley\" & Format(Now(), "yyyymmdd_hhnnss") & ".txt"
however when I try running the module I receive a
"compile error object required" and
txtLocation = is highlighted from the above code so I am assuming that the line is wrong?
Haven't been able to find much other info so far.
Many thanks

 

[PHV]

Set isn't needed:
Dim txtLocation As String
txtLocation = "E:\Snow Valley\" & Format(Now(), "yyyymmdd_hhnnss") & ".txt"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mginty]

Excellent that's doing what I want!
Thanks for your help.

 

[mginty]

thanks for your help -it's doing what I want.
Many thanks