Easy import form in Access

Question

how to create an access form that has an excel file import button. and after selecting the excel file, it automatically creates a table in the database with column headings, because the first row and Excel data are superior to other rows. If you think that I am not making any efforts, please give me an offer or a link and do it poorly yourself.

Answer 1

For versions of Access since 2003, you can use File Dialogto allow the user to view the desired file, earlier you can use this API calls. If this is too much for you, you can enter the user type in the name and path to the file, but you will need to check if it exists using code (possibly Dir).

It would be better to use the TransferSpreadsheetDoCmd object method (available in any version of Access from, AFAIK, 1997) to import the table. This can be run as a VBA (code) or macro.

Answer 2

If we assume that you can create a form and connect to a button, you have two problems:

• File Open Dialog.
• Start import.

1 Microsoft - VB.OLD Access ( 2007), COM Access, .

2 - , , , , , - , , , VBA. VBA , Excel, , .

, - , - - excel .

Answer 3

An example of using Access 2003 to select a file is as follows:

Dim fDialog  As Office.FileDialog
Dim strFile As String
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .InitialFileName = "C:\temp\*.xls"
    .Filters.Clear
    .Filters.Add "Excel file", "*.xls"
    .Filters.Add "All Files", "*.*"
    If .Show = True Then
        strFile = .SelectedItems(1)
    End If

End With

Debug.Print strFile

Note that you will need to add a link to the Office 12 object library

To import a file, you can use the TransferSpreadsheet function of the DoCmd object. For example,

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExcelImport", strFile, True

An access table called ExcelImport must already exist in the database.