Go Premium for a chance to win a PS4. Enter to Win

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

VBScript to create excel file and set the width of columns

Hi,

The attached code will create an Excel file and then when done it deletes the input file here:

fso.DeleteFile(strOrigFile)

If for some reason that file is being used by another process then I get this error:

line 66
char 1
error: permission denied
code 800A0046
runtime error

Is it possible to suppress this specific error, perhaps setting the error logging into a textfile. Or in the delete command tell it to ignore errors and carry on processing the rest of the script?



Option Explicit
 
 
Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory
Dim objExcel
 
Set fso = CreateObject("Scripting.FileSystemObject")
 
 
 
'******************************************************
'The input file name is passed at run time i.e. wscript C:\testing\csv_to_xls.vbs C:\testing\in.csv
'******************************************************
'strOrigFile = "C:\testing\in.csv"
 
strOrigFile = WScript.Arguments(0)
 
'******************************************************
'CONVERTS THE FILE TO AN EXCEL FILE
'******************************************************
strFile = replace(strOrigFile,".csv",".xls")
 
Set objExcel = CreateObject("Excel.Application")
 
If not fso.FileExists(strOrigFile) Then
'  msgbox "Sorry. the file could not be located."
  wscript.quit
End If
objExcel.Workbooks.Open strOrigFile
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit
 
'******************************************************
'FORMAT THE EXCEL FILE AND SAVE IT
'******************************************************
 
objExcel.Workbooks.Open strFile
 
'Set the number format to zero D.P for specified columns
objExcel.columns("A:A").numberformat="0"
objExcel.columns("C:C").numberformat="0"
objExcel.columns("D:D").numberformat="0"
 
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strFile, -4143
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Close
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
 
'msgbox "FINISHED!!!"
 
'******************************************************
'Delete the input file
'******************************************************
fso.DeleteFile(strOrigFile)

Open in new window

0
arundelr
Asked:
arundelr
  • 3
  • 2
1 Solution
 
arundelrAuthor Commented:
I can suppress the error message like so

On Error Resume Next
fso.DeleteFile(strOrigFile)
0
 
RobSampsonCommented:
Hi, you could also do something like:

On Error Resume Next
intRetryCount = 0
fso.DeleteFile(strOrigFile)
While Err.Number <> 0 And intRetryCount < 10
   WScript.Sleep 1000 ' wait one second
   Err.Clear
   fso.DeleteFile(strOrigFile)
Wend
Err.Clear
On Error GoTo 0
If fso.FileExists(strOrigFile) Then MsgBox strOrigFile & " was not deleted."


This will make it retry for 10 seconds.

Regards,

Rob.
0
 
RobSampsonCommented:
Whoops, forgot to increment intRetryCount

On Error Resume Next
intRetryCount = 0
fso.DeleteFile(strOrigFile)
While Err.Number <> 0 And intRetryCount < 10
   WScript.Sleep 1000 ' wait one second
   intRetryCount = intRetryCount + 1
   Err.Clear
   fso.DeleteFile(strOrigFile)
Wend
Err.Clear
On Error GoTo 0
If fso.FileExists(strOrigFile) Then MsgBox strOrigFile & " was not deleted."
0
 
arundelrAuthor Commented:
Thanks Rob ;o)
0
 
RobSampsonCommented:
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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