It is a while since I did this and I no longer have any access to SAP. However, from memory:
I started off with a VBScript that I had recorded in SAP.
I then deleted all the superfluous lines. For example, it always remembered the position of the cursor and usually that was not required so I removed those.
Then I inserted blank lines and added comments so that I could work out what it was doing.
Then I removed the first part that connected to SAP and turned the rest into a subroutine. The parameters for the transaction would be added and the constants turned into variables where necessary.
Then I had a master "boiler plate" VBScript that had the connection details for SAP Session 1. It also had the code to control Excel. It would fire up Excel and load a data file in the same folder.
This boiler plate code would be amanded to read in the right columns from the Excel workbook.
Then the code from the recording with the changes would be pasted in.
After that the script could be started, this would connect to SAP and load the workbook. It would then read the rows of the workbook until it found a blank line and send the data to SAP row by row. I did some with two routines to call parts of transactions separately.
If I had had some time to experiment I am fairly sure that I could have managed without the modified boiler plate script and driven everything from Excel but I retired before that happened!
Connecting could be tricky. I used to close all SAP sessions regardless of client or box then start again from the SAPGUI. This would ensure that the first session of the first connection would be available. It also stopped me from running test transactions in production by accident - never a popular move!
I hope this helps.