Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Natalia16
Discoverer
0 Kudos
360

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:\Users\strawan\Documents\Scripts\"
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:\Users\strawan\Documents\Scripts\VKM1.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:\Users\strawan\Documents\Scripts\"
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:\Users\strawan\Documents\Scripts\VKM2.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:\Users\strawan\Documents\Scripts\"
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:\Users\strawan\Documents\Scripts\ZFIAR045.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:\Users\strawan\Documents\Scripts\"
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:\Users\strawan\Documents\Scripts\ATB.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:\Users\strawan\Documents\Scripts\CombinedData.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:\Users\strawan\Documents\Scripts\CombinedData.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!

0 REPLIES 0