Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 30th, 2003, 01:06 PM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default Writing VBA Code for DOS Commands

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
 
Old July 30th, 2003, 02:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by dgarcia1128
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.
Danny, how exactly are you outputting the file? What code are you using?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old July 30th, 2003, 04:52 PM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default

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
 
Old July 31st, 2003, 02:38 AM
Authorized User
 
Join Date: Jul 2003
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 31st, 2003, 09:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Code:
Kill "S:\Central Claims\AutoGeneratedReports\claims.xls"
which is nice and easy.

Another option (and I haven't checked this works yet) is to turn the warnings off first - using
Code:
DoCmd.SetWarnings False
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
 
Old August 1st, 2003, 10:34 AM
Authorized User
 
Join Date: Jun 2003
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to dgarcia1128
Default

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
 
Old August 2nd, 2003, 09:12 AM
Authorized User
 
Join Date: Jul 2003
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 3rd, 2003, 08:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Run a MS DOS Batch file from Excel VBA Louh Excel VBA 1 March 4th, 2008 10:02 PM
Send Keystrokes to DOS Program from Access VBA Cosmos75 Access VBA 6 May 15th, 2007 06:24 PM
Click even code for open DOS .EXE mohiddin52 Access VBA 4 October 18th, 2006 12:20 PM
How to run DOS file commands using vba? alastair Access VBA 2 August 12th, 2006 03:40 AM
VBA writing an excel file toshesh BOOK: Access 2003 VBA Programmer's Reference 0 January 4th, 2006 07:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.