Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 30th, 2003, 01:06 PM
Authorized User
 
Join Date: Jun 2003
Location: Montclair, CA, USA.
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
Reply With Quote
  #2 (permalink)  
Old July 30th, 2003, 02:35 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
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
Reply With Quote
  #3 (permalink)  
Old July 30th, 2003, 04:52 PM
Authorized User
 
Join Date: Jun 2003
Location: Montclair, CA, USA.
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
Reply With Quote
  #4 (permalink)  
Old July 31st, 2003, 02:38 AM
Authorized User
 
Join Date: Jul 2003
Location: Stockton on Tees, , United Kingdom.
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
Reply With Quote
  #5 (permalink)  
Old July 31st, 2003, 09:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
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
Reply With Quote
  #6 (permalink)  
Old August 1st, 2003, 10:34 AM
Authorized User
 
Join Date: Jun 2003
Location: Montclair, CA, USA.
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
Reply With Quote
  #7 (permalink)  
Old August 2nd, 2003, 09:12 AM
Authorized User
 
Join Date: Jul 2003
Location: Stockton on Tees, , United Kingdom.
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
Reply With Quote
  #8 (permalink)  
Old August 3rd, 2003, 08:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 08:05 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.