Solved

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

Posted on 2008-06-10
5,754 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

### Suggested Solutions

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.