Simple VBS question

[lotaguts]

here is what I am trying to accomplish please look at code below.

Dim xlApp
Dim xlWkb
Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("\\myserver\sys\personal.xls")
Set xlWkb = xlApp.Workbooks.Open("\\myserver\sys\ReportExprenew.csv")
xlApp.Visible = True
Set xlWkb = Nothing
Set xlApp = Nothing

When the file ReportExprenew.csv opens I would like the Hotkeys "Ctrl Z" to be pressed how can accomplish this ?

Thanks for any feedback

 

[djtech2k]

Use the sendkeys function. Do some searching and you will find it around many times.

 

[lotaguts]

Thanks I will search

 

[jfdabiri]

how about this: (just an example)

Set WSHShell = WScript.CreateObject("Wscript.Shell") 
WSHShell.Run ("notepad.exe")
WshShell.AppActivate ("untitled - notepad")
WScript.Sleep 500
wshshell.sendkeys "^Z"

you have to "activate" (focus)
and then send keys to the app.

 

[lotaguts]

How would I apply this to my script which opens a csv in excel ? thanks again

 

[djtech2k]

I would try this:

When you have your excel sheet open, note the "title" of the app in the taskbar. Lets just say its "myspreadsheet.xls - Microsoft Excel". I would then try:

Set WSHShell = WScript.CreateObject("Wscript.Shell")
WshShell.AppActivate ("untitled - notepad")
WScript.Sleep 500
wshshell.sendkeys "^Z"

 

[lotaguts]

Dim xlApp
Dim xlWkb
Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("\\myserver\sys\personal.xls")
Set xlWkb = xlApp.Workbooks.Open("\\myserver\sys\ReportExpRenew.csv")
Set WSHShell = WScript.CreateObject("Wscript.Shell")
WshShell.AppActivate ("Microsoft Excel - ReportExpRenew.csv")
WScript.Sleep 500
wshshell.sendkeys "^Z"
xlApp.Visible = True
Set xlWkb = Nothing
Set xlApp = Nothing

Any ideas why the CTRL Z is being applied to personal.xls rather than ReportExpRenew.csv ? thanks for all your help

 

[lotaguts]

Correction ctrl z is not working at all am I using the correct syntax in the wshshell.sendkeys "^Z

 

[djtech2k]

Maybe try:

wshshell.sendkeys ^(Z)

You may need to put it in quotes, but try it both ways. This will emulate holding down ctrl and pressing z.

Here is a good reference:

http://cwashington.netreach.net/main/library/sendkeys.html