Need help with Excel macro - Exporting SAP data to Excel - Can't control Save As dialogue box

[Former Member]

I want to create an Excel macro that exports columns of data from SAP, exports it to Excel, and then manipulates the data into a pivot table.

I have successfully created this macro, except I had to split it into 2 pieces because I cannot control the Save As dialogue box when trying to export the data to excel. The script recorder does not record clicks and actions on the Save As dialogue box.  I have tried SendKeys with no avail.

Please look at my video to see how the macro currently runs:

www.youtube.com/watch?v=0kr0SE1HJtA

How do I create a script that automates clicking 'Save As' and then overwrites the previous file within the Save As dialogue box?

Thanks,

James

[Former Member]

James -

Your video link is broken.  What method are you using to export?  Are you downloading to a logical file?  (In our system it is "ZLF")  Or are you exporting directly from the form/report?  If you are able to download to ZLF logical files, it works really well.  Export to excel from there, and do all sorts of great stuff.

That said, to bypass the Save As dialog box, try putting in the following code (VBS) during the time the box is open (this is assuming the default directory is correct):

Set WshShell = CreateObject("WScript.Shell")

WshShell.sendkeys "%S" 'Alt+S command to save

WshShell.sendkeys "%Y" 'Alt+Y Yes I want to overwrite

Best of luck, let us know how it works!

Joe

[Former Member]

the video is private ...

I'm trying to do the same for sometime ... after upgrading GUI 740 I thought things will be easier but no improvement ..

[Former Member]

http://scn.sap.com/thread/3685815