If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Specifiy .Range .Values

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 43
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
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 43
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 11:56.
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,212
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,176
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!
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 43
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
Reply With Quote
  #7 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,176
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!
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 43
Thank you!!!
Reply With Quote
  #9 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,176
You're welcome!
__________________
Have a nice day!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On