Running a batch file from command prompt using VBA

Hi,

I know this can be done but I'm a novice at VBA. I have a batch file AutoLoop.bat which I run from command prompt at the moment. The idea is to run a VBA routine behind the onClick event of a command button in Access which would run this batch file in command prompt automatically. Say if the batch file path is I:AUTOGENLOOPGENAutoLoop.bat,
what VBA code can be used.

Thanks for any help.


Post your answer or comment

comments powered by Disqus
Can someone please show me how to execute a batch file from ACCESS command button?

Hi Guys,

This surely cant be as difficult as I am finding it

I have a batch file that works perfectly, which FTP's files from my local machine to one of my servers. All I want to do is have a step in my Microsoft Access Macro which runs this batch file

Is there a way of doing this?

Thanks

Paul

I'm trying to run a batch file which exists at "c:sscsblock1hosthold.bat". I've tried the following code:

Call Shell ("C:SSCSblock1HOSTHOLD.BAT", vbMaximizedFocus)

& the RunApp macro command, but I can't get the damn batch file to execute? For about half a second, the dos window appears, but thats it.

I'm using Access 2000 with Windows 2000.


Please let me know of any ideas you guys may have

TIA


Tim C

Hi,
I am using MS Access 2007 and I am trying, from my VBA code, to execute (or run) a Batch file. Now the batch foile works, but not from my code.
I used to be able to just do this --> Call Shell("S:AccountingAPTex FilesFile List Generator.cmd ", 1)
ANd it would runit. But using this I get a message telling me that it is an invalid procedure call. So upon looking around on the net I found tha ton place said that with MS Access 2007 you need to do this:
Call Shell(Environ$("COMSPEC") & " /c S:AccountingAP1099 ITEMSW9 FolderCorrected 2011 W9File List Generator.cmd ", vbNormalFocus)
But while it does not error out it dooes noting eitheer. What the batch file does, when it works, is produces a list of all the file names in a folder.
Any ideas would be relly appreciated.
Thank you
itm

How to programmatically run a batch file ( MS DOS ) file. What is the VB instruction used to run this file type?

Thanks,

I have the following batch file D:TelewestTextFilesINLO_DATADATTXT.BAT

The batch file converts .dat files to .txt file extensions. The .dat files are housed in the same directory as the batch file.

The db is housed under C: drive.

Behind a command button I have

Call Shell ("D:TelewestTextFilesINLO_DATADATTXT.bat"' , 1)

The DOS windows opens a split second but it doesn't executes the batch file. Running the batch file on its own works.

Can anyone enlighten me of what I'm doing wrong?

I'm having trouble with some code to backup my database. I thought I'd just have some code call a batch file that copies the backend to a new location. The problem is that when I call the batch file, it opens and runs from c:documents and settings%username%, so the relative paths in my batch file do not work.

Is there a way to have access tell run the batch file from command prompt in it's actual location? Or maybe it would be better just to copy the back end from within the VBA code. But I don't know how to do that, either. Maybe there's a way to use the Backup Database function under Tools/Database Utilities? With Sendkeys maybe? (but I don't know the code to do it).

Hi All, I am using the following code to execute a batch file. The batch file runs a SQL script which in turn produces a CSV file.

My batch file runs but doesnt produce the CSV file. The batch file works if triggered manually.

Any help much appriciated.


	Code:
	 
Dim wshThisShell As WshShell
Dim lngRet As Long
Dim strShellCommand As String
Dim strBatchPath As String
wshThisShell = New WshShell
strBatchPath = "C:RemoteListUpdateClientID1.bat"
'the path for the batch file you're using 
strShellCommand = """" & strBatchPath & """"
'the ridiculous number of quotation marks is necessary
'when there is a space in one or more of the folder names
lngRet = wshThisShell.Run(strShellCommand, vbNormalFocus, vbTrue)
'set 3rd argument above to vbFalse for asynchronous
'execution of the batch file.



Hi,

I am trying to run a batch file from a button click on an Access form. I am using 'Call Shell("c:test.bat", vbNormalFocus) but that's not working. What I want the app to do is automatically run an FTP session to upload an HTML file without human intervention. The bat file kicks off an FTPscript which has the userid and password. Any help would be greatly appreciated.

Thanks..Russ

hello,

i need to put multiple urls that i have made changes to into a database. i am hoping someone can help me do this efficiently.

i watched an associate of mine populate the database by running a batch file, and then importing the contents of it into the db; however, this associate has since left us, and no one else in my office knows much about this.

please help!

Thanks, Jh

Hi all
I need to run a certain file from the form
I create a button as follows
Select in the categories "Applications" then select "Run Ms Word"
I have tested it and it open Word but not even a blank page
I then open the code as seen below and want to insert the filename but do not know where. Can someone please help me?

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

Dim oApp As Object

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

Exit_Command35_Click:
Exit Sub

Err_Command35_Click:
MsgBox Err.Description
Resume Exit_Command35_Click

End Sub

The file is - D:PanelbeatersPb Manual.doc

Hi all

I have a batch file that runs when I click on a command button on a form. It makes a backup of all my files and resources for the database. When I click the button, Access gives me a warning saying:

Opening G:PayrollDataCenterDataResourcesPDCbak.bat
Some files can contain viruses or otherwise be harmful to your computer.
It is important to be certain that this file is from a trustworthy source.

Would you like to open this file?

Yes/No

Is there a way to keep this window from popping up?

I've tried "DoCmd.SetWarnings False" but it didn't work?

Any Ideas??

Thanks,

Adam

I want to run a macro using a batch file. I would like to know the exact format that shld be used for the macro name to do this. The batch file opens the dtata base by using the following path:

C:Program FilesMicrosoft OfficeOfficeMSACCESS.execoding.mdb(What should I type here for the macro to be run?)

The name of the macro is beeper. Thanks.

I am creating a batch in an Access Query to decompress files that come in from an FTP site. When I right click on the query and export it to a TXT file I get all the line items of the that I want without the header of the query field name. When I run the following command;

DoCmd.TransferText acExportDelim, "Decompress2 Specification", "qry_FileNameChange2", "J:operationsDecompress2.txt", True
(Where "Decompress2 Specification" is the name of the Spec)
I get the name of the field as the first line item and then all the line items I am looking for. I use the same Spec when I manually run it or when I run it from the code. Any idea how I can get rid of the first line item when I run it from the code?

I run a batch file to change the txt extension to bat.

Hello there!

I've searched the forum for help on this but can't seem to find what I need. I know I can run batch files from Access but I seem to be hitting a brick wall. Maybe it's because I a bit fick or maybe because it's Friday but I'm just completely stuck.

I have the basic syntax down OK and can get the calculator to execute using this example.

Sub Test()
Dim RetVal
RetVal = Shell("C:WINNTSYSTEM32calc.exe", 1)
End Sub

Moving ahead from there I can get X:DIRECTORYSUBDIRECTORYPKZIP.EXE to run also. But when I try to run a batch file with the command PKZIP -A OTTAWA Ot*.snp inside it bums out, or even if I just call a batch file with PKZIP.EXE as it's only command (just as a test) it changes the format of the sub-directory and I end up with this in the DOS window:

X:DIRECTORYSUBDIRECTORY> (note the addition of the greater than sign) and "PKZIP.EXE is not recognised as an internal or external command, operable program or batch file".

I am obviously screwing up on syntax somewhere but the Access "help" file isn't really helping at all and I don't know where else to turn. Can someone help me figure out what it is that I am missing - or am I just totally off base on what I can do with this Shell command thingie.

Comments appreciated. Thanks in advance and apologies if I am covering old ground.

Recently I created a batch fie to make it easier for coworkers to "install" the front end of my db. I am wanting to take this idea a step further and not sure how to proceed.

First, I want an access db that I can open on a cd-rom where a user can browse to the directory they want the back end saved.

Then I want access to create a the batch file using that location in that location.

then I want the batchfile to run copying all the files to the server location specified.

We have created a number of Training modules using Power Point. I would like to run the modules by creating a command button and assigning a ppt file to each button. Can any one help me with the switches I need to not only open Power Point but run either a ppt or pps file.

Thanks

Hi,

I have created a very simple backup/restore system, using 2 batch files (one to copy backend to the root folder, and the other to copy it back to it's proper location).

Anyway, I am looking for a way to have the user asked where they would like to put the backup file.

The more I think about it, the less I think I need a batch file at all....but anyway....I was looking to do it from a form, so the user can put in the path in a text box, click Backup, and it will copy the backend over to that path.

TIA

Hello,

I'm trying to ensure that a particular workbook (named Despatch_Schedule) on a particular machine is closed at certain times of the day. The workbook is populated with data from an SOP, and needs to be closed while the update takes place.

I have made a new workbook and given it a Workbook_Open() macro which closes Despatch_Schedule. I planned to schedule a batch file to open this new workbook for me from the machine it is on, which in turn will close Despatch_Schedule, save the changes and then close itself. This works fine as long as I open the new workbook myself. If I try to open the new workbook from the batch file, it does not.

The batch file simply opens the workbook like this:


	Code:
	C:
CDProgram FilesMicrosoft OfficeOffice10
Excel.exe "Sellit-serverrouteonePRD ReportsCloseDespSched.xls"
EXIT

The Wookbook_Open() macro consists of the following:


	Code:
	On Error GoTo OpenWorkBook:
Workbooks("Despatch_Schedule.xls").Activate

OpenWorkBook:
If Err.Number = 9 Then
    Workbooks.Open FileName:="Sellit-serverrouteonePRD ReportsCurrentDespatch_Schedule.xls"
    Resume
End If
Workbooks("Despatch_Schedule.xls").Close True
ActiveWorkbook.Close

If I have my main Despatch_Schedule workbook open and then open my new workbook, it will close it. If I have Despatch_Schedule open and then run the batch file that calls the new workbook, it will open another instance of Despatch_Schedule (i.e. go into the error trap, as if there wasn't an instance of it open), and close that. I didn't think that I could have two workbooks with the same name open at the same time, but I've put an exit sub in the code and had a look, and it does.

I'd be grateful of any assistance that anyone can offer.

Thanks,

Matthew

Hello Access Gurus of the world!!

Any one could help me with this little problem....

How can I open a xls file from access, using some sort of commands or a macro.....actually a macro will be the go.

Ho can I open a xls file using a macro in access

thanks for your help

Hi,

Version (Access): 2003
Version (Word): 2003

Wanted to know if it is possible to run a word macro from a button on my access form that opens the word document.

I have got this code currently (on the access form) - i can open the document correctly by using this code:

Private Sub IWS_Print_Button_Click()
On Error GoTo Err_IWS_Print_Button__Click
Dim objword As Object
Set objword = CreateObject("Word.Application")
objword.Visible = True
objword.Documents.Open ("C:Documents and SettingsNeilMy Documentssad.doc")
Exit_IWS_Print_Button__Click:
Exit Sub
Err_IWS_Print_Button__Click:
MsgBox Err.Description, , "User Request"
Resume Exit_IWS_Print_Button__Click
End Sub

However, i want to be able to run the word macro code (shown below) automatically after opening the document, in order to print it - unless there is some code that already enables me to do this that i have missed.

Could all you brill people help by telling me where the code should go and any extra lines that i need to add to the above code.

This is the macro code that i want to run upon opening the document:

Sub Printing()
'
' Printing Macro
' Macro recorded 31/08/2009 by Neil
'
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=False, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End Sub

Hey Folks,

I have a few Access database applications that are basically just some VBA modules that are kicked off by a macro trigger ... at the end of some of these modules I kick off a shell command that essentially runs a batch file resident on the machine. These apps have worked very well in the last few years, and I am wondering is there a way to perhaps embed the batch file somehow into the Access database so that I don't need that batch file as an extra file required on the system? If not, is there perhaps a way in which I can run the commands that are in the batch file but in VBA? Some of the batch files are fairly lengthy and they basically perform functions such as copying and renaming files across networked drives, ftping certain local files, unzipping certain files, etc.

Thanks for any help and ideas,

Joe

Is it possible without using bcp or SSIS to create a csv file from a query or a table in SQL Server 2005?

Running a bcp requires xp_cmdshell, due to safety protocols i can't use that.
Creating a SSIS package requires cooperation from a different department i don't want that.

So i am looking for a different solution.

Thanks.

hello everyone, i really need some detailed help as the deadline is approaching and I need to find a solution for this. Any help would be greatly appreciated

I currently have a batch file that ftps a text file from a Red Hat Linux Server to my W2k C:. I would like to make another command in the batch file that imports this text file into an existing access table. I would like the text file to repopulate the table everytime it is imported. I do not want the data added on to the existing data in the table.

Thank you for your time and insight.!!!


Not finding an answer? Try a Google search.