Writing code on the fly - TransferSpreadsheet

[IanT]

I want to set up a regular import of data from an excel spreadsheet using transferspreadsheet.

DoCmd.TransferSpreadsheet acImport, 8, "ImportTable", "F:\SalesDatabase\May2012.xlsx", True, "MonthlyImport"

How can I use a variable to write this command on the fly, using the variable to determine which spreadsheet will be imported. The variable will hold the file name, which will be calculated from the currant month and year, eg VarCurrentImport = "May2012".

Many thanks


Ian

[R_Badger]

Try something like this:

dim mnthyr as string
mnthyr=format(date, "mmmyyyy")

DoCmd.TransferSpreadsheet acImport, 8, "ImportTable", "F:\SalesDatabase\" & mnthry & ".xlsx", True, "MonthlyImport"