Skip to Content
avatar image
Former Member

Browse for folder to save extracted SAP report

Hello All,

Kindly help me with the error i'm encountering. Basically, i need the extracted file from SAP to browse for folder location first for saving, however, the main file (macro file) was the one saved in a specific path.

Public Sub IABatch() is for extracting the report from SAP. The GetSaveAsFileName module is the browse for folder/save as code.

Public Sub IABatch()

Title = Sheet1.Cells(6, 5)
ComCode = Sheet1.Cells(8, 5)
excl1 = Sheet1.Cells(11, 5)
excl2 = Sheet1.Cells(12, 5)
excl3 = Sheet1.Cells(13, 5)
excl4 = Sheet1.Cells(14, 5)
excl5 = Sheet1.Cells(15, 5)
excl6 = Sheet1.Cells(16, 5)
excl7 = Sheet1.Cells(17, 5)
excl8 = Sheet1.Cells(18, 5)
excl9 = Sheet1.Cells(19, 5)
excl10 = Sheet1.Cells(20, 5)


excl11 = Sheet1.Cells(11, 9)
excl11 = Sheet1.Cells(12, 9)
excl11 = Sheet1.Cells(13, 9)
excl11 = Sheet1.Cells(14, 9)
excl11 = Sheet1.Cells(15, 9)
excl11 = Sheet1.Cells(16, 9)
excl11 = Sheet1.Cells(17, 9)
excl11 = Sheet1.Cells(18, 9)
excl11 = Sheet1.Cells(19, 9)
excl11 = Sheet1.Cells(20, 9)




If Month(Now) < 9 Then
    monthtoday = "0" & Month(Now)
Else
    monthtoday = Month(Now)
End If


If Day(Now) < 9 Then
    daytoday = "0" & Day(Now)
Else
    daytoday = Day(Now)
End If
  
    Dim current As Workbook
    Set current = ActiveWorkbook
    Dim ARlayout As String
    Dim objExcel
    Dim objSheet, intRow, i
    Set objExcel = GetObject(, "Excel.Application")
    Set objBook = objExcel.ActiveWorkbook
    
        
Set SapGuiAuto = GetObject("SAPGUI")
Set Appln = SapGuiAuto.GetScriptingEngine
Set Connection = Appln.Children(0)
Set session = Connection.Children(0)

session.findById("wnd[0]").resizeWorkingPane 230, 38, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "REAJPR"
session.findById("wnd[0]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/ctxtS_BUKRS-LOW").Text = ComCode
session.findById("wnd[0]/usr/btn%_S_RECNNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[16]").press
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV").Select
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,0]").Text = excl1
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,1]").Text = excl2
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,2]").Text = excl3
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,3]").Text = excl4
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,3]").SetFocus
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpNOSV/ssubSCREEN_HEADER:SAPLALDB:3030/tblSAPLALDBSINGLE_E/ctxtRSCSEL_255-SLOW_E[1,3]").caretPosition = 10
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/txtP_TITLE").Text = Title
session.findById("wnd[0]/usr/ctxtP_PEXTID").Text = Title
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_METH/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1100/chkP_METH02").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_METH/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1100/chkP_METH02").Selected = True
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP").Select
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP08").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP08").Selected = False
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP03").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP03").Selected = False
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP04").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP04").Selected = False
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP06").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEP/ssub%_SUBSCREEN_TAB_ADJM:RFREAJPROCESS:1110/chkP_STEP06").Selected = False
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA").Select
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_NEXT").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_NEXT").Selected = True
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_CNOC").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_CNOC").Selected = True
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_CNNOC").SetFocus
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/chkP_CNNOC").Selected = True
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/cmbP_CNOCA").Key = "2"
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/cmbP_CNNOCA").Key = "2"
session.findById("wnd[0]/usr/tabsTABSTRIP_TAB_ADJM/tabpTAB_STEPPARA/ssub%_SUBSCREEN_TAB_ADJM:SAPLREAJ_GUI_PROCESS:4000/tabsCTRL_STEPPARA/tabpTAB_STEPPARA_01/ssubSUB_STEPPARA:SAPLREAJ_GUI_PROCESS_STEP_ADJM:2000/cmbP_CNNOCA").SetFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").setCurrentCell -1, "ICONADJMDRECORDSTAT"
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").selectColumn "ICONADJMDRECORDSTAT"
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").pressToolbarButton "&SORT_ASC"
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCC_ADJM_ADJMREC_GRID/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press


Call GetSaveAsFileName


'session.findById("wnd[1]/usr/ctxtDY_PATH").SetFocus
'session.findById("wnd[1]/usr/ctxtDY_PATH").caretPosition = 0
'session.findById("wnd[1]").sendVKey 4


'session.findById("wnd[2]/usr/ctxtDY_PATH").Text = s_path
'session.findById("wnd[2]").sendVKey 4
'session.findById("wnd[3]/usr/ctxtDY_FILENAME").Text = strFileName
'session.findById("wnd[3]/usr/ctxtDY_FILENAME").caretPosition = 9
'session.findById("wnd[3]/tbar[0]/btn[0]").press


session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
session.findById("wnd[0]").sendVKey 0


End Sub

Module

MODULE code
Sub GetSaveAsFileName()
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Long, Response As Long
    ComCode = Sheet1.Cells(8, 5)
    excl1 = Sheet1.Cells(11, 5)
    SAP_Workbook = "Worksheet in Basis(1)"
    
If Month(Now) < 9 Then
    monthtoday = "0" & Month(Now)
Else
    monthtoday = Month(Now)
End If


If Day(Now) < 9 Then
    daytoday = "0" & Day(Now)
Else
    daytoday = Day(Now)
End If
    
   
    '   Set to Specified Path\Folder
        ChDir "......INDEX ADJUSTMENT"
    '   Set File Filter
        Filt = "Excel Files (*.xls), *.xls"
    '   Set *.* to Default
        FilterIndex = 5
    '   Set Dialogue Box Caption
        Title = "Please select a different File"
    '   Get FileName
        FileName = ComCode & "_INDEX SIM_" & excl1 & "_" & Year(Now) & monthtoday & daytoday & ".xls"
    '   Exit if Dialogue box cancelled
        If FileName = False Then
            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")
            Exit Sub
        End If
    '   Display Full Path & File Name
        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")
    '   Save & Close Workbook
        With ActiveWorkbook
            .SaveAs FileName
            .Close
        End With
        
        
            
End Sub

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers