• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2013
  • Last Modified:

VBA Export EXCEL data to ACCESS table 2007

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
JP_TechGroup
Asked:
JP_TechGroup
  • 7
  • 6
  • 2
  • +1
1 Solution
 
rockiroadsCommented:
do you want to run from excel or access?

0
 
JP_TechGroupAuthor Commented:
Run from Excel. Thanks.
0
 
Kelvin SparksCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
JP_TechGroupAuthor Commented:
How can I run a docmd from Excel vba?
0
 
rockiroadsCommented:
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
 
Jerry PaladinoCommented:
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
 
Jerry PaladinoCommented:
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
 
JP_TechGroupAuthor Commented:
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
 
Jerry PaladinoCommented:
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
 
Jerry PaladinoCommented:
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
 
JP_TechGroupAuthor Commented:
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
 
Jerry PaladinoCommented:
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
 
JP_TechGroupAuthor Commented:
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
 
JP_TechGroupAuthor Commented:
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
 
Jerry PaladinoCommented:
Try   "IS NOT NULL"  you have "IN NOT NULL"
0
 
JP_TechGroupAuthor Commented:
>Quietly replacing in with is<
Thank you! Have some points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now