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

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

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