Solved

VBScript to create excel file and set the width of columns

Posted on 2008-06-10
5
5,768 Views
Last Modified: 2010-04-21
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
Comment
Question by:arundelr
  • 3
  • 2
5 Comments
 

Author Comment

by:arundelr
ID: 21752518
I can suppress the error message like so

On Error Resume Next
fso.DeleteFile(strOrigFile)
0
 
LVL 65

Expert Comment

by:RobSampson
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

by:
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

by:arundelr
ID: 31465789
Thanks Rob ;o)
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21764932
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now