## Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

• Help others & share knowledge
• Earn cash & points
Solved

# VBScript to create excel file and set the width of columns

Posted on 2008-06-10
5,810 Views
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.ActiveWorkbook.SaveAs strFile, -4143

objExcel.ActiveWorkbook.Close
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.ActiveWorkbook.SaveAs strFile, -4143

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing

'msgbox "FINISHED!!!"

'******************************************************
'Delete the input file
'******************************************************
fso.DeleteFile(strOrigFile)

0
Question by:arundelr
• 3
• 2

Author Comment

ID: 21752518
I can suppress the error message like so

On Error Resume Next
fso.DeleteFile(strOrigFile)
0

LVL 65

Expert Comment

ID: 21757379
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

LVL 65

Accepted Solution

RobSampson earned 500 total points
ID: 21757381
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

Author Closing Comment

ID: 31465789
Thanks Rob ;o)
0

LVL 65

Expert Comment

ID: 21764932
No problem. Thanks for the grade.

Regards,

Rob.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…