asked on

passing parameter into vb script

Hi,

The attached code will convert my CSV files into XLS files, my problem is that the input file is hard coded as:strOrigFile = "C:\testing\in.csv"

I would like to be able to pass this as a parameter when I run the script, i.e.
c:\testing\script.vbs -strOrigFile = "C:\testing\in.csv"

How can I achieve this.

Also as a less important point I would like to delete the source file after teh conversion

cheers

Rob

Option Explicit
 
Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory
Dim objExcel
 
 
 
strOrigFile = "C:\testing\in.csv"
'strOrigFile = "& var1"
 
'******************************************************
'CONVERTS THE FILE TO AN EXCEL FILE
'******************************************************
strFile = replace(strOrigFile,".csv",".xls")
 
Set objExcel = CreateObject("Excel.Application")
 
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!!!"

Open in new window

rseabird

Check this site out: http://www.autohotkey.com/forum/topic20160.html

arundelr

ASKER

Hi,

Thanks for reply, I am still a little unsure. could you suggest an example using my script ?

Rob

ASKER CERTIFIED SOLUTION

Mike Tomlinson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arundelr

ASKER

Woo hoo - Perfect Idle_Mind - Thanks very much ;o)

Do you know how I can delete the input file ? I was trying...

strInputFile =WScript.Arguments(0)
objFSO.DeleteFile(strInputFile)

But not the correct syntax, as you amy have guessed I am new to VBS ;)

Mike Tomlinson

It should work...try:

Set fso = CreateObject("Scripting.FileSystemObject")
fso.DeleteFile(strInputFile)

arundelr

ASKER

Hi,

I tried....

Set fso = CreateObject("Scripting.FileSystemObject")
strInputFile =WScript.Arguments(0)
fso.DeleteFile(strInputFile)

(I attached whole script also)

But I get error message:
Line: 61
Char: 1
Error: Variable is undefined "strInputFile"

Option Explicit
 
 
 
Dim fldr, f, file,strOrigFile, strFile, fso, strDirectory
Dim objExcel
 
'strOrigFile = "C:\testing\in.csv"
 
'******************************************************
'The input file name is passed at run time i.e. wscript C:\testing\csv_to_xls.vbs 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")
 
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
'******************************************************
Set fso = CreateObject("Scripting.FileSystemObject")
strInputFile =WScript.Arguments(0)
fso.DeleteFile(strInputFile)

Open in new window

SOLUTION

Mike Tomlinson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arundelr

ASKER

"You need to use YOUR variable names my friend..."
*blush* - Ok, that was pretty dumb. lol

Thanks for your invaluable help ;o)

Rob

arundelr

ASKER

Fantastic !