Thread: Upload Excel Worksheets to AS400 with VBA

Hi Folks,

I have an excel workbook with multiple worksheets. First worksheet have the login information of the user and there is a button to upload all worksheets corresponding to different tables in AS400. Worksheet name are the same names as the AS400 table names.

I have this code for the upload button as below:

Dim objConn As New ADODB.Connection, objRs As New ADODB.Recordset
Dim WS_Count As Integer
Dim I As Integer
Dim WS_Name As String

objConn.ConnectionString = "DSN=MYAS400;DRIVER=Client Access ODBC Driver (32-bit); " & _
                         "SYSTEM = <ip>; UID = <uname>;PWD = <pwd>"
objConn.Open
   WS_Count = ActiveWorkbook.Worksheets.Count
For I = 2 To WS_Count
        WS_Name = ActiveWorkbook.Worksheets(I).Name
        objConn.Execute "DELETE FROM MYAS400LIB. " & WS_Name & ""
        objConn.Execute "INSERT INTO MYAS400LIB. " & WS_Name & "  SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=" & ThisWorkbook.FullName & ";HDR=YES;IMEX=1', 'SELECT * FROM [" & WS_Name & "$]')"
     

Next I
objConn.Close
Set objConn = Nothing
End Sub

How can I insert each worksheet (with column headers) into AS400?

Please help as I am a newbie with Excel and AS400.

Thank you so much.