TransferSpreadsheet with variable File Name

[grime]

I have a parameter query that collects records within a certain date
range. The user inputs the start date and end date. The dates could
be different for a range longer than 1 day, but 90% of the time, they
are the same date.

I have a TransferSpreadsheet macro set up, which works perfectly other
than the fact that I need the file name to contain the date. For
example, if the parameter dates were the same (20070314 and 20070314
for example), the file would be named "learning 20070314.xls" If the
range was longer than 1 day (20070312 and 20070314), the file would be
named "learning 20070312-20070314"

I know I need to use VBA instead of the macro, but how do I pull the
parameters entered as a string to use?

Thanks in advance,

 

[Ken Snell \(MVP\)]

You can still use a macro. Just use an expression for the File Name argument
in the macro action (include the = sign in the expression string for the
argument):

="C:\MyFolder\MyFileName" & Format(Date(), "mmddyyyy") & ".xls"

 

[Steven]

I see your reply to Ken snell about the transfer spreadsheet...I'm wondering if you know how I could make the entire file name variable...it will always be the same format ############_##_##_## where the first 12 are an invoice number, and the others are a date. I'd appreciate any help you have. Thanks.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Ken Snell \(MVP\)]

From where would you get the values to be used for the filename? And are you
running TransferSpreadsheet from a macro or from VBA code?