Solved

VBA Export EXCEL data to ACCESS table 2007

Posted on 2010-08-30
16
1,878 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
  • 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
 

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 500 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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

18 Experts available now in Live!

Get 1:1 Help Now