VBA equivalent without excel

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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Dan_W

Board Regular
Joined
Jul 11, 2018
Messages
152
Office Version
365
Platform
Windows
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
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
771
Office Version
2013
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,345
Messages
5,486,323
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top