?
Solved

Converting batch code to VB!

Posted on 2008-01-31
4
Medium Priority
?
468 Views
Last Modified: 2012-06-27
Hello all,
I apologize upfront for the length of this question!
I have a batch file that I use to run some equations for a database dump.
Batch code starts here>
CMD /C
Net Use P: \\10.1.206.36\e$ /USER:"username"
P:
CD\Temp2\Temp3\cgi-bin
..\util\dumpdb userstat -trim -header -search tag psqdone -delim , -time  -o "%date:/=-%"QA_REPORT.csv
..\util\dumpdb userstat -trim -header -search TAG PSQDONE -delim , -time  -o "%date:/=-%"UsersDone.csv
R:
cd\Temp4\Temp Reports
MD "%date:/=-%"
copy p:\P-Synch\PS_SYNCH_USERS\cgi-bin\"%date:/=-%"QA_REPORT.csv r:\"P-Synch\P-Synch Reports"\"%date:/=-%"\ /Y
copy p:\P-Synch\PS_SYNCH_USERS\cgi-bin\"%date:/=-%"UsersDone.csv r:\"P-Synch\P-Synch Reports"\"%date:/=-%"\ /y
del p:\P-Synch\PS_SYNCH_USERS\cgi-bin\"%date:/=-%"QA_REPORT.csv
del P:\P-Synch\PS_SYNCH_USERS\cgi-bin\"%date:/=-%"UsersDone.csv
CMD /C NET USE P: /D /y
Exit
< Batch code ends here>

The ("%date:/=-%") makes a folder or file name with the system date as the name and this is where I am having the issue!
In my VBScript I have the following code that opens Excel and imports the "%date:/=-%"QA_REPORT.csv trims the columns that are not needed.  The code looks like this:
>VB code Starts here > 
' Option Explict
' Dim WkBkName as String
Dim oXL
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

'Open the CSV
WkBkName = "C:\Reports\"%date:/=-%"\ "%date:/=-%"QA_REPORT.csv"
oXL.Workbooks.Open(WkBkName)

' Select the undesired columns working from right to left
' then delete them one at a time
oXL.Columns("E:E").Select
oXL.Selection.Delete
oXL.Columns("D:D").Select
oXL.Selection.Delete
oXL.Columns("C:C").Select
oXL.Selection.Delete
oXL.Columns("B:B").Select
oXL.Selection.Delete
<VB code Stops Here>
As I indicated before the folder and file dates are the issue, I can not open the folder. If I could do all of this in VBscript it would be better. Any suggestions would be appreciated!


Hello-all.doc
0
Comment
Question by:robby6161
  • 2
  • 2
4 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 20790994
Hi,  you cannot use DOS variable methods in VBScript.  Change this:
WkBkName = "C:\Reports\"%date:/=-%"\ "%date:/=-%"QA_REPORT.csv"

to this
WkBkName = "C:\Reports\" & Day(Now) & "-" & Month(Now) & "_QA_REPORT.csv"

This would make a file called 31-01_QA_REPORT.csv

Regards,

Rob.
0
 

Author Comment

by:robby6161
ID: 20791812
Okay that is fine and I am assuming that you are saying I could do the whole thing in VBscript. How would I run these two (2) lines in VB?
..\util\dumpdb userstat -trim -header -search tag psqdone -delim , -time  -o "%date:/=-%"QA_REPORT.csv
&
..\util\dumpdb userstat -trim -header -search TAG PSQDONE -delim , -time  -o "%date:/=-%"UsersDone.csv
Which are done from the command line
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 1000 total points
ID: 20792362
Hi, yes, the whole thing can be done in VBS....the easiest way to run your command would be to run it in a command shell, like so:

Set objShell = CreateObject("Wscript.Shell")
strCommand = "cmd /k P:\Temp2\Temp3\util\dumpdb userstat -trim -header -search tag psqdone -delim , -time  -o " & Day(Now) & "-" & Month(Now) & "_QA_REPORT.csv"
objShell.Run strCommand, 1, True

when you have finished testing, you can change the cmd /k to cmd /c to have the command prompt go away after processing the command.

Regards,

Rob.
0
 

Author Closing Comment

by:robby6161
ID: 31426871
Thanks Rob!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses
Course of the Month7 days, 4 hours left to enroll

592 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