Solved

VBA Export EXCEL data to ACCESS table 2007

Posted on 2010-08-30
16
1,893 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

19 Experts available now in Live!

Get 1:1 Help Now