Simplify the vba code

[cogivina]

I try to upload the data from excel to the table in access database using this long vba code. How can I simplify this code:

Set rstData = dbName.OpenRecordset(iTable, dbOpenTable)
Range("Upload").Cells(1, 0).Select
For iRow = 0 To Range("Upload").Rows.Count - 1
rstData.AddNew
rstData(0).Value = ActiveCell.Offset(0, 1).Value
rstData(1).Value = ActiveCell.Offset(0, 2).Value
rstData(2).Value = ActiveCell.Offset(0, 3).Value
......
rstData(33).Value = ActiveCell.Offset(0, 34).Value

rstData.Update
rstData.MoveLast
ActiveCell.Offset(1, 0).Select

Next iRow

I'd like to loop through the column but don't know how to use the rstdata(irow, icol) code:

For iRow = 0 To Range("Upload").Rows.Count - 1

For iCol = 0 To Range("Upload").Columns.Count - 1
rstData.AddNew
rstData(0).Value = ActiveCell.Offset(0, iCol+1).Value
Next iCol

rstData.Update
rstData.MoveLast
ActiveCell.Offset(1, 0).Select
Next iRow

Thanks.

 

[PHV]

rstData(iCol).Value = ActiveCell.Offset(0, iCol+1).Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cogivina]

Hi PHV,
Thanks for your quick response.

The code works but it takes a few minutes to upload the whole data (>14,000 rows) by looping through every rows and columns. Can/How I just write the code to copy the whole matrix in excel and paste that to the table in access to reduce the time?

 

[MakeItSo]

Do you really need to do this with vba at all?
You could as well link the XLS into the MDB and create an UPDATE query.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[NeilRP]

Instead of using AddNew and Update, I would build a SQL INSERT statement and execute it on the database.

 

[ettienne]

Looks like you are using DAO to open the Access db, you can use DAO to open Excel as well, then do an append query.
It is still going to take some time to run through 14,000 records though.