import excel into access from form-CodePudding

I am very new so apologies in advance.

I am wanting to import spreadsheets that are all the same format.

It is a simple data entry database.

The data is being entered on many client computers (there is no network) and then imported into one. The exported filenames may vary so I want the end user to be able to import the files one by one, but can select a different named file from the dialogue box. I have been trying to get the filename data_entry.xlsx to be wild cards. I have tried dimming as a string, but then you have to give the string an actual filename.

At the moment the following code imports a file Data_entry.xlsx if it is found, then opens a dialogue box where the user thinks they can choose a file of any name and import it. In fact the import has already occurred.

The software is being used on remote Pacific Island where IT support is not great or I would use the import spreadsheet feature of Access. Instead I just want the database to allow the end use to browse for the excel spreadsheet of any name and import it. Code is below – I have a 7 day timeframe.

Private Sub Command4_Click()

Dim ffx As Object

Set ffx = Application.FileDialog(3)

'Dim fileName As String


ffx.AllowMultiSelect = False

DoCmd.TransferSpreadsheet acImport, , "Data_entry_import ", "data_entry.xlsx", True, "b1:s9000"

If ffx.show = True Then
MsgBox "Success!"

Else

 
MsgBox "No file was imported"
 End If
 

End Sub

CodePudding user response：

Use the filename, the user has selected:

Private Sub Command4_Click()

    Dim ffx         As Object
    Dim FileName    As String
    Dim FolderName  As String

    Set ffx = Application.FileDialog(3)
    ffx.Title = "Select Excel file to import"
    ffx.AllowMultiSelect = False
    ffx.Filters.Clear
    ffx.Show

    If ffx.SelectedItems.Count = 0 Then
        ' User cancelled.
        MsgBox "No file was imported."
    Else
        FileName = ffx.SelectedItems(1)
        FolderName = Left(FileName, InStrRev(FileName, "\"))
        DoCmd.TransferSpreadsheet acImport, , "Data_entry_import ", FileName, True, "b1:s9000"
        MsgBox "Success!"
    End If        

    Set ffx = Nothing

End Sub

Also, do rename Command4 to something meaningful.