Wondering if it's possible to run DOS commands in my VBA Code...

For example, what I want to do is output a file from access to an excel spreadsheet, which I already do. However, when it comes to re-outputting the new file (same file name), it halts with an error becuase the file already exists out there. So I have to manually delete the file being outputted for the new one to be written in that location. Does anyone know how to do this?

Thanks, any help is much appreciated.

~Danny

__________________
~Danny

Danny, how exactly are you outputting the file? What code are you using?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I have a form setup to query for dates that in turn trigger the following outputting of the file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1Completed-A", "S:\Central Claims\AutoGeneratedReports\Claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1Completed-B", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1Completed-C", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2TotalCompleted-A", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2TotalCompleted-B", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2TotalCompleted-C", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "3TotalNEWClaims-A", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "4TotalPendingManagement-A", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "4TotalPendingManagement-B", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "4TotalPendingManagement-C", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "5TotalsClaimsAmountsCompleted", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "6TotalCompletedbyUSER", "S:\Central Claims\AutoGeneratedReports\claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Stats - 2003 YTD Completed Claims (A or D) with Reason Codes_Cro", "S:\Central Claims\AutoGeneratedReports\claims.xls"
'Export of Check Info Query to "Check Listing.xls" file using same date ranges as "Claims.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Check Info", "s:\Central Claims\AutoGeneratedReports\CheckListing.xls"

at this point, if the file located on s:\Central_Claims\AutoGeneratedReports\claims.xls is existing, then the output will not work, it will create error, but if you first delete the claims.xls file and then you export the data as u see above, then it works fine.

The DOS Commands I have in mind is simply "del s:\Central_Claims\AutoGeneratedReports\claims.xls" but how do I make that reflect in VBA Code...

Thanks again guys...

~Danny

I use these functions for testing for an exsting file, and for deleting a file.

Hope they are what you are looking for.

Test for existing file:

Function Gen_test_file_exists(FilePath) As Boolean
    Dim fs

    Gen_test_file_exists = False
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FileExists(FilePath) Then
        MsgBox "External file " & FilePath & Chr(13) & Chr(10) & Chr(10) & _
        "already exists."
        Gen_test_file_exists = True
    End If
    Set fs = Nothing

End Function


Delete a file:
Function Gen_delete_file(FilePath)
    Dim fs
    'e.g. filepath = "c:\data\projects\RO05558.htm"

        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.deletefile FilePath
        Set fs = Nothing

End Function


Cheers Ray

Another option is to use the kill statement - for example:

which is nice and easy.

Another option (and I haven't checked this works yet) is to turn the warnings off first - using
A warning with that warning statement though - make sure you turn them on at the end of yr function

Steven

I am a loud man with a very large hat. This means I am in charge

Ray & Steven,
   Thank you both for the excellent information. After reading your replies, it all seems to make sense to me. I haven't implemented it yet as I've been working on a couple other projects. I should be implementing this later today or over the weekend. I'll keep you posted of my results. Thanks again.

On the same not of the subject. Is there some kind of listing that I can find do do similar things like this that I would normally do in DOS myself? Such as renaming a file in DOS from within VBA, moving a file to another location, etc.. etc...

Thanks again, I'll keep you posted of my results.

~Danny

The FileSystemObject gives access to the files system commands of VBScript or the Windows Scripting Host. I found details in book 'The Expert Guide to Windows 98' by Sybex but most comprehesive Windows Manuals contain a chapter on scripting.



Cheers Ray

Danny,
To follow up on Ray's suggestion -
there's an excellent FSO reference available at:
http://www.sloppycode.net/fso/

You can also shell out to run programs - including batch files, if there's something DOSsy you need to do that FSO can't do.

just look up "Shell" in VBA help.

Steven

I am a loud man with a very large hat. This means I am in charge