Copy File Macro

[D Willett]

Hi

Is it possible to create a macro which copies files ( delimited txt ) from one location to another.
I've not used macros before and Google doesn't really show any good examples.
The codes not an issue with filesystemobject, but how do I change, record, create it as a macro?

Dim fso
Dim sfol As String, dfol As String
sfol = "\\Someserver01\console\d-BASE FILES"
dfol = "L:\LeekTransfer"
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next

If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else

fso.CopyFile (sfol & "\*.txt"), dfol
End If

If Err.Number = 53 Then MsgBox "File not found"

[HansV]

You can't use FileSystemObject in a macro, only in VBA code. You can call a VBA function from a macro, though. But why do you want to use a macro in Access?

[D Willett]

Hi Hans, I have a small database which transfers delimited text files via ODBC query and places them in a specified folder, 6 txt files in all.
The database has 6 macro's to create the txt files plus 1 extra macro which runs all the other macros ( RunAllMacros ). The final macro has a shortcut in task scheduler set to run every 10 mins, giving me access virtually live data from our branch 10 miles away. No issues with this, works absolutely superb.
The final step is to transfer the txt files from one server ( 10 miles away ) to the local server ( here ).
I created a VB6 app using the filesystemobject and that works too, but Ihave to manually run the VB6 app which is simply a form and a button.
So, creating a macro to sit at the end of the other six would do this task for me automatically.
So the process would be:

Macro 1 thru 6 ( created the deli.. txt files )
Macro 7 ( the one I want to create to transfer the txt files from server to server )
Run Macro 8 ( Runs all the previous macros - this is the only one that runs )
And there I have full automation.
I just don't know how to create macro 7.

[HansV]

So do you want to call a VB6 application from Macro 7? How would it deal with the button on the form?

Or do you want to transfer the VB6 code to a module in the Access database and call it from Macro 7?

[D Willett]

Option 2 to call it from Macro 7.

[HansV]

In that case, create a VBA function in a standard module instead of a sub. You can call a function from a macro using the RunCode action.
Let's say you create a function

Public Function CopyMyFiles()
...
End Function

In your macro, select RunCode from the Action dropdown.
Enter CopyMyFiles() in the Function name box.

[D Willett]

Cheers Hans
Exactly as I needed it.
Having not worked with Macros before, and getting stuck in coding again after such a long break, the help is and always appreciated.

Regards

Dave