Please Help with Access and Excel Importing and Code

[ALaurie10]

I am somewhat familar with access, but have no clue to modules and coding. I am trying to develop code or a module that will enable a user to import a specific excel spreadsheet and its' data into a table by pressing a command button. I am able to import excel, however it will not attach to existing spreadsheet. I find myself, having to build an append query for each import which is time consuming and inefficient. I have already checked, my excel columns have the same data. Essentially, I need my command button to browse for the excel file, and import one of the spreadsheets into a specified table. Please help.

[e-mail]

[FishVal]

Hi, there.

• I am able to import excel, however it will not attach to existing spreadsheet. I find myself, having to build an append query for each import which is time consuming and inefficient.

  Programmatical iterating through spreadsheet and appending rows to a table will be far more inneficient and time consuming. I suggest you for import purposes to temporarily link Excel ranges and perform append query.
  Something like
  
  

  With DoCmd
      .TransferSpreadsheet  acLink, acSpreadsheetTypeExcel9, _
            "tblTempLink","X:\qqq.xls", , "Sheet2!A:D"
      .RunSQL "INSERT INTO tblImport ( fld1, fld2, fld3, fld3) " & _
          "SELECT tblTempLink.F1, tblTempLink.F2, tblTempLink.F3, tblTempLink.F4 " & _
          "FROM tblTempLink;"
      .DeleteObject acTable,"tblTempLink"
  End With
   

• export is much more simple issue
  
  

  DoCmd.TransferSpreadsheet  acExport, acSpreadsheetTypeExcel9, _
      "tblSomeTable", "X:\qqq1.xls", , "Sheet1!A:D"
   

• and finally concerning file browsing:
  take a look at this thread file browse button on a form

[ALaurie10]

Thanks FishVal:
I your assistance is greatly appreciated. Please pardon me if I am a little more than naive, but a couple of more questions if I may....

1. Do I paste the code you gave me into the Event Procedure of the command button?

2. I am unfamilar with the color coding, do I change the code written in red with my own?

3. I did perform the browsing code, in a module, and it worked. Do I paste this code before or after the import code that you gave me in the same module?

4. How do I link the code in the module to a macro that will enable me to call the code or link the module directly to the command button?

4. Finally, do I need to create the table that will hold my imported data first, or will access automatically create a temp import table with the code each time? If so, how can I keep the data in another table?

[FishVal]

Ok. To answer your questions I've written a small example.
DB contains:
Table: tblImport with 4 fields: fld1, fld2, fld3, fld4
Form: contains one button [btnImportXLData]

Here is the code of button [btnImportXLData] Click event handler.

Private Sub btnImportXLData_Click()
 
    Dim dlgPickFiles As Office.FileDialog
    Dim strFileName As String
 
    Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
 
    With dlgPickFiles
        .AllowMultiSelect = False
        With .Filters
            .Clear
            .Add "Excel files", "*.xls"
        End With
        .Show
        strFileName = .SelectedItems(1)
    End With
 
    Set dlgPickFiles = Nothing
 
    With DoCmd
        .TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
              "tblTempLink", strFileName, False, "Sheet2!A:D"
        .RunSQL "INSERT INTO tblImport (fld1, fld2, fld3, fld4) " & _
            "SELECT tblTempLink.F1, tblTempLink.F2, tblTempLink.F3, tblTempLink.F4 " & _
            "FROM tblTempLink;"
        .DeleteObject acTable, "tblTempLink"
    End With
 
End Sub
 

This simple example imports data to table [tblImport] from chosen XL file, Sheet2, Columns A:B. Get this working and will proceed to more friendly interface including selection of sheet to import data from.

[ALaurie10]

Not a problem, give me a couple of days, Have a trainning and unfortunately it is not for programming. Will get this going by Friday.

Thanks