I have vb code using ADO that can insert a single row of data from an Excel sheet one at a time.
But I am looking to insert the entire contents on the sheet1 all at once.
Here is my single row code.
Sub SampleInsertLoopingThroughRows()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim rng As Range
Dim row As Range
conn.ConnectionString = "Provider=MSDASQL.1;Extended Properties=Driver={Teradata};DBCName=xxxxx;maxrespsize=1048576;UID=xxxxx;PWD=xxxxx"
conn.Open
Set rng = Range("rng_database")
For Each row In rng.Rows
Sql = "INSERT INTO prfmrgn_t.CR_TestImport " & _
"(CUSTOM_FRMT, CUSTOM_BU_ID, CUSTOM_INPUT_ID, CUSTOM_DIV_FINDPT_ID, CUSTOM_LN_FINCTG_ID, SALES) " & _
" VALUES (" & _
"'" & row.Cells(1, 1).Value & "', " & _
"'" & row.Cells(1, 2).Value & "', " & _
"'" & row.Cells(1, 3).Value & "', " & _
"'" & row.Cells(1, 4).Value & "', " & _
"'" & row.Cells(1, 5).Value & "', " & _
"'" & row.Cells(1, 6).Value & "'" & _
")"
conn.Execute Sql
Next row
conn.Close
End Sub
But I am looking to do something like this code below. I am trying to define sheet1 and a table and excel as the database. I have seen many examples of this but none working with Teradata.
Any suggestions would be appreciated. Thank You
Sub SampleInsertAllDataOnSheet()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL.1;Extended Properties=Driver={Teradata};DBCName=xxxxx;maxrespsize=1048576;UID=xxxxx;PWD=xxxxx"
conn.Open
Sql = "INSERT INTO prfmrgn_t.CR_TestImport Select * from [Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name & "].[sheet1$];"
conn.Execute Sql
conn.Close
End Sub
But I am looking to insert the entire contents on the sheet1 all at once.
Here is my single row code.
Sub SampleInsertLoopingThroughRows()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim rng As Range
Dim row As Range
conn.ConnectionString = "Provider=MSDASQL.1;Extended Properties=Driver={Teradata};DBCName=xxxxx;maxrespsize=1048576;UID=xxxxx;PWD=xxxxx"
conn.Open
Set rng = Range("rng_database")
For Each row In rng.Rows
Sql = "INSERT INTO prfmrgn_t.CR_TestImport " & _
"(CUSTOM_FRMT, CUSTOM_BU_ID, CUSTOM_INPUT_ID, CUSTOM_DIV_FINDPT_ID, CUSTOM_LN_FINCTG_ID, SALES) " & _
" VALUES (" & _
"'" & row.Cells(1, 1).Value & "', " & _
"'" & row.Cells(1, 2).Value & "', " & _
"'" & row.Cells(1, 3).Value & "', " & _
"'" & row.Cells(1, 4).Value & "', " & _
"'" & row.Cells(1, 5).Value & "', " & _
"'" & row.Cells(1, 6).Value & "'" & _
")"
conn.Execute Sql
Next row
conn.Close
End Sub
But I am looking to do something like this code below. I am trying to define sheet1 and a table and excel as the database. I have seen many examples of this but none working with Teradata.
Any suggestions would be appreciated. Thank You
Sub SampleInsertAllDataOnSheet()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL.1;Extended Properties=Driver={Teradata};DBCName=xxxxx;maxrespsize=1048576;UID=xxxxx;PWD=xxxxx"
conn.Open
Sql = "INSERT INTO prfmrgn_t.CR_TestImport Select * from [Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name & "].[sheet1$];"
conn.Execute Sql
conn.Close
End Sub