Solved

# passing parameter into vb script

Posted on 2008-06-10
Medium Priority
1,989 Views
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.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!!!"

0
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
• 5
• 3

LVL 8

Expert Comment

ID: 21750072
0

Author Comment

ID: 21750371
Hi,

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

Rob
0

LVL 86

Accepted Solution

Mike Tomlinson earned 2000 total points
ID: 21751659
You access the parameters passed into your script using WScript.Arguments:
http://msdn.microsoft.com/en-us/library/z2b05k8s(VS.85).aspx

So you could do:

strOrigFile = WScript.Arguments(0)

0

Author Comment

ID: 21751744
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 ;)
0

LVL 86

Expert Comment

ID: 21751770
It should work...try:

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

Author Comment

ID: 21751815
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.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
'******************************************************
Set fso = CreateObject("Scripting.FileSystemObject")
strInputFile =WScript.Arguments(0)
fso.DeleteFile(strInputFile)

0

LVL 86

Assisted Solution

Mike Tomlinson earned 2000 total points
ID: 21751881
You need to use YOUR variable names my friend...

Change:

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

To:

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

0

Author Comment

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

Thanks for your invaluable help ;o)

Rob
0

Author Closing Comment

ID: 31465682
Fantastic !
0

## Featured Post

Question has a verified solution.

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

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
###### Suggested Courses
Course of the Month14 days, 6 hours left to enroll