Need help from Excel and SAP expert!

[YH]

I need a micro that runs a vbs script to export data to SAP, waits for the
SAP data export to complete, saves the exported data in a new worksheet, and
goes back to SAP screen. However, my micro (attached below) has these
problems:

1) When running wscript.exe to execute vbs code, a wscript logon window pops
up.
How can I remove the pop-up?
2) Excel does not wait for SAP to finish exporting. It automatically runs
the next line.
3) Once the exported data is saved in excel format, how can I make the micro
return to original SAP window?

Any help is greatly appreciated.

Thanks,
YH



Declare Function OpenProcess Lib "Kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long


Declare Function GetExitCodeProcess Lib "Kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long


Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

Sub VF()
Dim StartTime As Double
Dim retval As Variant

retval = Shell("c:\windows\system32\wscript.exe C:\VF.vbs", vbNormalFocus)
ShellAndWait "c:\windows\system32\wscript.exe", 1
'ShellAndWait does not wait for VF.vbs to finish and runs the next line!
Need help.
MsgBox "file download complete"
ActiveWorkbook.SaveAs Filename:="C:\REV.xls"

'When SAP exports data to excel, it by default exports a worksheet called
"Worksheet
'in Basis(1)". Here is to delete the worksheet since I already saved it as
REV.xls.

Windows("Worksheet in Basis (1)").Close

End Sub

'Window States (Per Help for Shell function):
' 1, 5, 9 Normal with focus.
' 2 Minimized with focus.
' 3 Maximized with focus.
' 4, 8 Normal without focus.
' 6, 7 Minimized without focus.
Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long

'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub

 

[Shaka215]

YH,

If you figure this one out let me know because I think I might be
able to use the same process eventually. I am confused though are you
needing help with script writting or with Excel doing something inside
of SAP? Probally not the answer you are looking for but if you plan on
saving time your self doing this I would suggest downloading a 3rd
Party Macro program and just recording your movements...then launching
the macro.

 

[Tim Williams]

What's in the vbs script ?
It would probably be much cleaner to translate that to VBA, instead of
shelling out and running it as vbs.

Tim

 

[NickHK]

Why not convert your VBS to Excel VBA, as you are working in Excel anyway.
Then there's no need to overcome the problem of asynchronous code execution
and everything is in Excel.

NickHK

 

[Kim Greenlee]

YH,

First, don't use wscript.exe, use cscript.exe. 'W' = windows which means
GUI and async, 'C' = console which means no GUI and sync. That is why the
script isn't waiting for SAP to finish.

I've discussed Excel automation in several blog posts in order of relevance:

http://krgreenlee.blogspot.com/2006/03/digipede-distributing-excel_10.html
http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

You'll probably want to disable some of the Excel UI elements and my posts
talk about that.

Good luck,

Kim Greenlee

 

[YH]

I use:

Dim WSHshell As Object
set WSHshell = CreateObject("wscript.Shell")

And it works now. Thanks!

YH