469,347 Members | 18,457 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

Export to Excel - Access Overwriting existing files

I have a series of macros running everynight creating tons of excel files (using transfer spreadsheet). I then have another program that zips them up and emails them all over the place. It then deletes the originals. I would like to move away from the emailing and begin pushing the files out directly via the WAN. The problem is I need to DELETE the existing excel files before they are recreated everynight. There seems to be some glitch that botches up the excel file when 'transfer sreadsheet' overwrites or appends existing excel files rather that recreating new ones. Is there a way to send a command from within my macro that will delete the existing excel files before I run the transfer spreadsheet?

Thanks a Bunch!
Nov 1 '06 #1
4 9267
280 Expert 100+
u could issue the kill command to delete a file

kill "somefile.xls"

or u could create a dos batch script which cd's into a directory and delete's all xls files., then run this script via Shell
Nov 1 '06 #2
14,534 Expert Mod 8TB
In case the Kill command doesn't work properly.

You can use the following sub procedure;

Expand|Select|Wrap|Line Numbers
  2. Public Sub delFile(fromPath As String)
  3. Dim fs As Object
  5.     Set fs = CreateObject("Scripting.FileSystemObject")
  6.     fs.DeleteFile fromPath, True
  8. End Sub
Nov 1 '06 #3
Thanks so much I will give it a try.
Nov 2 '06 #4
32,184 Expert Mod 16PB
I couldn't find it in the help under TransferSpreadsheet, but I think that executing it to an already existing file will NOT replace the file but try to add the results to a new worksheet. I'm not sure what happens when the Table or QueryDef used is already listed as a worksheet name.

When using any export function I always use the following code first (which should get you past any such problems) :-
Expand|Select|Wrap|Line Numbers
  1.     On Error Resume Next
  2.     Kill strFileName
  3.     On Error GoTo 0
Obviously, the name of the file (full or relative) is held in strFileName.
Nov 2 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Matt | last post: by
5 posts views Thread by Simon | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.