Solved

passing parameter into vb script

Posted on 2008-06-10
9
1,978 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:arundelr
  • 5
  • 3
9 Comments
 
LVL 8

Expert Comment

by:rseabird
Comment Utility
0
 

Author Comment

by:arundelr
Comment Utility
Hi,

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

Rob
0
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 500 total points
Comment Utility
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

by:arundelr
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
It should work...try:

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

Author Comment

by:arundelr
Comment Utility
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

0
 
LVL 85

Assisted Solution

by:Mike Tomlinson
Mike Tomlinson earned 500 total points
Comment Utility
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

by:arundelr
Comment Utility
"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

by:arundelr
Comment Utility
Fantastic !
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

16 Experts available now in Live!

Get 1:1 Help Now