[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2350
  • Last Modified:

File Copy Using VBA In Excel 2010

Hi  I have wrote this code to copy the file by reading the text file with bunch of computer name si n it and pick the associated file from my c drive and copy on to destination computer with a common name,

I need some error handling solution so if the code is unable to reach the destination computer then it must log the error in a separate file with just computer name and continue with the rest of the list.

I am able to copy the file successfully if the computer is on but when its not reachable the whole code failes PLEASE HELP EXPERTS

--------------
Const ForReading = 1
Const OverwriteExisting = True

'constants defined we next create an instance to open the first file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Computers.txt")

' With the text file open next step is to read through the first file line-by-line until we’ve reached the end of the file
Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine

' constructing a UNC path, & new file name at destination substituting the value of the variable strComputer for the computer name

strRemoteFile = "\\" & strComputer & "\C$\CONFIG.TXT"
strRemoteEXE = "\\" & strComputer & "\C$\BiosConfigUtility.EXE"

' constructing a UNC path, substituting the value of the variable strFile for the Source File name
strSourceFile = "\\holit126\Temp\" & strComputer & ".txt"

objFSO.CopyFile strSourceFile, strRemoteFile, OverwriteExisting
objFSO.CopyFile "\\holit126\Temp\BiosConfigUtility.EXE", strRemoteEXE, OverwriteExisting

MsgBox ("gone through")

Loop
   HOLIT126.txt UNLGTCC001.txt Computers.txt Copy-of-Autocopy-new.txt
0
immipathan
Asked:
immipathan
1 Solution
 
sshah254Commented:
Use Error Handlers ... examples are here ... http://www.cpearson.com/excel/errorhandling.htm

SS
0
 
VipulKadiaCommented:
Rewrite your code as follows :

---------------------------------------------------------------------------------

On Error Resume Next

Const ForReading = 1
Const OverwriteExisting = True

'constants defined we next create an instance to open the first file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Computers.txt")

' With the text file open next step is to read through the first file line-by-line until we’ve reached the end of the file
Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine

' constructing a UNC path, & new file name at destination substituting the value of the variable strComputer for the computer name

strRemoteFile = "\\" & strComputer & "\C$\CONFIG.TXT"
strRemoteEXE = "\\" & strComputer & "\C$\BiosConfigUtility.EXE"

' constructing a UNC path, substituting the value of the variable strFile for the Source File name
strSourceFile = "\\holit126\Temp\" & strComputer & ".txt"

objFSO.CopyFile strSourceFile, strRemoteFile, OverwriteExisting
IF Err.Number<>0 THEN
    ' Write a code to save ComputerName in LOG file
End If
objFSO.CopyFile "\\holit126\Temp\BiosConfigUtility.EXE", strRemoteEXE, OverwriteExisting
IF Err.Number<>0 THEN
    ' Write a code to save ComputerName in LOG file
End If

MsgBox ("gone through")

Loop
0
 
immipathanAuthor Commented:
coool
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now