Solved

Export range in Excel to MS Access uisng DAO

Posted on 2015-02-08
24
112 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:alisonthom
  • 12
  • 8
  • 4
24 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40596963
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
 

Author Comment

by:alisonthom
ID: 40596987
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40596990
Your missing table name is it output ?
Project_c1.mdb or ... ?
gowflow
0
 

Author Comment

by:alisonthom
ID: 40596994
Sorry gowflow

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

Thanks
Alison
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40597043
ok fine will get back to you after the meeting I have I finishes.
gowlfow
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40597066
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40597088
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40597846
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
 

Author Comment

by:alisonthom
ID: 40598348
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40598564
Alison, nothing is attached ! :)
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598807
Cannot test it before you attach the file.
gowflow
0
 

Author Comment

by:alisonthom
ID: 40598831
Sorry Gowflow.  It is now attached.

Alison
Project-c1.accdb
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40598840
@alisonthom

did you try the codes i posted above?
0
 

Author Comment

by:alisonthom
ID: 40599035
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40599117
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40599207
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40599242
DO you insist on DAO or can you accept ADO ?
gowflow
0
 

Author Comment

by:alisonthom
ID: 40599303
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40599368
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
 

Author Comment

by:alisonthom
ID: 40599481
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40600151
ok then replace this
Set RS = DB.OpenRecordset(SQL, dbOpenDynaset, , dbOptimistic)

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


gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40600286
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
 

Author Closing Comment

by:alisonthom
ID: 40601737
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40602495
Your welcome Alison and glad I could help.
gowflow
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now