Export range in Excel to MS Access uisng DAO

Hi

I need to export a named range in Excel to MS Access.

Previously I was using  VBA in Access to achieve this using DoCmd.TransferSpreadsheet which worked, but I would now like to avoid launching Access.  I am keen on using ADO or DAO instead, which I have not used before, but from what I have read DAO may be preferable.  I am using MS Office 2010.

I have started to use DAO and I have successfully exported data to Access, either adding of editing records in a table. I am however unsure how I should export a named range in Excel to Access.  The data to be exported is to be appended to an existing table.  The first row in each column of the named range has a header which corresponds with a field name in the Access table.  The ordering of the headers in Excel matches the ordering of the field names in Access.

I would really appreciate if someone could illustrate how I could do this.

Many thanks in advance
Alison
alisonthomAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gowflowConnect With a Mentor Commented:
Alison,

Here you are I found the culprit !!!! you first posted the location of the database together with its name and I included in the code
"C:\Clients\Project_c1.accdb"

However, when you posted the database its name was instead  "Project-c1.accdb" and I did not pay attention between underscore '_' and hyphen '-' reason why I was getting file not found !!!

Anyway, this got you to get 2 for the price of 1 !!! :) :)

Below workbook give you 2 solutions 1 via ADO and the second one via DAO you pick and choose. Both are linked to the buttons in the main sheet.

You may want to try also creating an other range say very far like in Col XW row 560 putting the same header on the first row of that range being the fields and then filling it with data and try running the code to see it should pick up and update data for both ranges.

Enjoy.
gowflow
Example-DAO-ADO-V01.xlsm
0
 
gowflowCommented:
what is the name of your access database (full name and path)
what is the name of the table in Access

Can you post a sample excel of that data ?
gowflow
0
 
alisonthomAuthor Commented:
Hi gowflow

thanks for the response.  I have attached a sample Excel workbook ("Example_DAO.xlsm")  with a populated range called "Area1".

With respect to the information you have asked for:
Access database path = C:\Clients
Database name = "Project_c1"

Many thanks Alison
Example-DAO.xlsm
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
gowflowCommented:
Your missing table name is it output ?
Project_c1.mdb or ... ?
gowflow
0
 
alisonthomAuthor Commented:
Sorry gowflow

the full name for the Access database is "Project_c1.accdb"
and the Access database table name is "ExcelData"

Thanks
Alison
0
 
gowflowCommented:
ok fine will get back to you after the meeting I have I finishes.
gowlfow
0
 
Rey Obrero (Capricorn1)Commented:
place this codes in your excel module

Sub ExportToAccess()
Dim strAccessPath As String, strExcelPath As String
Dim objAccess As Object

strExcelPath = " C:\Clients\Example-DAO.xlsm"
strAccessPath = " C:\Clients\Project_c1.accdb"
Set objAccess = CreateObject("Access.Application")
Call objAccess.OpenCurrentDatabase(strAccessPath)
objAccess.DoCmd.TransferSpreadsheet acimport, 10, "ExcelData", strExcelPath, True, "B1:K10"
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
forgot to close the db
Sub ExportToAccess()
Dim strAccessPath As String, strExcelPath As String
Dim objAccess As Object

strExcelPath = "C:\Users\rey\Downloads\EE\alisonthom\Example-DAO.xlsm"
strAccessPath = "C:\Users\rey\Downloads\EE\alisonthom\Project_c1.accdb"
Set objAccess = CreateObject("Access.Application")
Call objAccess.OpenCurrentDatabase(strAccessPath)
objAccess.DoCmd.TransferSpreadsheet acimport, 10, "ExcelData", strExcelPath, True, "B1:K10"
objAccess.CloseCurrentDatabase
End Sub

Open in new window

0
 
gowflowCommented:
Sorry asking too many questions but as you posted specific DAO I need specifics.
Could you post this sample access database table that you created that have these fields in the excel so I can test the macro I have done ? As you very well know there are keys and data type that need to be respected so If I just throw a code to you that will bounce becoz of either record exist or wrong data fields types or maximum length then we will be in an endless discussion. So better for your to post this Access database that contain the "ExcelData" table and will shortly revert with the code.

You clearly specified an example using DAO this is why I am pursuing or else previous reply on replicating Access from Excel I guess would be fine but is not ur intention.

gowflow
0
 
alisonthomAuthor Commented:
First of all, thank you Rey for your solution.  It is much appreciated.

Gowflow - I have attached an example database with the "ExcelData" table and fields.

Thanks
Alison
0
 
gowflowCommented:
Alison, nothing is attached ! :)
gowflow
0
 
gowflowCommented:
Cannot test it before you attach the file.
gowflow
0
 
alisonthomAuthor Commented:
Sorry Gowflow.  It is now attached.

Alison
Project-c1.accdb
0
 
Rey Obrero (Capricorn1)Commented:
@alisonthom

did you try the codes i posted above?
0
 
alisonthomAuthor Commented:
Hi Rey

Yes, I have successfully used your code.  So, this achieves my objective of not having to launch Access which is great.  When I was reading about the methods available to export data to Access database from Excel/VBA I did not see a suggestion of using DoCmd.TransferSpreadsheet.
The example data I provided is very small, and the actual data I need to export is significantly greater.  When I was using DoCmd.TransferSpreadsheet  within Access VBA the transfer was sometimes slow.  Would an approach using DAO in Excel/VBA provide a quicker transfer?

Thanks
Alison
0
 
Rey Obrero (Capricorn1)Commented:
using DAO recordset would mean that you will require more vba codes.. the codes will be something like this
open the database

    Set db = OpenDatabase(strAccessPath)
' open the table as recordset
    Set rs = db.OpenRecordset("ExcelData")
    intRow=6
'then you  will iterate thru each cells in a row to append 1 record to the access table,
   do until len(range("A" & introw).value >0
          with rs
                .addnew
                !F1=range("A" & intRow).value
               !F1=range("B" & intRow).value
                !F1=range("C" & intRow).value
                ' add more ields
              .update
        end with
0
 
gowflowCommented:
Alison
You mentioned having used DAO you seem to have an Access 2010 table
Are you successful in this instruction:
Set DB = OpenDatabase(sDatabaseName)
where sDatabaseName = "C:\Clients\Project_c1.accdb"

I am getting file not found error. Are you able to get pass this instruction ?
gowflow
0
 
gowflowCommented:
DO you insist on DAO or can you accept ADO ?
gowflow
0
 
alisonthomAuthor Commented:
Hi Goflow

Yes, I can successfully use the instruction:
Set DB = OpenDatabase(sDatabaseName)
 where sDatabaseName = "C:\Clients\Project_c1.accdb"

I would be very happy to use ADO instead of DAO.

Thanks
Alison
0
 
gowflowCommented:
ok will revert tomoorow as on different time zone if no one else got to it before. but meantime here is the solution for DAO that I did not test.

The emphasis here is Named ranges as you mentioned more than the DAO issue that is futile but for some reason the 2010 could not open with me and got me a file not found.

The idea is that any name range wherever it is in your workbook will be spoted and will consider the first row as fields and the rest as data and will update the table set in stablename with the records.

pls try it as it should not give you an error like it did here and let me know.
Meantime when awake will push the ADO one.

here is the code for that

Sub TransferToExcelData()
Dim WS As Worksheet
Dim oName As Name
Dim Rng As Range
Dim DB As DAO.Database
Dim WKS As DAO.Workspace
Dim RS As DAO.Recordset
'Dim DB As Object
'Dim RS As Object
Dim SQL As String
Dim MaxRow As Long, MaxCol As Long, FmRow As Long, FmCol As Long, I As Long, J As Long, FstR As Long, FstC As Long, SecR As Long, SecC As Long
Dim RealTot As Long
Dim sDatabaseName As String, sTableName As String, sRng As String, sSheet As String
Dim Status As Boolean

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
sDatabaseName = "C:\Clients\Project_c1.accdb"
sTableName = "ExcelData"

'---> Open Database
Set WKS = CreateWorkspace("", "admin", "", dbUseJet)
Set DB = WKS.OpenDatabase(sDatabaseName)

'---> Open Recordset
SQL = "SELECT * FROM " & sTableName
Set RS = DB.OpenRecordset(SQL, dbOpenDynaset, , dbOptimistic)

'---> Get the Range Name
For Each oName In ActiveWorkbook.Names
    '---> Find all limits of the Named Range
    sRng = oName.RefersToR1C1
    sSheet = Mid(sRng, 2, InStr(1, sRng, "!") - 2)
    Set WS = Sheets(sSheet)
    RealTot = 0
    FstR = InStr(1, sRng, "R")
    FstC = InStr(1, sRng, "C")
    SecR = InStr(FstR + 1, sRng, "R")
    SecC = InStr(FstC + 1, sRng, "C")
    FmRow = Mid(sRng, FstR + 1, FstC - FstR - 1)
    FmCol = Mid(sRng, FstC + 1, SecR - FstC - 2)
    MaxRow = Mid(sRng, SecR + 1, SecC - SecR - 1)
    MaxCol = Mid(sRng, SecC + 1, Len(sRng) - SecC)
    
    '---> Loop thru all rows of data to update all fields in the table
    For I = FmRow + 1 To MaxRow
        RS.AddNew
        For J = FmCol To MaxCol
            'Debug.Print "Field: " & WS.Cells(FmRow, J).Value & " Data: " & WS.Cells(I, J).Value
            RS(WS.Cells(FmRow, J).Value) = WS.Cells(I, J).Value
        Next J
        RS.Update
        RealTot = RealTot + 1
    Next I

Next oName



'---> Enable Event
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
End With

'---> Advise User
If Status Then
    MsgBox ("A total of " & RealTot & " Records were transfered successfuly.")
Else
    MsgBox ("No data was transfered.")
End If

End Sub

Open in new window


You will notice I declared Dim DB as ADO.Database this means you will need to add reference to Microsoft Office 14 Access Data object Library or else if you don't want simply change the ADO declarations with Object for the 3 items
Dim DB As Object
Dim WKS As Object
Dim RS As Object

gowflow
Example-DAO.xlsm
0
 
alisonthomAuthor Commented:
Hi Gowflow

Many thanks for this.  It is most appreciated.

The code executes as far as line 33 (i.e. Set RS = DB.OpenRecordset(SQL, dbOpenDynaset, , dbOptimistic)
at which point the following error is thrown:

Run-time error 3001
Invalid argument

The following values are used:
SQL = "SELECT * FROM ExcelData"
dbOpenDynaset = 2
dbOptimistic = 3

Thanks again
Alison
0
 
gowflowCommented:
ok then replace this
Set RS = DB.OpenRecordset(SQL, dbOpenDynaset, , dbOptimistic)

by this
Set RS = DB.OpenRecordset(SQL, dbOpenDynaset)


gowflow
0
 
alisonthomAuthor Commented:
Hi Gowflow

Thank you so much!!!
Both approaches work brilliantly and I have been able to successfully use the code with multiple ranges.

I can't thank you enough.
Alison

P.S. Thank you too Rey for your response.  It's most appreciated.
0
 
gowflowCommented:
Your welcome Alison and glad I could help.
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.