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"
Copy File Macro
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Copy File Macro
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy File Macro
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?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Copy File Macro
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.
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.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy File Macro
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?
Or do you want to transfer the VB6 code to a module in the Access database and call it from Macro 7?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy File Macro
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Copy File Macro
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
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
Cheers ...
Dave.
Dave.