Need to clear all data from external spreadsheet from within access

[mully1]

Hi, my database imports data from a spreadsheet into a temporary access table. When finished with the temporary access table i use a macro to clear down the table but i also need to be able to clear down the data in the external spreadsheet, from access, if possible. This is because a scheduled cognos report runs weekly from my pc & the output is saved into this (blank) spreadsheet. Then the user uses the db to import the spreadsheet into the DB (weekly)& the DB processes the info as required & then clears down the temp table. I need the spreadsheet cleared down before the scheduled report next runs - is it possible to do this from within access? I tried using a macro - transfer spreadsheet to export the now blank temp table to the spreadsheet but this appends a worksheet to the spreadsheet rather than overwriting it. Any ideas would be really appreciated, thanks.

 

[hjgoldstein]

I am guessing that you import the data from the spreadsheet each time it is required.

I would suggest that instead of this, the spreadsheet is treated as a linked table. You should then be able to manipulate the data in the spreadsheet in any way you want.

Hope this helps.

 

[mully1]

Thanks for your reply hjoldstein. When i use the spreadsheet as a linked table shoudl i be able to clear it down using a delete query? When i try i get the error: Deleting data in a linked table is not supported by this ISAM. (Error 3617)

 

[hjgoldstein]

mully1,
This link

http://support.microsoft.com/kb/904953/

is mentioned in thread181-1232199.

This might help you. It seems to be a known problem with this product combination.

 

[PWise]

Create a template file Set up as the congos import file is
and call it
congosimportTmeplate.xls


After you do the import Just overwrite the import file with the template

filecopy "congosimportTmeplate.xls","congosimport.xls"

 

[mully1]

Thanks pwise - sorry for sounding stupid but do you mean i would use that code in access to overwrite one spreadsheet with the other? Would this be using a macro?

 

[PWise]

You can do it in a macro?

How do you do the the the import export ect