How to save as "XLSX" file?

[Former Member]

Hello,

I viewed a couple of old posts regarding this matter

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

http://scn.sap.com/message/15919600#15919600

But i'm not able to resolve the issue.

In one of the posts the asked to add the following

REM==
Set xclapp = GetObject(,"Excel.Application")
xclapp.displayalerts = false
Set xclwbk = xclapp.workbooks.item("Worksheet in ALVXXL01 (1)")
xclapp.ActiveWorkbook.SaveAs("C:\tmp\test.xlsx")
xclapp.ActiveWorkbook.close

Set xclwbk = Nothing

xclapp.displayalerts = true

set xclapp = Nothing

REM==

The script stops @

Set xclwbk = xclapp.workbooks.item("Worksheet in ALVXXL01 (1)")

help please

& can you help on this? 🙂

[Former Member]

I think I see the problem in the current code... also I was running from Access vs Excel, so I activeworkbook was guranteed to be the SAP created workbook, not the one running the macro.

I ran from excel and eliminated the extra code not needed to find the excel app since you are already in excel.  Since it seems you cannot rely on the workbook name, I used the highest internal workbook count as the reference

Try this.

Workbooks.Application.DisplayAlerts = False

Workbooks.Item(Workbooks.Count).SaveAs ("C:\tmp\" & COL1 & ".xlsx")

Workbooks.Item(Workbooks.Count).Close

Workbooks.Application.DisplayAlerts = True

Those 4 lines should replace all of this:

Set xclapp = GetObject(,"Excel.Application")

xclapp.displayalerts = false

Set xclwbk = xclapp.workbooks.item("Worksheet in ALVXXL01 (0)")

rem Set xclwbk = xclapp.workbooks.item(1)

xclapp.ActiveWorkbook.SaveAs("C:\tmp\" & COL1 & ".xlsx")

rem xclapp.ActiveWorkbook.SaveAs("C:\tmp\test.xlsx")

xclapp.ActiveWorkbook.close

Set xclwbk = Nothing

xclapp.displayalerts = true

set xclapp = Nothing

[Former Member]

Hello,

The script stopped @ row 70

Workbooks.Application.DisplayAlerts = False

!!

The application needs to be defined first i think. You are trying to "saveAs" the most recent opened workbook. This will work if the SAP opened a new one but this didn't happen yet.

[Former Member]

I guess I should have realized, based on the error message format, that you are starting with your excel macro but then calling a .vbs file from there.  Is that right?  That makes it very hard to troubleshoot and is unnecessary and you could solve this in a fraction of the time.

You can copy all of your .vbs code into the excel macro as it's own procedure and you'll be able to step through it line by line in debug mode, if you make the SAP Connection within excel.  but I suppose that's a discussion for a different thread.

So keeping it in the .vbs, then it needs to look like this (basically replacing the explicit name of the workbook with the highest internal number of the workbook, since it is the most recently created one)

Set xclapp = GetObject(,"Excel.Application")

xclapp.displayalerts = false

Set xclwbk = xclapp.workbooks.item(xclapp.workbooks.count)

xclwbk.SaveAs("C:\tmp\" & COL1 & ".xlsx")

xclwbk.close

Message was edited by: Chad Horihan  corrected to change references from ActiveWorkbook to xclwbk

[Former Member]

Hello,

You are right, i was doing this from VBS file.

I've moved the code to the excel VBA now and this is what i have

Public SapGuiAuto As Object

Public App As Object

Public Connection As Object

Public Session As Object

Sub CJI3_click()

Dim objExcel

Dim objSheet, intRow, i

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set Session = Connection.Children(0)

Set objExcel = GetObject(, "Excel.Application")

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 To objSheet.UsedRange.Rows.Count

col1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1

col2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2

col3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3

col4 = Trim(CStr(objSheet.Cells(i, 4).Value)) 'Column4

col5 = Trim(CStr(objSheet.Cells(i, 5).Value)) 'Column5

Rem ADDED BY EXCEL *************************************

AppActivate Session.findbyId("wnd[0]").Text 'this to give focus to SAP and bring it to foreground

Session.findbyId("wnd[0]").resizeWorkingPane 226, 39, False

Session.findbyId("wnd[0]/tbar[0]/okcd").Text = "/ncji3"

Session.findbyId("wnd[0]").SendVKey 0

If Session.ActiveWindow.Name = "wnd[1]" Then

  Session.findbyId("wnd[1]/usr/ctxtTCNT-PROF_DB").Text = "z1000"

  Session.findbyId("wnd[1]").SendVKey 0

End If

Session.findbyId("wnd[0]/usr/ctxtCN_PROJN-LOW").Text = col1

Session.findbyId("wnd[0]/usr/ctxtR_BUDAT-LOW").Text = col2

Session.findbyId("wnd[0]/usr/ctxtR_BUDAT-HIGH").Text = col3

Session.findbyId("wnd[0]/usr/ctxtP_DISVAR").Text = col4

Session.findbyId("wnd[0]/usr/ctxtP_DISVAR").SetFocus

Session.findbyId("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 11

Session.findbyId("wnd[0]/usr/btnBUT1").press

Session.findbyId("wnd[1]/usr/txtKAEP_SETT-MAXSEL").Text = col5

Session.findbyId("wnd[1]/usr/txtKAEP_SETT-MAXSEL").caretPosition = 5

Session.findbyId("wnd[1]/tbar[0]/btn[0]").press

Session.findbyId("wnd[0]/tbar[1]/btn[8]").press

Set xclapp = GetObject(, "Excel.Application")

xclapp.DisplayAlerts = False

Set xclwbk = xclapp.Workbooks.Item("Worksheet in ALVXXL01 (1)")

xclapp.ActiveWorkbook.SaveAs ("C:\tmp\test.xlsx")

xclapp.ActiveWorkbook.Close

Set xclwbk = Nothing

xclapp.DisplayAlerts = True

Set xclapp = Nothing

Rem==

'Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu

'Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

'Session.findById("wnd[1]/tbar[0]/btn[0]").press

Rem FINALIZATION CONTROL CHECK ************************

aux = col1 & " " & col2 & " " & col3 & " " & col4 & " " & col5

CreateObject("WScript.Shell").Run ("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")

Next

MsgBox "Process Completed"

Rem FINALIZATION CONTROL CHECK ************************

End Sub

Things went fine and the report was out, I'm again at the same issue, how to "SaveAs". At the moment, i need to trigger the SAP to create a new workbook before iniating the saving and closing, right?

[Former Member]

It looks like you moved and commented out the lines to have SAP export the data to excel.

Rem==

'Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu

'Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

'Session.findById("wnd[1]/tbar[0]/btn[0]").press

move them back to after these lines:

.

.

.

Session.findbyId("wnd[1]/usr/txtKAEP_SETT-MAXSEL").caretPosition = 5

Session.findbyId("wnd[1]/tbar[0]/btn[0]").press

Session.findbyId("wnd[0]/tbar[1]/btn[8]").press

'the export to excel lines go next

Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu

Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

Session.findById("wnd[1]/tbar[0]/btn[0]").press

'then save the excel workbook SAP just created,

'which will be the latest/highest count workbook in the Workbooks collection

Workbooks.Application.DisplayAlerts = False

Workbooks.Item(Workbooks.Count).SaveAs ("C:\tmp\" & COL1 & ".xlsx")

Workbooks.Item(Workbooks.Count).Close

Now that you are running entirely from within Excel, you can run in debug mode and step through each line one at a time using the F8 key (on excel side) and watch exactly what happens on the SAP screen. 

[Former Member]

Thanks Chad for your support, much appreciated.

This is what I have now.

Public SapGuiAuto As Object

Public App As Object

Public Connection As Object

Public Session As Object

Sub CJI3_click()

Dim objExcel

Dim objSheet, intRow, i

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set Session = Connection.Children(0)

Set objExcel = GetObject(, "Excel.Application")

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 To objSheet.UsedRange.Rows.Count

COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1

COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2

COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3

COL4 = Trim(CStr(objSheet.Cells(i, 4).Value)) 'Column4

COL5 = Trim(CStr(objSheet.Cells(i, 5).Value)) 'Column5

Rem ADDED BY EXCEL *************************************

AppActivate Session.findById("wnd[0]").Text 'this to give focus to SAP and bring it to foreground

Session.findById("wnd[0]").resizeWorkingPane 226, 39, False

Session.findById("wnd[0]/tbar[0]/okcd").Text = "/ncji3"

Session.findById("wnd[0]").sendVKey 0

If Session.ActiveWindow.Name = "wnd[1]" Then

  Session.findById("wnd[1]/usr/ctxtTCNT-PROF_DB").Text = "z1000"

  Session.findById("wnd[1]").sendVKey 0

End If

Session.findById("wnd[0]/usr/ctxtCN_PROJN-LOW").Text = COL1

Session.findById("wnd[0]/usr/ctxtR_BUDAT-LOW").Text = COL2

Session.findById("wnd[0]/usr/ctxtR_BUDAT-HIGH").Text = COL3

Session.findById("wnd[0]/usr/ctxtP_DISVAR").Text = COL4

Session.findById("wnd[0]/usr/btnBUT1").press

Session.findById("wnd[1]/usr/txtKAEP_SETT-MAXSEL").Text = COL5

Session.findById("wnd[1]/tbar[0]/btn[0]").press

Session.findById("wnd[0]/tbar[1]/btn[8]").press

Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu

Session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"

Session.findById("wnd[1]/usr/cmbG_LISTBOX").Key = "10"

Session.findById("wnd[1]/tbar[0]/btn[0]").press

'The save as window appears here, i have to type in the file name and press Enter

Workbooks.Application.DisplayAlerts = False

Workbooks.Item(Workbooks.Count).SaveAs ("C:\tmp\" & COL1 & ".xls")

Workbooks.Item(Workbooks.Count).Close

Rem FINALIZATION CONTROL CHECK ************************

aux = COL1 & " " & COL2 & " " & COL3 & " " & COL4 & " " & COL5

CreateObject("WScript.Shell").Run ("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")

Next

MsgBox "Process Completed"

Rem FINALIZATION CONTROL CHECK ************************

End Sub

-----

[Former Member]

based on your original code, I assumed that CJI3 was exporting directly into a new excel workbook without asking you for a file name then you needed to save it (IQ09, IW58, IW72 t-codes do it that way).  I didn't have access to CJI3, so I couldn't try it.  But I have since found a system where I could run CJI3 and also from seeing the screenshot you provided, I see that it is prompting for the file name via the Windows Save As window and now you are in the often lamented problem of how to interact with that window, since it isn't an SAP screen.  The usual response is to use a .vbs script to make it the ActiveApp, then tab around in it.  You call the .vbs file from your Excel VBA code.  Since I hate calling .vbs files I spent the day digging through a bunch of half answered forum questions and a youtube video and finally pieced together a way to do it from within the VBA code.

1. first at the top of your module above all of your function and sub definitions, add these declarations to get access to the 3 windows apis that you need.

    Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

    Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

    'this one requires the PtrSafe wording on 64-bit windows

    Declare PtrSafe Function SendMessageByString Lib "user32.dll" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long

2. I created this custom procedure to contain all of the interaction with the Save As window

Sub ProcessSaveAsWindow(FileNameAndPath As String)

Dim wHndMainWindow

Dim wHndFileNameLabel

Dim wHndFileNameEntry

Dim wHndSaveButton

Const BM_CLICK As Integer = &HF5

Const WM_SETTEXT As Integer = &HC

  'this window's caption is "Save As", so we search for it's handle using the FindWindow API

  wHndMainWindow = FindWindow(vbNullString, "Save As")

  If wHndMainWindow <> 0 Then

    'there is no caption on the file name entry box,

    'but it is right after the file name label and has a class type of ComboBoxEx32

    wHndFileNameLabel = FindWindowEx(wHndMainWindow, 0, vbNullString, "File &name:")

    wHndFileNameEntry = FindWindowEx(wHndMainWindow, whandFileNameLabel, "ComboBoxEx32", vbNullString)

    wHndSaveButton = FindWindowEx(wHndMainWindow, 0, vbNullString, "&Open")

    'send text to the field

    SendMessageByString wHndFileNameEntry, WM_SETTEXT, False, ByVal FileNameAndPath

    'click the save button

    SendMessage wHndSaveButton, BM_CLICK, 0, 0&

  End If

End Sub

3. get rid of these lines and replace with

'The save as window appears here, i have to type in the file name and press Enter

Workbooks.Application.DisplayAlerts = False

Workbooks.Item(Workbooks.Count).SaveAs ("C:\tmp\" & COL1 & ".xls")

Workbooks.Item(Workbooks.Count).Close

Call ProcessSaveAsWindow ("C:\tmp\" & COL1 & ".xls")

4. I might need to change the If wHndMainWindow <> 0 to a loop that keeps trying to find the Save As window if it doesn't appear before the code gets there...

[Former Member]

Chad, thanks a lot for your hard work 🙂 ... we are closer now but not yet done!

Your understanding is correct, you have to "save as" first in order to get the new work book generated with the name you entered in the "Save as" window.

For every line in the VBA code you will get the control back to VBA. But Once you click on the green button on the "select spreadsheet" window - presented by the following line

Session.findById("wnd[1]/tbar[0]/btn[0]").press

The focus/control will stay in the SAP till you enter a file name. This is why any code in the VBA is not going to work 🙂

Your good work "ProcessSaveAsWindow" works perfect in the following scenario :

- Execute the CJI3 from the SAP -no VBA-

- Select the Spreadsheet

- click on the green button, the "Save As" window will appear.

- Call ProcessSaveAsWindow ("C:\tmp\testingfunction.xlsx") from the "Immediate" window in the VBA.

Can we have "ProcessSaveAsWindow" working in the background waiting for the "Save as" window to pop-up?

"ProcessSaveAsWindow" can be called before starting the "export" codes.

Once again, thanks a lot 🙂

[Former Member]

We need to call "ProcessSaveAsWindow" Asynchronous before starting the export?

so the ProcessSaveAsWindow will keep looping waiting for the save As window to appear.

[Former Member]

amazing how after I search for examples I can't find any, then I write my own and I can suddenly find multiple examples from other people.

Ok, so VBA can't launch a separate thread natively, so it's usually back to calling a vb script to get it done.  I found a response from ScriptMan for the Save As window... here is the code

in your VBA code, in place of calling ProcessSaveAsWindow, you need to launch a vbscript file using the shell command

'launch vb script that will watch for and interact with Save As window

'this needs to run  before the SAP step that triggers the Save As window

Shell "wscript c:\tmp\SavesAs.vbs c:\tmp\testfile1.xlsx"

SaveAs.vbs file contents.  I copied this almost directly from one of ScriptMan's responses:

if Wscript.Arguments.count > 0 then

' This first section deletes the file if it already exists, to avoid a prompt to overwrite.

set fs = CreateObject("Scripting.FileSystemObject")

if fs.fileExists(WScript.arguments(0)) then

  Set myfile = fs.GetFile(WScript.arguments(0))

  myfile.Delete

end if

'this loop runs until the Save As window finally appears

set Wshell = CreateObject("WScript.Shell")

Do

  bWindowFound = Wshell.AppActivate("Save As")

  WScript.Sleep 1000

Loop Until bWindowFound

  'this is the actual interaction with the Save As window.  Tabbing 5 times gets you to the file name

  'entry field

  Wshell.appActivate "Save As"

  Wshell.sendkeys "{TAB 5}"

  Wshell.sendkeys WScript.arguments(0)

  WScript.Sleep 400

  Wshell.appActivate "Save As"

  Wshell.sendkeys "%s"

  WScript.Sleep 400

end if

I considered writing a VBA procedure that would create the vbs file, run it, then delete it, so it would not have to live on every user's computer that uses the excel file, but it could just as easily be placed on a shared folder for common access.

[Former Member]

check the name that SAP is assigning to the workbook.  Looks like it may vary based on the underlying file format you have chosen.  For me, the workbook name is "Worksheet in Basis (1)" not "Worksheet in ALVXXL01 (1)".  I used the above code exactly except for changing the workbook name and it worked fine.

Chad

[Former Member]

Thanks Chad for your prompt reply.

It didn't work for me? i got the attached error at the same line.

Any other variations? what is these options about?

[Former Member]

Can you provide a screenshot of the excel window that SAP created?

[Former Member]

Hello,

This is what i have in my excel sheet, i'm executing this vbs from my XLS sheet

My VBS contents as following :

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If
REM ADDED BY EXCEL *************************************

Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(,"Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 to objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2
COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3
COL4 = Trim(CStr(objSheet.Cells(i, 4).Value)) 'Column4
COL5 = Trim(CStr(objSheet.Cells(i, 5).Value)) 'Column5

REM ADDED BY EXCEL *************************************

session.findById("wnd[0]").resizeWorkingPane 226,39,false
session.findById("wnd[0]/tbar[0]/okcd").text = "/ncji3"
session.findById("wnd[0]").sendVKey 0
If session.ActiveWindow.Name = "wnd[1]" Then
  session.FindById("wnd[1]/usr/ctxtTCNT-PROF_DB").text = "z1000"  
  session.FindById("wnd[1]").SendVKey 0
End If
session.findById("wnd[0]/usr/ctxtCN_PROJN-LOW").text = COL1
session.findById("wnd[0]/usr/ctxtR_BUDAT-LOW").text = COL2
session.findById("wnd[0]/usr/ctxtR_BUDAT-HIGH").text = COL3
session.findById("wnd[0]/usr/ctxtP_DISVAR").text = COL4
session.findById("wnd[0]/usr/ctxtP_DISVAR").setFocus
session.findById("wnd[0]/usr/ctxtP_DISVAR").caretPosition = 11
session.findById("wnd[0]/usr/btnBUT1").press
session.findById("wnd[1]/usr/txtKAEP_SETT-MAXSEL").text = COL5
session.findById("wnd[1]/usr/txtKAEP_SETT-MAXSEL").caretPosition = 5
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
REM session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").contextMenu
REM session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").selectContextMenuItem "&XXL"
REM session.findById("wnd[1]/tbar[0]/btn[0]").press

REM==
Set xclapp = GetObject(,"Excel.Application")
xclapp.displayalerts = false
Set xclwbk = xclapp.workbooks.item("Worksheet in ALVXXL01 (0)")
rem Set xclwbk = xclapp.workbooks.item(1)
xclapp.ActiveWorkbook.SaveAs("C:\tmp\" & COL1 & ".xlsx")
rem xclapp.ActiveWorkbook.SaveAs("C:\tmp\test.xlsx")
xclapp.ActiveWorkbook.close

Set xclwbk = Nothing

xclapp.displayalerts = true

set xclapp = Nothing

REM==

REM FINALIZATION CONTROL CHECK ************************

aux=col1 & " " & col2 & " " & col3  & " " & col4 & " " & col5
CreateObject("WScript.Shell").run("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
next
msgbox "Process Completed"

REM FINALIZATION CONTROL CHECK ************************

[Former Member]

SAP is creating new workbook in the same frame as the one where you are running your code.  Can you bring that to the foreground and send me a screenshot.  I need to see what the name of that window is... I suspect it is not exactly "Worksheet in ALVXXL01 (0)", but something similar

[Former Member]

Nothing is created!

The script works fine and the report is generated. When it comes to line 57

Set xclwbk = xclapp.workbooks.item("Worksheet in ALVXXL01 (1)")

I got the error msg mentioned in my earlier reply.

Looks like i need to creat the object first.

[Former Member]

Hello,

This is what changed now :

REM==

Set xclapp = GetObject(,"Excel.Application")

xclapp.displayalerts = false

rem Set xclwbk = xclapp.workbooks.item("Worksheet in Book2 (1)")

Set xclwbk = xclapp.workbooks.item(2)

xclapp.ActiveWorkbook.SaveAs("C:\tmp\" & COL1 & ".xlsx")

rem xclapp.ActiveWorkbook.close

This gave me the following error

I changed the following line to be XLS instead of XLSX

xclapp.ActiveWorkbook.SaveAs("C:\tmp\" & COL1 & ".xlsx")

This generated two files as expected but the contents ar the same as my Macro file.

The export process didn't work, instead i "SaveAs" the opened Macro file to two new files!