VBScript to Export Excel Worksheet to Access Table

[dskelley]

I'm looking for a VBScript that exports a specified Excel worksheet, clearing and replacing a table as is in an Access database. I'm not well versed in this, and am hoping someone can provide a template that all I have to is plug file names into (the files will sit local to each other in the same folder). Thanks.

 

[AppStaff]

' Deletes Table
DoCmd.DeleteObject acTable, [Table Name]
'Imports Table
DoCmd.TransferSpreadsheet acImport, , [TableName], [FileName], [Has fields= True or False. Only true or false should go here ]

I might suggest another alternative to this. Link the spreadsheet as a table in access. The spreadsheet can change or be replaced at anytime and no importing is required. You'll just need to ensure the same name of the spreadsheet.

 

[dskelley]

Thanks for the info. I should clarify that importing is currently already being done using a macro as a temporary workaround, and linking is not an option for reasons I won't get into. I specifically need an external, independent VBScript that will transfer a worksheet called Data from the Excel spreadsheet to a table called Data in the Access data by essentially doing what the macro I have set up is doing, clearing everything in the Data table and replacing it with the modified data, keeping the column headings, without having to open up either application. Thanks.