[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

VBA Export EXCEL data to ACCESS table 2007

Posted on 2010-08-30
16
Medium Priority
?
1,966 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
 
LVL 1

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

Author Closing Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

650 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