Import Excel Docs to Access

[slickp007]

Hi, I have created a Policy database in access, and the user has requested that they have an import button to be able to import excel spreadsheets in the database. I was going to provide them with an import template, and then provide them with an import button with the following procedure attached to it:

Private Sub Import_Click()
' Import orders from Excel file Orders.xls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCustomerOrders", "C:\orders.xls", True

End Sub


Is there anyway in the above where i can provide the user with a import search box like you get when you go File > Get external data > Import, so that they can search a network drive for the excel spreadsheet they require?

Also do you feel this is the best solution, or would you think that having a linked table to an excel spreadsheet would be better and just have the user add to that everytime they want to add a new entry?

Cheers

 

[MikeC14081972]

Declare fName as a string in the header of your module then add the following code.

In the import Spreadsheet command line replace "C:\orders.xls" with fName

Function Dialog()

Dim fDialog As Office.FileDialog
Dim varFile As Variant

On errror GoTo ErrHandler

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
    .AllowMultiSelect = True
    .Title = "Please Select One or More Files"
    .Filters.Clear
    .Filters.Add "Excel File", "*.xls"
    If .Show = True Then
        For Each varFile In .SelectedItems
        
        fName = varfile          
             
        Next
    Else
        MsgBox ("You Clicked Cancel in the File Dialog Box.")
    End If
End With
Exit Function
ErrHandler:
   ' Insert your error handler
Exit Function
End Function

Hope that helps

 

[MikeC14081972]

Meant to add change

.AllowMultiSelect = True

to False to allow only one file to be selected as I have removed some code to cope with multiple selections.

Also required a reference to the Microsoft Office Object Library to be added.

 

[slickp007]

I'm getting a compile error on the following:

Dim fDialog As Office.FileDialog

"Compile error: User defined type not defined"

Any help?

 

[MichaelRed]

I have found that importing foregin 'data' into an ative table type recordset to be a hazzardous prospect. Excel in particular has little to no enforcement of data typing, so many of the files will have text in place ov values, and partial dates. These will generally cause problems when imported directly into active 'tables'.

Also worth consideration is wheather the Excel file is a 'worksheet' or a 'workbook' with possibly several different spreadsheets.

A better approach is, for me, to always import foregin data into a temporary table type record set, run a set of V&V procedures on the fields of each record to assure that they are of the type expected and only after passing the tests to then append them to the active recordset. V&V should check not only the data types, but also the (expected) range of all non text fields. For text fields, you can realistically only check the length of the entry unless there is a simple list of expected values.

I also generally either accept or reject the entire dataset. when rejecting, I return the data set to the originator with one or more added columns specifying the 'errors' encountered. This protects the integrity of the internal data and provides the originator both the opportunity to correct the problems and to 'learn' to be careful in entering the information.



MichaelRed

 

[MikeC14081972]

Did you add a reference to Microsoft Office Object Library

 

[slickp007]

Sorry being dumb, how would i do that?

 

[MikeC14081972]

In the VBA IDE select Tools>References then scroll down the list and check the required reference.

 

[PHV]

FYI, the FileDialog object has been removed from Office2007.

 

[slickp007]

Wicked!! Thanks for all your help!!