Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

passing parameter into vb script

Posted on 2008-06-10
9
Medium Priority
?
1,992 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
[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
  • 5
  • 3
9 Comments
 
LVL 8

Expert Comment

by:rseabird
ID: 21750072
0
 

Author Comment

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

by:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

by:Mike Tomlinson
ID: 21751770
It should work...try:

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

Author Comment

by:arundelr
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.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 86

Assisted Solution

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

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

by:arundelr
ID: 31465682
Fantastic !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

618 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