471,892 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,892 software developers and data experts.

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 9615
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,470 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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.