• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Export range in Excel to table in Access

I've seen some online examples of how to do this but they require looping through each cell in the range and each record and field in the table. I.e...

    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("FieldName1") = Range("A" & r).Value
            .Fields("FieldName2") = Range("B" & r).Value
            .Fields("FieldNameN") = Range("C" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop

I have a big, continuous block of data so this seems like a very slow way to do something really simple. Isn't there a way to do this without having to go through one cell at a time (i.e. transfer the entire range to a table)? Thanks!

Thanks./
0
ltdanp22
Asked:
ltdanp22
  • 5
  • 2
  • 2
2 Solutions
 
ltdanp22Author Commented:
Unfortunately, I need to transfer the data from Excel to Access. Can you use TransferSpreadsheet from Excel?
0
 
tbsgadiCommented:
The TransferSpreadsheet can only be used from Access to import Excel Data
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tbsgadiCommented:
Have a look at the following:
http://www.vbforums.com/showthread.php?t=402060


Gary
0
 
tbsgadiCommented:
Sorry that's showing the other way
0
 
jefftwilleyCommented:
How about something like so?


Function ImportUsedRange()

Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim sht As Excel.Worksheet, rng As Excel.range
Dim db As DAO.Database, rs As DAO.Recordset
Dim I As Integer, J As Integer

Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open("C:\Rar.xls")   '<--Your Excel File Here

xl.Visible = True

Set sht = wkbk.Sheets("sale1201")            '<--Your Sheet Here
Set rng = sht.UsedRange                      '<--This includes all used rows
Set db = CurrentDb

Set rs = db.OpenRecordset("YourImportTable") '<-- Open your table for input

For I = 1 To rng.Columns.count               '<--This starts at column x, just change I = x

    rs.AddNew
        For J = 1 To rng.Rows.count          '<--This goes down the rows reading each value

            rs(J - 1).Value = Nz(Cells(I, J).Value, "")

        Next j
    rs.Update

    Next
rs.Close
xl.Quit
Set xl = Nothing

End Function


J
0
 
jefftwilleyCommented:
I misread...are you pushing data To access from Excel?
0
 
ltdanp22Author Commented:
I solved this by writing a TransferSpreadsheet procedure in the Access database I'm exporting to and then calling that function from Excel. Thanks for everyone's replies!
0
 
tbsgadiCommented:
Glad to help!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now