Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    45

    Specifiy .Range .Values

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,519
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    45
    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
    Last edited by VBAnewbie; 02-26-13 at 12:56.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,519
    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
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,273
    An expression such as:
    Code:
    xlSht.Range("A:A").Value
    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):
    Code:
    Range("A:A").Item(1, 1).Value, Range("A:A").Item(2, 1).Value,... Range("A:A").Item(65536, 1).Value
    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:
    Code:
    Dim i As Long
    For i = 1 to 65536
        dbRst.AddNew
        dbRst.Fields(0).Value = xlSht.Range("A" & i).Value
        '
        ' Continue with the other columns.
        '
        db.Rst.Update
    Next i
    Or:
    Code:
    Dim i As Long
    For i = 1 to 65536
        dbRst.AddNew
        dbRst.Fields(0).Value = xlSht.Range("A:A").Item(i, 1).Value
        '
        ' Continue with the other columns.
        '
        db.Rst.Update
    Next i
    Have a nice day!

  6. #6
    Join Date
    Jan 2013
    Posts
    45
    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

  7. #7
    Join Date
    Mar 2009
    Posts
    5,273
    To me, it should be more like:
    Code:
    Dim i As Long
    For i = 1 To 10
        dbRst.AddNew
        dbRst.Fields(0).Value = xlSht.Range("A" & i).Value
        dbRst.Fields(1).Value = xlSht.Range("B" & i).Value
        dbRst.Fields(2).Value = xlSht.Range("C" & i).Value
        dbRst.Fields(3).Value = xlSht.Range("D" & i).Value
        dbRst.Update
    Next i
    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)
    Have a nice day!

  8. #8
    Join Date
    Jan 2013
    Posts
    45
    Thank you!!!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,273
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •