Hello again all, have a question regarding how to specify a range in excel which creates a table in access. Here is the code I have thus far. Any suggestions on how to get this to work? Thanks!

xlSht.Range("A:A").Select
dbRst.Fields(0).Value = xlSht.Range("A:A").Value
xlSht.Range("B:B").Select
dbRst.Fields(1).Value = xlSht.Range("B:B").Value
xlSht.Range("C:C").Select
dbRst.Fields(2).Value = xlSht.Range("C:C").Value
xlSht.Range("D: D").Select
dbRst.Fields(3).Value = xlSht.Range("D: D").Value
dbRst.Update

is this not more of an Excel question than an Access question?

do you want me to move the post to the Excel forum

generally speaking sending data to Access from Excel has the feel of the wrong way round, store data in Access, use excel to manipulate/analyse the data assuming you cannot get the same results from an Access report itself

Thanks for your response healdem, I actually think this is more of an Access question. Here is my entire vba script. I'm hitting a road block in trying to import all the data from the Excel worksheet. Since this is an ongoing process for me, it is much faster than a traditional import.


Private Sub importExcelData()

Dim xlApp As Excel.Application
Dim xlBk As Excel.Workbook
Dim xlSht As Excel.Worksheet

Dim dbRst As DAO.Recordset
Dim dbs As DAO.Database
Dim SQLStr As String

Set dbs = CurrentDb
Set xlApp = Excel.Application
Set xlBk = xlApp.Workbooks.Open("C:\Temp\ImportData.xlsx")
Set xlSht = xlBk.Sheets(1)

SQLStr = "CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT, columnThree TEXT, columnFour TEXT)"
DoCmd.SetWarnings False
DoCmd. RunSQL (SQLStr)

Set dbRst = dbs.OpenRecordset("excelData")
dbRst.AddNew

'Here is where I'm encountering issues
xlSht.Range("A:A").Select
dbRst.Fields(0).Value = xlSht.Range("A:A").Value
xlSht.Range("B:B").Select
dbRst.Fields(1).Value = xlSht.Range("B:B").Value
xlSht.Range("C:C").Select
dbRst.Fields(2).Value = xlSht.Range("C:C").Value
xlSht.Range("D: D").Select
dbRst.Fields(3).Value = xlSht.Range("D: D").Value
dbRst.Update

dbRst.Close
dbs.Close
xlBk.Close

End Sub

you are doing (or trying to do) something in Excel VBA, the fact its trying to insert rows into an Access DB is largely irrelevant. the problem is in your Excel VBA, not Access or Access VBA

FWIW I think that what you may have to do is iterate through each row and then insert a row. but as Imnot upto speed with Excel VBA I haven't got a clue if you are trying to insert one row or multiple rows into the DB
I don't understand the Excel VBA well enough

An expression such as:
Returns an array object, not a scalar value. Its actually composed of (remember that, by default, the lower bound of an array in Excel is 1, not 0):
From Access (more precisely from DAO) viewpoint, the Field object of a RecordSet is scalar, i.e. it can only contain a single value (with the infamous exception of the so-called multi-value fields in Acc2007 and 2010, but that's another story).

You must use a loop to perform this kind of operation. You can either:
Or:

Thanks Sinndo, this is what I have thus far. The table generates but only displays the data in the last column specified. Not sure where the Do and Loop would be introduced here.

xlSht.Range("A: D").Select

Dim i As Long
For i = 1 To 10
dbRst.AddNew
dbRst.Fields(0).Value = xlSht.Range("A" & i).Value
dbRst.Fields(0).Value = xlSht.Range("B" & i).Value
dbRst.Fields(0).Value = xlSht.Range("C" & i).Value
dbRst.Fields(0).Value = xlSht.Range("D" & i).Value
dbRst.Update
Next i

To me, it should be more like:
Remeber that x in Fields(x) is related to a Column, in the Excel sheet as well as in the Access table, while i (in the loop For i = ... Next) is a counter related to the Rows (both in Excel and Access)

Thank you!!!

You're welcome!