Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBScript to create excel file and set the width of columns

Posted on 2008-06-10
5
Medium Priority
?
5,951 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
[X]
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
  • Learn & ask questions
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 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