Upload Excel worksheets to AS400

Question

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

I have this code for the upload button as below:

Code:

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?

Tracy Probst Tracy Probst · Accepted Answer · 2012-01-24T15:08:32

Without getting into the details of actually coding the whole thing, the problem with your code sample is that you cannot send the select SQL of the spreadsheet to the AS/400. The AS/400 cannot see your spreadsheet and, therefore, cannot load the data from it in a single SQL. Instead, you must read through all lines of your spreadsheet and insert those records, one at a time, into your AS/400 table. Parameterized SQL is preferable.

This is the section of code that needs to go:

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 & "$]')"

It won't work. Replace it with a loop reading through the lines in the workbook. You could use an Excel OLEDB set or read the spreadsheet row by row. Inside the loop, for each spreadsheet row read, you will have to write an INSERT INTO statement to be executed via ODBC to the AS/400.

Upload Excel worksheets to AS400

2 Answers