aespinoza

New Member
Joined
Mar 1, 2017
Messages
11
Hi Guys and Gals

I have a problem that is out of my expertise
I have some VBA working fine doing what it needs however the end goal is to copy a file form a network drive to a local drive, however, the local machine i need this on does have excel installed to fire the VBA form task scheduler. does anybody have the knowledge to convert the below into a batch file or something?
I am aware i will need to define the source and destination differently the aim was to make this easier to update but if has to be hard coded then I'll cross that bridge if it needs to change.

VBA Code:
Dim source As String
Dim destination As String

source = Worksheets("Admin").Range("D3").Text
destination = Worksheets("Admin").Range("D4").Text


Dim oFSO As Object
 
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
Call oFSO.CopyFile(source, destination, False)

Thanks in advance
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You could always just use VBScript. As long as the computer you're running it on has windows, all you need to do is put the code into a file with the extension .VBS and then double click on it from explorer and it should work. VBA needs to be adjusted slight to work as VBS, but I just test the following code and it works:

VBA Code:
Dim oFSO, Source, Destination
Source = InputBox("Source file:")
Destination = InputBox("Destination path:")
If Right(Destination, 1) <> "\" Then Destination = Destination & "\"
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile Source, Destination
 
Upvote 0
An alternative could be a Windows console batch file. If the aim is to copy just one file with a certain frequency this could be a solution.
Open Notepad and type the following:
Xcopy Z:\folder\someotherfolder\filename.xlsx C:\users\aespinoza\documents\ /Q
where Z is the network drive. If path names contain spaces use double quotes:
Xcopy "Z:\folder\someotherfolder\filename.xlsx" "C:\users\aespinoza\documents\" /Q
Save the file on a secure location on your local drive using SaveAs > change dropdown "Text files (*.txt)" into "All files (*.*) > give your file a proper name ending on .cmd
Within task scheduler you can launch this batch file whenever and as many times you want. It runs silently without prompting, if the destination file already exists it will be overwritten.
Although it is kind of "hardcoded" it easily can be changed.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top