Exporting Data from Excel (Dynamic Range) to Access Table

itsmefeddi

New Member
Joined
Dec 19, 2012
Messages
34
Hi all,

I am having trouble in the below code its keep getting me error on line
rst.Fields(Cells(1, i).Value) = Cells(x, i).Value

i dont know what the problem is please help me out.

the complete code is here

Sub Export_Data()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbPath As String, TblName As String
Dim x As Long, i As Long
Dim nextrow As Long, lastcolmn As Long
Dim ws As Worksheet

TblName = "SubCategoryList"
Set ws = ThisWorkbook.Worksheets(TblName)
ws.Activate

dbPath = "D:\WMS\DataBase\wms.accdb"
nextrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolmn = Range("IV1").End(xlToLeft).Column

'Initialise the collection class variable
Set cnn = New ADODB.Connection

If ws.Range("A2").Value = "" Then
MsgBox " Add the data that you want tot send to MS Access"
Exit Sub
End If

cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.ConnectionString = "D:\WMS\DataBase\wms.accdb"
cnn.Open

Set rst = New ADODB.Recordset
rst.Open TblName, cnn, adOpenDynamic, adLockOptimistic, adCmdTable

For x = 2 To nextrow
rst.AddNew
For i = 1 To lastcolmn
rst.Fields(Cells(1, i).Value) = Cells(x, i).Value
Next i
rst.Update
Next x

rst.Close

cnn.Close

Set rst = Nothing
Set cnn = Nothing

Application.ScreenUpdating = True

End Sub
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
and the error i am getting at present is

Multiple step OLE-DB operations generated error, Check each OLEDB status value

Hi all,

I am having trouble in the below code its keep getting me error on line
rst.Fields(Cells(1, i).Value) = Cells(x, i).Value

i dont know what the problem is please help me out.

the complete code is here

Sub Export_Data()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbPath As String, TblName As String
Dim x As Long, i As Long
Dim nextrow As Long, lastcolmn As Long
Dim ws As Worksheet

TblName = "SubCategoryList"
Set ws = ThisWorkbook.Worksheets(TblName)
ws.Activate

dbPath = "D:\WMS\DataBase\wms.accdb"
nextrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolmn = Range("IV1").End(xlToLeft).Column

'Initialise the collection class variable
Set cnn = New ADODB.Connection

If ws.Range("A2").Value = "" Then
MsgBox " Add the data that you want tot send to MS Access"
Exit Sub
End If

cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
cnn.ConnectionString = "D:\WMS\DataBase\wms.accdb"
cnn.Open

Set rst = New ADODB.Recordset
rst.Open TblName, cnn, adOpenDynamic, adLockOptimistic, adCmdTable

For x = 2 To nextrow
rst.AddNew
For i = 1 To lastcolmn
rst.Fields(Cells(1, i).Value) = Cells(x, i).Value
Next i
rst.Update
Next x

rst.Close

cnn.Close

Set rst = Nothing
Set cnn = Nothing

Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top