By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,408 Members | 1,796 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,408 IT Pros & Developers. It's quick & easy.

Export to Excel - Access Overwriting existing files

P: 2
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
Share this Question
Share on Google+
4 Replies


pks00
Expert 100+
P: 280
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

MMcCarthy
Expert Mod 10K+
P: 14,534
In case the Kill command doesn't work properly.

You can use the following sub procedure;

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub delFile(fromPath As String)
  3. Dim fs As Object
  4.  
  5.     Set fs = CreateObject("Scripting.FileSystemObject")
  6.     fs.DeleteFile fromPath, True
  7.  
  8. End Sub
  9.  
  10.  
Nov 1 '06 #3

P: 2
Thanks so much I will give it a try.
Nov 2 '06 #4

NeoPa
Expert Mod 15k+
P: 31,561
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.