469,352 Members | 1,653 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

How can I integrate DOS batch files in MS Access?

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
17 9900
Steven Kogan
107 Expert 100+
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
8,800 Expert 8TB
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
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
8,800 Expert 8TB
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
Steven Kogan
107 Expert 100+
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
In 'Function Name' put:
Aug 3 '10 #6
8,800 Expert 8TB
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
32,184 Expert Mod 16PB
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
8,800 Expert 8TB
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
8,800 Expert 8TB
  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
    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"
    6. lngProcessID = Shell(Environ$("Comspec") & " /c" & conPATH_TO_BATCH_FILE, vbNormalFocus)
    8. If lngProcessID <> 0 Then
    9.   'Get a handle to the shelled process.
    10.   lngProcessHandle = OpenProcess(SYNCHRONIZE, 0, lngProcessID)
    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
32,184 Expert Mod 16PB
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
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
32,184 Expert Mod 16PB
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
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
32,184 Expert Mod 16PB
What help do you need? I should be able to answer, assuming a sensible question.
Aug 6 '10 #15
Where do I enter ShellWait() ?
Remember, I need it to be in the macro section.
Aug 6 '10 #16
32,184 Expert Mod 16PB
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
Steven Kogan
107 Expert 100+
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.

Similar topics

2 posts views Thread by Herb Stevenson | last post: by
7 posts views Thread by erniedude | last post: by
4 posts views Thread by ScoobyDoo | last post: by
6 posts views Thread by Charles Neitzel | last post: by
2 posts views Thread by Stephen | last post: by
reply views Thread by Chris Rebert | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.