ImportExport Spreadsheet using Macro *overwrite existing table*

[Lydia Uys]

Hihi, hoping someone can help me, I am writing a macro where I am importing an Excel file (pretty standard -nothing fancy) after the macro has run the table has duped. Meaning I didn't get the message "Overwrite existing table or query file" like you get when you import a file manually? :banghead: I am importing prior day data and use that then in queries etc but only data for a spesific date.

Hoping you can help me.
Lydia

 

[David R]

Are you saying it imports as "TableLinkData1" instead of "TableLinkData" like you expected? This is a feature/bug. You should delete (rename, archive, whatever) the old table first before reimporting.

If you're saying it overwrote without warning you first, I'd be VERY surprised. What version of Access?

 

[Lydia Uys]

I am using Access 2010. No warning thought the file just overwrote the table untill I looked and noticed I had 2 days of date in one table. I've used the Macro Builder and selected:
!ImportExport Spreadsheet
Transfer Type: Import
Spreadsheet Type: Excel Workbook
Table Name: "XXX"
File Name: c:\ XXX\XXX\XXX.xlsx
Has field names: yes.

Not sure If I somehow have to inclued another step in this macro. Basically instead of manually importing an excel file I would like to automate the import ;-)

 

[David R]

When you say "I had 2 days of data in one table" do you mean it was twice as long as expected? That's an append, not an overwrite, and the solution is almost the same.

Build a delete query "DELETE * FROM ImportedTable;" and run that as an earlier step in your macro (I don't do Macros so I don't know the syntax, but it's probably RunQuery or OpenQuery).

http://msdn.microsoft.com/en-us/library/office/bb177401(v=office.12).aspx has more information on why this is doing what it's doing.