still problems with a vbs script

Hello,
the question is related to the script accepted in:
https://www.experts-exchange.com/questions/29137575/getting-rid-of-commas-in-a-vbs-script.html
only for certain excel files I obtain the error message enclosed.
the translation is more or less: "error with the method SaveAs for the Workbook class"
Thanks
Fabiano
error.jpg
fabiano petroneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Open that Excel file and look for differences.. Macros which run on open or a printer reassignment. Maybe it is corrupt.

The error itself is generic. But it seems that it in general means a kind of permission error. Does the destination file exists and you don't have permissions to overwrite it?

btw, clean code is better for debugging. But not necessary. E.g.

'Const DIRECTORY_TO_PROCESS = "C:\Perl64\eg\alma"
Const DIRECTORY_TO_PROCESS = "C:\Temp"

Dim ExcelApplication
Set ExcelApplication = CreateObject("Excel.Application")
ExcelApplication.Visible = False
ExcelApplication.DisplayAlerts = False
ProcessDirectory DIRECTORY_TO_PROCESS, ExcelApplication
ExcelApplication.Quit
Set ExcelApplication = Nothing

Function ConvertToCsv(AExistingFileName, ANewFileName, AExcelApplication)
  Const xlTextWindows = 20
  Const xlPart = 2
  Const xlByRows = 1
  Const msoEncodingUTF8 = 65001

  On Error GoTo 0

  WScript.Echo "Processing file: " & AExistingFileName
  With AExcelApplication.Workbooks.Open(AExistingFileName, False, True)
    With .Sheets(1)
      .Rows(1).Delete
      .Columns(1).Delete
      .UsedRange.Replace ",", " ", xlPart, xlByRows, False, False, False
    End With

    WScript.Echo "Writing new file: " & ANewFileName
    .WebOptions.Encoding = msoEncodingUTF8
    .SaveAs ANewFileName, xlTextWindows
    .Close False
  End With
End Function

Function ProcessFile(AExistingFileName, ANewFileName, AExcelApplication)
  On Error Resume Next

  Err.Clear
  ConvertToCsv AExistingFileName, ANewFileName, AExcelApplication
  If Err.Number <> 0 Then
    WScript.Echo "ERROR: " & Err.Number & " - " & Err.Desription
  End If
End Function

Function ProcessDirectory(ADirectoryName, AExcelApplication)
  Dim FileSystemObject
  Dim File
  Dim Extension

  Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
  For Each File In FileSystemObject.GetFolder(ADirectoryName).Files
    Extension = FileSystemObject.GetExtensionName(File.Path)
    If LCase(Left(Extension, 3)) = "xls" Then
      ProcessFile File.Path, Replace(File.Path, "." & Extension, ".csv"), AExcelApplication
    Else
      WScript.Echo "Skipping file: " & File.Path
    End If
  Next

  Set File = Nothing
  Set FileSystemObject = Nothing
End Function

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Try opening the file manually in Excel and saving it as a Tab delimited file from Excel.  See if you get an error there and any additional clues.


»bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fabiano petroneAuthor Commented:
Hi, Bill
Thnaks a lot: I've tried the process manually & discovered that something was wrong with my MS Office.
I've re-installed and now works OK!
Thanks,
Fabiano
Bill PrewIT / Software Engineering ConsultantCommented:
Great, glad you got that sorted out.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.