How can I integrate DOS batch files in MS Access?

[Merch]

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.ba t, the code in Access might look like
call Shell ( Environ$ ( "COMSPEC" ) & " /c c:\Transfer01in .bat", vbNormalFocus)

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

(Environ$("COMS PEC") 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.

[Steven Kogan]

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.

[ADezii]

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.

[Merch]

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.

[ADezii]

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.

[Steven Kogan]

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:

Public Function RunMyBatch()
    Call Shell("c:\test\test.bat")
End Function

In 'Function Name' put:
RunMyBatch()

[ADezii]

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.

[NeoPa]

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.

[ADezii]

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:

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.

[ADezii]

1. Copy-N-Paste the following Declarations into a Standarrd Code Module:
   

   Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
   Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
                           ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
   Public Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
                           ByVal dwMilliseconds As Long) As Long
   
   Public Const SYNCHRONIZE = &H100000
   Public Const INFINITE = -1&

2. Copy-N-Paste the following Code, then Execute it substituting the PATH to you Batch File in Line #4:
   

   Dim lngProcessID As Long
   Dim lngProcessHandle As Long
   Dim lRet As Long
   Const conPATH_TO_BATCH_FILE As String = " C:\Test\Map.bat"
   
   lngProcessID = Shell(Environ$("Comspec") & " /c" & conPATH_TO_BATCH_FILE, vbNormalFocus)
   
   If lngProcessID <> 0 Then
     'Get a handle to the shelled process.
     lngProcessHandle = OpenProcess(SYNCHRONIZE, 0, lngProcessID)
         
     'If successful, wait for the application to end and close the handle.
     If lngProcessHandle <> 0 Then
       lRet = WaitForSingleObject(lngProcessHandle, INFINITE)
       CloseHandle (lngProcessHandle)
     End If
       'You will not see this Dialog until the DOS Window is Closed
       MsgBox "Just terminated.", vbInformation, "Shelled Application"
   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.

[NeoPa]

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.

[Merch]

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\*.* "

[NeoPa]

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.

[Merch]

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.

[NeoPa]

What help do you need? I should be able to answer, assuming a sensible question.