Converting batch code to VB!

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
robby6161Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
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
robby6161Author Commented:
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
RobSampsonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
robby6161Author Commented:
Thanks Rob!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.