?
Solved

VBA Export EXCEL data to ACCESS table 2007

Posted on 2010-08-30
16
Medium Priority
?
1,949 Views
Last Modified: 2012-05-10
I am seeking a simple means of exporting or sending data from an excel spreadsheet (Sheet 6)
to an Access table in an existing database (.accdb) using VBA. The version of both is 2007. Can anyone suggest a reliable means of doing this? Thanks!
0
Comment
Question by:JP_TechGroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33562556
do you want to run from excel or access?

0
 

Author Comment

by:JP_TechGroup
ID: 33562583
Run from Excel. Thanks.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 33562585
Initially do it as an import in Access. At the end of the wizard you get the option to save the process - do this and give it a name.
 
Then in VBA use DoCmd.RunSavedImportExport "NameofSavedImport" to rerun this.
 
Kelvin


0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:JP_TechGroup
ID: 33562603
How can I run a docmd from Excel vba?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33562680
something like this perhaps
change False to True for headings


    Dim sRemoteDB As String
    Dim dbData As Object
    Dim i As Integer
   
   
    sRemoteDB = "c:\temp\access.mdb"
    Set dbData = CreateObject("Access.Application")
    dbData.opencurrentdatabase (sRemoteDB)

    dbData.DoCmd.TransferSpreadsheet acImport, , "MyTable", ActiveWorkbook.FullName, False, "Sheet6!"

    dbData.CloseCurrentDatabase
    Set dbData = Nothing
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33563136
The sample code below uses ADO to write the Excel records to Access.  In it's current state it will clear out the contents of the Access table before it exports the data to the Access table so the table is refreshed each time it is rum.  Remove the line:

cn.Execute "DELETE FROM DataFromExcel"

to make it an APPEND query that will add(append) the Excel records to the exisitng records in the Access table each time it is run.

Jerry
Sub ExportExcelDataToAccess()

' Procedure assumes that:
'   The Access db is - C:\EE\Sample.accdb
'   Access Table name is - DataFromExcel
'   Excel file is - C:\EE\myExcel.xlsx
'   Worksheet name that contains the expert data is - PartsData
'   Using Access 2007 and Excel 2007 (2003 syntax also included)
'   Requires - VBA Ref Lib: Microsoft ActiveX Data Objects 2.x Library
'
    Dim cn As Object, strQuery As String
    Application.StatusBar = "Replacing Access Table 'DataFromExcel' With Data From MyExcel.xlsx[PartsData]"

    Set cn = CreateObject("ADODB.Connection")

    mydb = "C:\EE\Sample.accdb"
    thiswb = "'C:\EE\myExcel.xlsx'"

    With cn
        '.Provider = "Microsoft.Jet.OLEDB.4.0"    'For Access 2003 Database
        .Provider = "Microsoft.ACE.OLEDB.12.0"    'For Access 2007 Database
        .ConnectionString = mydb
        .Open
    End With

    strQuery = "INSERT INTO [DataFromExcel] " & _
               "SELECT " & _
               " [PartNum] as [PartNum], " & _
               " [BinNum] as [BinNum], " & _
               " [RefNum] as [RefNum] " & _
               "FROM [PartsData$] " & _
               "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' "
            '  "IN " & thiswb & "  'Excel 8.0;HDR=Yes;' "              '  For Excel 2003

    'Delete Access Table Contents before Excel Export
    'To Append the records to the table remove or comment out the DELETE FROM line below
    cn.Execute "DELETE FROM DataFromExcel"
    
    'Export the data from Excel to Access
    cn.Execute strQuery
    
    'Close the database connection
    cn.Close
    Set cn = Nothing
    Application.StatusBar = False

End Sub

Open in new window

0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 33563208
If you are exporting all the fields from the Excel sheet and the column names match the column names in the Access Table you can use the "Select *  " syntax below.
Sub ExportExcelDataToAccess()
    Dim cn As Object, strQuery As String
    Set cn = CreateObject("ADODB.Connection")
    mydb = "C:\EE\Sample.accdb"
    thiswb = "'C:\EE\myExcel.xlsx'"

    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"   'For Access 2007 Database
        .ConnectionString = mydb
        .Open
    End With

    strQuery = "INSERT INTO [DataFromExcel] Select * FROM [PartsData$]" & _
               "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' "
    
    cn.Execute "DELETE FROM DataFromExcel"
    cn.Execute strQuery
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 

Author Comment

by:JP_TechGroup
ID: 33563633
ProdOps, your last example works like a charm, thank you. Is there a way to specify WHERE in the SQL, such that if Column I's relevant cell is not empty then do not export?
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33563797
JP,
>> specify WHERE in the SQL, such that if Column I's relevant cell is not empty then do not export?
Sure, the nice thing about ADO is that you can pass any valid SQL statement you want to Access.   Assume that your column " I " has a column heading of "PartNum"  in the modified VBA below.  Then, only export the records where PartNum is blank.
Jerry

Sub ExportExcelDataToAccess()
    Dim cn As Object, strQuery As String
    Set cn = CreateObject("ADODB.Connection")
    mydb = "C:\EE\Sample.accdb"
    thiswb = "'C:\EE\myExcel.xlsx'"
  
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"   'For Access 2007 Database
        .ConnectionString = mydb
        .Open
    End With
  
    strQuery = "INSERT INTO [DataFromExcel] Select * FROM [PartsData$] " & _
               "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' " & _
               "Where [PartNum] = """"  "
      
    cn.Execute "DELETE FROM DataFromExcel"
    cn.Execute strQuery
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33563821
Sorry - I left the final semicolon off in the SQL statement in the previous code.  Included here.
Sub ExportExcelDataToAccess()
    Dim cn As Object, strQuery As String
    Set cn = CreateObject("ADODB.Connection")
    mydb = "C:\EE\Sample.accdb"
    thiswb = "'C:\EE\myExcel.xlsx'"
  
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"   'For Access 2007 Database
        .ConnectionString = mydb
        .Open
    End With
  
    strQuery = "INSERT INTO [DataFromExcel] Select * FROM [PartsData$] " & _
               "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' " & _
               "Where [PartNum] <> """" ; "
      
    cn.Execute "DELETE FROM DataFromExcel"
    cn.Execute strQuery
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 

Author Comment

by:JP_TechGroup
ID: 33563915
Hmmm, I must have done something wrong. Nothing posts to the Access table now.
I have tried the WHERE clause both as ="" and <> "".
The SQL is attached.
 strQuery = "INSERT INTO [tblRawData] Select * FROM [RawData$] " & _
     "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' " & _
     "WHERE [EXPORTED]= """" ; "

Open in new window

0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33564027
First - Try the SQL statement in the code box that checks for "Is Null" instead of Blank.
If that does not solve it then:
  • Any error message or indication of a problem
  • Double check to see if [EXPORTED] is spelled correctly from the Excel column header?
  • Are there blank cell values in the EXPORTED column?
  • Are you sure the cell values in the EXPORTED column are truly blank?  Any possibility that they have a space in them like " " instead of "" ?


strQuery = "INSERT INTO [tblRawData] Select * FROM [RawData$] " & _  
     "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' " & _  
     "WHERE [EXPORTED]= Is Null; "

Open in new window

0
 

Author Comment

by:JP_TechGroup
ID: 33564059
changing the SQL to "WHERE [EXPORTED] IS NULL; " did the trick... again thank you!
Last question... I need to add an other WHERE command to prevent the import of null lines.
I am getting blank entries in my exports. Any thoughts oh vba sage?
0
 

Author Comment

by:JP_TechGroup
ID: 33566536
I used the below, which should do what I need, but get an error.
IN operator without () in query expression. OYE, syntax will be the death of me.

 strQuery = "INSERT INTO [tblRawData] Select * FROM [RawData$] " & _
     "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes' " & _
     "WHERE ((([EXPORTED]) is null) AND ([WORKORDER]) in not null))); "

Open in new window

0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33566551
Try   "IS NOT NULL"  you have "IN NOT NULL"
0
 

Author Closing Comment

by:JP_TechGroup
ID: 33566668
>Quietly replacing in with is<
Thank you! Have some points!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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