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

How can I integrate DOS batch files in MS Access?

P: 5
I need to transfer entire excel files (not just a range of data) from one directory to another using an Access Macro because I want to intersperse them with my other macros in Access. I want to run one macro that will call other macros. So I am thinking I need to figure out how to run DOS Batch files from Access and incorporate them in my Access dB. I think I have to use Shell / VBA.

If, for example, my batch file was Transfer01in.bat, the code in Access might look like
call Shell ( Environ$ ( "COMSPEC" ) & " /c c:\Transfer01in.bat", vbNormalFocus)

And my batch file, Transfer01in.bat, would contain
copy "C:\CD BI Model\2010 07\*.*" "C:\CD BI Model\Version E\*.*" /y

(Environ$("COMSPEC") returns the path to Command.com on the machine and the "/c" argument makes sure that the Dos window is automatically closed when the batch file finishes executing.

I need help to integrate it all into my Access database file.
Aug 3 '10 #1
Share this Question
Share on Google+
17 Replies


Expert 100+
P: 107
It sounds like you are very close to your solution.

You can make an Access Macro run a function that contains whatever code is needed to perform the directory copy.

The macro action is 'RunCode'.

If your code consists of a single function call to a built-in function, just put it in the 'Function Name' argument.

If you need a custom function then you could place it in a separate module, making it easier to copy over to a new Access database.
Aug 3 '10 #2

ADezii
Expert 5K+
P: 8,706
If you are interested, I have code that will execute any DOS Based Application, and more importantly, will wait until the Shelled Process (DOS App) has finished before resuming code execution. This can all be done from within Access.
Aug 3 '10 #3

P: 5
Yes, please, although my need is only to copy Excel files. As I understand the recommendation written by Steve Kogan, I need to use the RunCode action. But what do I write in Function Name below? I need the details. I am new to VBA.
Aug 3 '10 #4

ADezii
Expert 5K+
P: 8,706
If all you need to do is to Copy Files, then this can be done entirely withing Access via the FileCopy VBA Statement. There is no need to Shell to DOS, execute a Function Call, etc.
Aug 3 '10 #5

Expert 100+
P: 107
It seems Shell isn't recognized as a function, which seems odd.

You could create a function to call your batch file, then put the function name followed by parentheses in the 'Function Name' area at the bottom.

For example, place this in a module:

Expand|Select|Wrap|Line Numbers
  1. Public Function RunMyBatch()
  2.     Call Shell("c:\test\test.bat")
  3. End Function
  4.  
In 'Function Name' put:
RunMyBatch()
Aug 3 '10 #6

ADezii
Expert 5K+
P: 8,706
The problem with using Shell() in this context is that you are copying several Files as indicated (*.*) and that Shell() executes code asynchronously which means that code following the call to Shell() may execute before it has completed copying all the Files. I'll post a solution to this obstacle tomorrow as indicated in Post #3.
Aug 4 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
We have an article (ShellWait() Function) on synchronous calling of Shell.

I would pay close attention to something else ADezii posted earlier though. Copying files doesn't depend on anything as drastic as batch files. Incidentally, Batch files are pretty archaic now. CMD files (that use CMD.EXE rather than COMMAND.COM) are more powerful and more current. I'm still not convinced you need either mind.

Whatever you need to do, I would not recommend using Access macros (Sorry Steven). These are also very restricted and frankly will take you away from finding support if you have problems. Most experts steer clear of them. Why learn macros when VBA does all you need in a way that is better in all aspects.

It's possible to create and manage any CMD files you need to run, from within your database. As I say though, there seems little point to go to such complexity for this issue when it can be handled with some simple VBA.
Aug 4 '10 #8

ADezii
Expert 5K+
P: 8,706
I was along the same trend of thought as you until I double checked the Copy Operation as indicated in Post #1 which involves the use of Wildcards:
Expand|Select|Wrap|Line Numbers
  1. copy "C:\CD BI Model\2010 07\*.*" "C:\CD BI Model\Version E\*.*" /y
P.S. - There is still the issue of Asynchronous Code Execution with a simply File Copy operation which may be a problem.
Aug 4 '10 #9

ADezii
Expert 5K+
P: 8,706
  1. Copy-N-Paste the following Declarations into a Standarrd Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    2. Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
    3.                         ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    4. Public Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
    5.                         ByVal dwMilliseconds As Long) As Long
    6.  
    7. Public Const SYNCHRONIZE = &H100000
    8. Public Const INFINITE = -1&
  2. Copy-N-Paste the following Code, then Execute it substituting the PATH to you Batch File in Line #4:
    Expand|Select|Wrap|Line Numbers
    1. Dim lngProcessID As Long
    2. Dim lngProcessHandle As Long
    3. Dim lRet As Long
    4. Const conPATH_TO_BATCH_FILE As String = " C:\Test\Map.bat"
    5.  
    6. lngProcessID = Shell(Environ$("Comspec") & " /c" & conPATH_TO_BATCH_FILE, vbNormalFocus)
    7.  
    8. If lngProcessID <> 0 Then
    9.   'Get a handle to the shelled process.
    10.   lngProcessHandle = OpenProcess(SYNCHRONIZE, 0, lngProcessID)
    11.  
    12.   'If successful, wait for the application to end and close the handle.
    13.   If lngProcessHandle <> 0 Then
    14.     lRet = WaitForSingleObject(lngProcessHandle, INFINITE)
    15.     CloseHandle (lngProcessHandle)
    16.   End If
    17.     'You will not see this Dialog until the DOS Window is Closed
    18.     MsgBox "Just terminated.", vbInformation, "Shelled Application"
    19. End If
  3. Now, when you Execute the Code, all your Files will be copied to the Destination Folder. Code Execution will not Resume until this Operation has completed, and the DOS Window closed.
  4. Any questions, problems, please feel free to ask.
Aug 4 '10 #10

NeoPa
Expert Mod 15k+
P: 31,769
In my previous post I referred to asynchronous when I meant synchronous (I've changed it now). I would expect synchronous to be required in this case, hence the ShellWait() link.

FileCopy() is indeed limited to a single file at a time. This is easy enough to get around in most instances though, as you've proven on a number of occasions. If the requirement is not properly covered, or even if the OP simply prefers otherwise, then CMD files can be invoked synchronously or asynchronously (delault Shell() call) to accomplish what's required.
Aug 4 '10 #11

P: 5
After thinking about it and reading comments posted here and at other sites, I see that the FileCopy command would work well for me.
FileCopy "C:\ABC\*.*" "C:\DEF\*.*"
Aug 6 '10 #12

NeoPa
Expert Mod 15k+
P: 31,769
Except FileCopy() cannot be used with wildcards (at least not in Access 2003). See post #11 or the related Help section.

It can be got around, but would involve further coding.
Aug 6 '10 #13

P: 5
OK. Summary so far...
I used the batch file approach by using the "RunApp" action. But since Access won't wait for the Shell command to complete, I insterted a MsgBox to manually halt and continue the process. However, I don't want to do this (as I have several similar sequential processes). I need help to weave in the ShellWait() mentioned earlier.
Aug 6 '10 #14

NeoPa
Expert Mod 15k+
P: 31,769
What help do you need? I should be able to answer, assuming a sensible question.
Aug 6 '10 #15

P: 5
Where do I enter ShellWait() ?
Remember, I need it to be in the macro section.
Aug 6 '10 #16

NeoPa
Expert Mod 15k+
P: 31,769
Merch: Where do I enter ShellWait() ?
Remember, I need it to be in the macro section.
What does "the macro section" mean to you? I can only guess that you're trying to imply that you've stated earlier that VBA code is not an option for you. If you did, I missed it. If you did, would you explain why.

I cannot tell you how to put VBA code in as an Access macro. That wouldn't make any sense. I can tell you generally how to go about doing this, but not with an impossible stipulation such as above.

As far as I can see, your previous comments have referred to VBA quite heavily, so maybe there's a certain misunderstanding of the terms here. Perhaps you could clear this up for us and we can proceed :)
Aug 6 '10 #17

Expert 100+
P: 107
Macros can run a function using the RunCode action. The argument for the RunCode action is 'Function Name'. In the 'Function Name' argument you place the function call with parameters placed between parentheses. For example the 'Function Name' argument can be:
Expand|Select|Wrap|Line Numbers
  1. MyFunction(strArg1, lngArg2)
If you want to run VBA code from a macro, you would put the code into a function and call that function using the RunCode command.

If you need the macro to exist without requiring any VBA coding then you are restricted to built-in functions and macro actions.
Aug 6 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.