0

I have a following question. Is there any way to change the below code in order to upload excel spreadsheet data into DB2 database? The below code downloads data from DB2 to excel spreadsheet.

I would like to download data to excel, manipulate it in excel and finally upload into database

Sub CreateQueryTableWithParameters()
    Dim qryTable As QueryTable
    Dim rngDestination As Range
    Dim strConnection As String
    Dim strSQL As String
    
    With Sheets("Sheet1")
        .Activate
        .Range("A:XFD").Clear
        
    End With
    
    
' Define the connection string and destination range.
strConnection = "ODBC;DSN=RDBWC;UID=;PWD=;DBALIAS=RDBWC;"

Set rngDestination = Sheet1.Range("A1")
' Create a parameter query.
strSQL = "SELECT *"
strSQL = strSQL & " FROM PDB2I.DI_NOS_OST_MVT_01 "


' Create the QueryTable.
Set qryTable = Sheet1.QueryTables.Add(strConnection, rngDestination)

' Populate the QueryTable.
qryTable.CommandText = strSQL
qryTable.CommandType = xlCmdSql
qryTable.Refresh False

End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
Tommeck37
  • 131
  • 2
  • 15

1 Answers1

0

See Import Excel spreadsheet columns into SQL Server database. The 5th answer shows how to execute an INSERT statement. Note that you must execute the INSERT statement for each row in your Excel sheet you want to upload (or UPDATE).

Community
  • 1
  • 1
Paul Ogilvie
  • 25,048
  • 4
  • 23
  • 41