Help needed in my VBA code SAP logon / SAP Scripting & Excel Interaction in VBA

By /

Hi there,

 

I have the following code:

‘ Function to read data from file
Function GetDataFromFile(filePath)
Dim ExcelApp
Dim Workbook
Dim DataSheet
Dim DataRange
Dim Data

‘ Create Excel object
Set ExcelApp = CreateObject(“Excel.Application”)
ExcelApp.Visible = False ‘ Optional: Hide Excel application

‘ Open workbook
Set Workbook = ExcelApp.Workbooks.Open(filePath)

‘ Assuming data is in the first sheet (Change if necessary)
Set DataSheet = Workbook.Sheets(1)

‘ Get the used range in the sheet
Set DataRange = DataSheet.UsedRange

‘ Get the data from the range
Data = DataRange.Value

‘ Close workbook without saving changes
Workbook.Close False

‘ Quit Excel application
ExcelApp.Quit

‘ Release objects
Set DataRange = Nothing
Set DataSheet = Nothing
Set Workbook = Nothing
Set ExcelApp = Nothing

‘ Return the data
GetDataFromFile = Data
End Function

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

‘ Create Excel object
Dim ExcelApp
Set ExcelApp = CreateObject(“Excel.Application”)

‘ Open Excel workbook
Dim ExcelWorkbook
Set ExcelWorkbook = ExcelApp.Workbooks.Add

‘ Transaction 1: VKM1
MsgBox “Executing VKM1 transaction…”
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nVKM1”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/ctxtKKBER-LOW”).text = “2000”
session.findById(“wnd[0]/usr/ctxtP_VARI”).text = “/TE CCA 2000”
session.findById(“wnd[0]/usr/ctxtP_VARI”).setFocus
session.findById(“wnd[0]/usr/ctxtP_VARI”).caretPosition = 12
session.findById(“wnd[0]/tbar[1]/btn[8]”).press
session.findById(“wnd[0]/mbar/menu[3]/menu[1]/menu[2]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).setFocus
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/usr/ctxtDY_PATH”).text = “C:UsersstrawanDocumentsScripts”
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).text = “VKM1.xls”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).text = “4103”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).setFocus
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).caretPosition = 4
session.findById(“wnd[1]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press
session.findById(“wnd[1]/usr/btnSPOP-OPTION1”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press

‘ Add a new worksheet for VKM1 data
Dim VKM1Sheet
Set VKM1Sheet = ExcelWorkbook.Worksheets.Add
VKM1Sheet.Name = “VKM1_Data”

‘ Fetch data from VKM1.xls file
Dim VKM1Data
VKM1Data = GetDataFromFile(“C:UsersstrawanDocumentsScriptsVKM1.xls”)

‘ Paste data into VKM1 worksheet
‘ Check if VKM1Data is not empty before pasting
If Not IsEmpty(VKM1Data) Then
‘ Paste values from VKM1Data into VKM1Sheet starting from cell A1
VKM1Sheet.Cells(1, 1).Resize(UBound(VKM1Data, 1), UBound(VKM1Data, 2)).Value = VKM1Data
End If

‘ Add a delay of 20 seconds
WScript.Sleep 20000 ‘

‘ Transaction 2: VKM2
MsgBox “Executing VKM2 transaction…”
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nVKM2”
session.findById(“wnd[0]/tbar[0]/btn[0]”).press
session.findById(“wnd[0]/usr/ctxtKKBER-LOW”).text = “2000”
session.findById(“wnd[0]/usr/ctxtP_VARI”).text = “/credit”
session.findById(“wnd[0]/usr/ctxtP_VARI”).setFocus
session.findById(“wnd[0]/usr/ctxtP_VARI”).caretPosition = 7
session.findById(“wnd[0]/tbar[1]/btn[8]”).press
session.findById(“wnd[0]/mbar/menu[3]/menu[1]/menu[2]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).setFocus
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/usr/ctxtDY_PATH”).text = “C:UsersstrawanDocumentsScripts”
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).text = “VKM2.xls”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).text = “4103”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).setFocus
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).caretPosition = 4
session.findById(“wnd[1]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press

‘ Add a new worksheet for VKM2 data
Dim VKM2Sheet
Set VKM2Sheet = ExcelWorkbook.Worksheets.Add
VKM2Sheet.Name = “VKM2_Data”

‘ Fetch data from VKM2.xls file
Dim VKM2Data
VKM2Data = GetDataFromFile(“C:UsersstrawanDocumentsScriptsVKM2.xls”)

‘ Paste data into VKM2 worksheet
‘ Check if VKM2Data is not empty before pasting
If Not IsEmpty(VKM2Data) Then
‘ Paste values from VKM2Data into VKM2Sheet starting from cell A1
VKM2Sheet.Cells(1, 1).Resize(UBound(VKM2Data, 1), UBound(VKM2Data, 2)).Value = VKM2Data
End If

‘ Add a delay of 20 seconds
WScript.Sleep 20000 ‘

‘ Transaction 3: ZFIAR045
MsgBox “Executing ZFIAR045 transaction…”
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nZFIAR045”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/ctxtSP$00001-LOW”).text = “2000”
session.findById(“wnd[0]/usr/ctxtSP$00001-HIGH”).text = “9999999999”
session.findById(“wnd[0]/usr/ctxtSP$00007-LOW”).text = “1028”
session.findById(“wnd[0]/usr/ctxtSP$00006-LOW”).text = “110000”
session.findById(“wnd[0]/usr/ctxtSP$00014-LOW”).text = “2000”
session.findById(“wnd[0]/usr/ctxtSP$00014-LOW”).setFocus
session.findById(“wnd[0]/usr/ctxtSP$00014-LOW”).caretPosition = 4
session.findById(“wnd[0]/tbar[1]/btn[8]”).press
session.findById(“wnd[0]/mbar/menu[0]/menu[4]/menu[2]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).setFocus
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/usr/ctxtDY_PATH”).text = “C:UsersstrawanDocumentsScripts”
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).text = “ZFIAR045.xls”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).text = “4103”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).setFocus
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).caretPosition = 4
session.findById(“wnd[1]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press

‘ Add a new worksheet for ZFIAR045 data
Dim ZFIAR045Sheet
Set ZFIAR045Sheet = ExcelWorkbook.Worksheets.Add
ZFIAR045Sheet.Name = “ZFIAR045_Data”

‘ Fetch data from ZFIAR045.xls file
Dim ZFIAR045Data
ZFIAR045Data = GetDataFromFile(“C:UsersstrawanDocumentsScriptsZFIAR045.xls”)

‘ Paste data into ZFIAR045 worksheet
‘ Check if ZFIAR045Data is not empty before pasting
If Not IsEmpty(ZFIAR045Data) Then
‘ Paste values from ZFIAR045Data into ZFIAR045Sheet starting from cell A1
ZFIAR045Sheet.Cells(1, 1).Resize(UBound(ZFIAR045Data, 1), UBound(ZFIAR045Data, 2)).Value = ZFIAR045Data
End If

‘ Add a delay of 20 seconds
WScript.Sleep 20000 ‘

‘ Transaction 4: ATB
MsgBox “Executing ATB transaction…”
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell”).doubleClickNode “F00009”
session.findById(“wnd[0]/tbar[1]/btn[17]”).press
session.findById(“wnd[1]/usr/txtENAME-LOW”).text = “kazmag”
session.findById(“wnd[1]/usr/txtENAME-LOW”).setFocus
session.findById(“wnd[1]/usr/txtENAME-LOW”).caretPosition = 6
session.findById(“wnd[1]/tbar[0]/btn[8]”).press
session.findById(“wnd[0]/tbar[1]/btn[8]”).press
session.findById(“wnd[0]/mbar/menu[0]/menu[1]/menu[2]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).select
session.findById(“wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]”).setFocus
session.findById(“wnd[1]/tbar[0]/btn[0]”).press
session.findById(“wnd[1]/usr/ctxtDY_PATH”).text = “C:UsersstrawanDocumentsScripts”
session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).text = “ATB.xls”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).text = “4103”
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).setFocus
session.findById(“wnd[1]/usr/ctxtDY_FILE_ENCODING”).caretPosition = 4
session.findById(“wnd[1]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/btn[15]”).press
session.findById(“wnd[0]/tbar[0]/btn[3]”).press

‘ Add a new worksheet for ATB data
Dim ATBSheet
Set ATBSheet = ExcelWorkbook.Worksheets.Add
ATBSheet.Name = “ATB_Data”

‘ Fetch data from ATB.xls file
Dim ATBData
ATBData = GetDataFromFile(“C:UsersstrawanDocumentsScriptsATB.xls”)
If Not IsEmpty(ATBData) Then
‘ Paste values from ATBData into ATBSheet starting from cell A1
ATBSheet.Cells(1, 1).Resize(UBound(ATBData, 1), UBound(ATBData, 2)).Value = ATBData
End If

‘ Add a delay of 20 seconds
WScript.Sleep 20000 ‘

‘ Add debug statements to check data fetching
MsgBox “Fetching data from SAP…”

‘ Save Excel workbook
MsgBox “Saving Excel workbook…”
Dim FilePath
FilePath = “C:UsersstrawanDocumentsScriptsCombinedData.xls”
ExcelWorkbook.SaveAs FilePath

‘ Close Excel workbook and quit Excel application
MsgBox “Closing Excel workbook…”
ExcelWorkbook.Close
ExcelApp.Quit

‘ Release Excel objects
Set ExcelWorkbook = Nothing
Set ExcelApp = Nothing

‘ Create Excel application object
Dim ExcelAppEnd
Set ExcelAppEnd = CreateObject(“Excel.Application”)

‘ Make Excel visible
ExcelAppEnd.Visible = True

‘ Open the exported data file
Dim ExportedDataFilePath
ExportedDataFilePath = “C:UsersstrawanDocumentsScriptsCombinedData.xls”
ExcelAppEnd.Workbooks.Open ExportedDataFilePath’

 

That is working well. I would like to create further the code to remove some columns and rows from Excel file from each tab: VKM1_Data, VKM2_Data, ZFIAR045_Data, ATB_Data.

I am completely new to creating own code, so I would appreciate your help how to write this?

 

Thank you!

Scroll to Top