Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Excel Data Range into Access Table
I saw a post from K_Dale which provided assistance in Inserting data from a
data range into an Access Table. He used a Function to loop through the range for the INSERT INTO [TableName] Can someone please review my Procedure and Function to see where I have an error? In the Function BuildSQL I get a Compile Error: "For Each control variable must be Variant or Object" Some Background Access Table = [Inventory Export], which has 3 columns and are defined to match Excel columns Excel Range = Worksheets("InvExport").Range("A29:C33) Sample Range data W0214071 411 2 W0214071 412 1 W0214071 413 2 W0214071 414 3 Sub UploadData() Dim TableName As String Dim ValueRange As Range Dim MyCn As ADODB.Connection Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" _ & "DBQ=Z:\El Dorado Springs Inventory.mdb" MyCn.Execute BuildSQL(TableName, ValueRange) MyCn.Close Set MyCn = Nothing End Sub Function BuildSQL(TableName As String, ValueRange As Range) As String Dim DataCell As String Dim SQL As String Dim FirstCell As Boolean TableName = "[Inventory Export]" Set ValueRange = Worksheets("InvExport").Range("A29:C33") SQL = "INSERT INTO " & TableName & " Values(" FirstCell = True For Each DataCell In ValueRange If Not (FirstCell) Then SQL = SQL & "," SQL = SQL & "'" & DataCell.Text & "'" FirstCell = False Next DataCell SQL = SQL & ")" BuildSQL = SQL End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Excel Data Range into Access Table
Replace: Dim DataCell As String
With: Dim DataCell As Range That addresses the error message. I didn't test otherwise. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Excel Data Range into Access Table
yes, DataCell should be dim'd as Range or Object, not String.
-- Regards, Tom Ogilvy "MikeElectricUtility" wrote: I saw a post from K_Dale which provided assistance in Inserting data from a data range into an Access Table. He used a Function to loop through the range for the INSERT INTO [TableName] Can someone please review my Procedure and Function to see where I have an error? In the Function BuildSQL I get a Compile Error: "For Each control variable must be Variant or Object" Some Background Access Table = [Inventory Export], which has 3 columns and are defined to match Excel columns Excel Range = Worksheets("InvExport").Range("A29:C33) Sample Range data W0214071 411 2 W0214071 412 1 W0214071 413 2 W0214071 414 3 Sub UploadData() Dim TableName As String Dim ValueRange As Range Dim MyCn As ADODB.Connection Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)};" _ & "DBQ=Z:\El Dorado Springs Inventory.mdb" MyCn.Execute BuildSQL(TableName, ValueRange) MyCn.Close Set MyCn = Nothing End Sub Function BuildSQL(TableName As String, ValueRange As Range) As String Dim DataCell As String Dim SQL As String Dim FirstCell As Boolean TableName = "[Inventory Export]" Set ValueRange = Worksheets("InvExport").Range("A29:C33") SQL = "INSERT INTO " & TableName & " Values(" FirstCell = True For Each DataCell In ValueRange If Not (FirstCell) Then SQL = SQL & "," SQL = SQL & "'" & DataCell.Text & "'" FirstCell = False Next DataCell SQL = SQL & ")" BuildSQL = SQL End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
Inserting data from excel into access | Excel Programming | |||
Data From Excel Range To Existing Access Table | Excel Programming | |||
inserting a picture from an excel file into an access table | Excel Programming | |||
Excel Range to Access Table | Excel Programming |