TransferSpreadsheet Macro

[dtaylor]

hello all,

using a the TransferSpreadsheet option in a macro is there a way to make the import file location a variable??

like maybe pick up the file location from a table... i am currently using a routine and form to accomplish but am trying to cut down the size and was thinking of using a macro for this improt..

any ideas..

Thanks

Dan

 

[Norie]

That shouldn't be a problem but using VBA rather than a macro

e.g.

Sub ImportData()

Dim ImportLocation As String

ImportLocation = "C:\MyDirectory\MySpreadsheet.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", ImportLocation

End Sub

To pick up the filename from a table shouldn't be too hard either.

Assuming you have a table called 'MyTableOfLocations' with fields 'DestTable' (the name of the destination table) and 'ImportLocation' (the full path location of the spreadsheet you are importing.

For each record the following should import every spreadsheet into the appropriate table:

Sub ImportFromLocationTable()
Dim db As Database
Dim rst as Recordset

Set db=CurrentDb

Set rst = db.OpenRecordset("MyTableOfLocations")

rst.MoveFirst

While Not rst.EOF

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, rst.Fields("DestTable"), rst.Fields("ImportLocation")
rst.MoveNext

Wend

Set rst = Nothing
Set db = Nothing

End Sub

This illustrates the concept, I think, and I hope you understand it.

 

[dtaylor]

Hi Norie - thanks for the reply..
yes i am currently using this method except coded in ADO -- my db is loaded with tables and forms and was trying to cut out the fat... looking at all angles...

i have tried to accomplish with the macro but to no avail...no options for variables... i am going to stick with my current routine and look for other ways to reduce...


thanks for your input...

Dan