Importing data using a button

[wilbr73]

Hello,

I am new to the forum and new to Access 2010 but do have some experience with older versions of Access (I am by no means a power user). I am trying to set up an import data button so that customers can enter data into an excel spreedsheet and then I can have the database users import the data with a click of a button. What is the easiest way to do this? I want to set it up so that this function can be automated from inside a form and not have to click the "External Data" tab from the tools ribbon.

Thanks in advance for your help!
Will

[alansidman]

Here is an example of what I use.

Private Sub Command8_Click()
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author    : jed
' Date      : 1/16/2009
' Purpose   : To import all worksheets in a specified workbook into
'             individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
' Make sure that the file Pipeline Report.xls is located on your H:\ Drive.  You may have to change the path
' if you are using a newer version of Windows.  This was designed to run on Windows XP.
'---------------------------------------------------------------------------------------
'This first line of code will delete the contents of the current table.
DoCmd.DeleteObject acTable, "Pipeline Report"
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
   On Error GoTo ImportXLSheetsAsTables_Error
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("H:\Pipeline Report.xls")
  
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, 9, "Pipeline Report", "H:\Pipeline Report.xls", True, sh.Name & "!"
Next
wb.Close
appExcel.Quit
MsgBox "Import Completed"

   On Error GoTo 0
   Exit Sub
ImportXLSheetsAsTables_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

[wilbr73]

Thanks alansidman. Is there a way to append it to an existing table? I have samples that are submitted over time (4 or 5 samples at a time for a couple months) that I need to keep adding to a project so my hope is to have the option to keep adding onto a sample table for that project. Does that make sense?

In the mean time I will give your code a try.

Thanks,
Will

[wilbr73]

I tried the code listed above but got the following error: "Compile error: User defined type not defined." What have I not defined properly?

[alansidman]

To answer your first question, you can bring the data into a temporary table and then run an append query. Without seeing how you modified the code I gave you, I don't know what you have not defined. When you look at the code, which line of code is highlighted?

Have you referenced the Excel Object Library as noted in the comments at the start of the code?

Alan

[wilbr73]

Here is the line of code that is highlighted: Dim appExcel As Excel.Application I have tried to find the area to reference the Excel Object Library but I can't find where it is located in the ribbon. I looked in "Help" to try to find out where to do that but the help is not so helpful.

I have changed the file path and file name to my specific file.

Here is the code I have used.
Private Sub Command29_Click()
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
On Error GoTo ImportXLSheetsAsTables_Error
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\All Users\Documents\Will Brown\NewSamplesForImport.xls")

For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, 9, "New_Samples", "C:\Documents and Settings\All Users\Documents\Will Brown\NewSamplesForImport.xls", True, sh.Name & "!"
Next
wb.Close
appExcel.Quit
MsgBox "Import Completed"
On Error GoTo 0
Exit Sub
ImportXLSheetsAsTables_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

Sorry for being a VBA bone head! I have toyed with making the leap from macros for everything to VBA but never have had a reason. Now I do.

Thanks again for the help.
Will

[alansidman]

You need to do it in the VBA editor. Click on Tools and then References. Find the reference to XL and check the box and save it.

Alan