[RESOLVED] VBA to VBScript

[Kubull]

I've been trying to develop a File distribution script, with some success, I've managed to get the script to copy one file and then distribute it into the many folders, but now I'd like it to record the folders it has skipped due to errors IE: Error 76 "File path not found". But I can't get it to work, Any suggestions?

Workbooks.Add
Range("A1").Value = "Folders Omitted"
Range("A1").Font.Bold = True
ActiveCell.Offset(1, 0).Select

Dim fso, f, fc, f1, SourceFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFile = fso.GetFile("E:\Test Area\NormalUpdates\NormalPM\normal.dot")
Set f = fso.Getfolder("E:\Test Area\")
Set fc = f.SubFolders
For Each f1 In fc

If InStr(f1.Name, ",") > 0 Then
On Error Resume Next
SourceFile.Copy f1 & "\Databases\normal_PM\"
If Err.Number <> 0 Then
Select Case Err.Number ' Evaluate error number.
Case 76 ' "Path not found" error.
ActiveCell.Value = f1
ActiveCell.Offset(1, 0).Select
'LogInformation f1
Err.Clear
Case Else
ActiveCell.Value = f1
ActiveCell.Offset(1, 0).Select
End Select
Else
End If
On Error GoTo 0
End If
Next

Objexcel.ActiveWorkbook.SaveAs("E:\Test Area\Noticeboard\NormalUpdates\omitted files.xls")
ObjWorkbook.Close

[Hack]

Are you trying to get this to work in Excel VBA or VBScript?

[Kubull]

Need it to run via VBScript - .vbs file so as to run with Win server 2003 scheduled tasks over night.

[kevchadders]

Have you checked to see if it does step into the 'Case 76' part in code?

[Kubull]

Thats part of the problem, when I run it through VBA, the full script works like a charm, as soon as I change it to VB Script (.vbs) file, it doesn't. I think it's conflict between VBA Labels and VB script, but I'm not getting any error messages.

[RobDog888]

But if you are doing this in VB Script then you would need to create the Excel objects as in Excel VBA its already created.

[Kubull]

You wouldn't have the VB Script code for creating Excel objects would you?

[RobDog888]

It should be using Late Binding techniques but Im not fluent with VBS.

Just

CreateObject("Excel.Application")

to start an instance of Excel.

Declare your workbook object variable as Object
Declare your Excel constants

[Kubull]

I'm getting this all wrong, only just realised, I was asking the script to copy a file and distribute it into multipule folders and then log the ones it's skipped (for what ever reason, as long as it tells us) into an Excel file. Just bleedin dawned on me that our server doesn't have MS Office on it, the script would never have work properly from the start. Duurrr.....

If anyone knows the script code for entering in the skipped file paths into a .txt or .rtf that would be fantastic!

[westconn1]

use the existing file scripting object to write a text file

Set f1 = fs.CreateTextFile("C:\Documents and Settings\peter\My Documents\basic\vbs\mylog.txt", True)
f1.Write mystr
f1.Close

add all the failed installs to a string variable (mystr), (separated by vbnewline) during the for loop then write the file before destoying your fso object

[Kubull]

Without sounding dumb (which I probably will) but not sure what you mean by "add all the failed installs to a variable (mystr)" do you mean change all the "ActiveCell.Value = f1" inputs?

[westconn1]

If InStr(f1.Name, ",") > 0 Then
On Error Resume Next
SourceFile.Copy f1 & "\Databases\normal_PM\"
If Err.Number <> 0 Then
Select Case Err.Number ' Evaluate error number.
Case 76 ' "Path not found" error.
'ActiveCell.Value = f1
mystr = mystr & f1 & " path not found" & vbnewline  ' add to string here
'ActiveCell.Offset(1, 0).Select
'LogInformation f1
Err.Clear
Case Else
mystr = mystr & " " & err.description & vbnewline   ' or here
'ActiveCell.Value = f1
'ActiveCell.Offset(1, 0).Select
End Select
Else
End If
On Error GoTo 0
End If
Next
       Set f1 = fso.CreateTextFile("C:\Documents and Settings\peter\My Documents\basic\vbs\mylog.txt", True)
      f1.Write mystr               'write string to file
      f1.Close 
set f1 = nothing    'destroy objects
set fso = nothing

[anhn]

Your code should be indented, it will be easier to read.
Try this: (I cannot test it)

Option Explicit

Dim xlApp, wb, aCell
Dim fso, f, f1, SourceFile

Set xlApp = CreateObject("Excel.Application")
Set wb = Workbooks.Add
Set aCell = wb.Sheets(1).Range("A1")
aCell.Value = "Folders Omitted"
aCell.Font.Bold = True

Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFile = fso.GetFile("E:\Test Area\NormalUpdates\NormalPM\normal.dot")
Set f = fso.Getfolder("E:\Test Area\")

For Each f1 In f.SubFolders
   If InStr(f1.Name, ",") > 0 Then
      Set aCell = aCell.Offset(1, 0)
      aCell.Value = f1.Name
      On Error Resume Next
      SourceFile.Copy f1 & "\Databases\normal_PM\"
      If ErNum <> 0 Then
         aCell.Offset(0, 1).Value = Err.Description
         Err.Clear
      End If
      On Error GoTo 0
   End If
Next
wb.SaveAs ("E:\Test Area\Noticeboard\NormalUpdates\omitted files.xls")
wb.Close
xlApp.Quit

[westconn1]

@ anhn
he does not have excel installed, so needs to save errors to file

[Kubull]

Westconn1, You are a God! the script works like a charm! Many thanks for the help! gimmie a PO Box address and there'll be a case of Beer waiting for ya!

[anhn]

@ anhn
he does not have excel installed, so needs to save errors to file

I didn't know that. I thought he wanted the logs in Excel.

Anyway save the logs to a text file is quicker, but look at my For Loop, you should capture any error, not just error 76.

[westconn1]

see post #9
i thought i had caught all other errors, in case else

[anhn]

But why you have to split 76 and others? No need to use Case statement, the same manner : the file cannot be coppied.
"Path not found" is the Err.Description of Err 76.