Allow user to choose the file to import

Try this.

Private Sub cmdImport_Click()
Dim xl As Object
Dim strName As String
Dim strImportFilename As String

    DoCmd.OpenQuery "qryAppend_ImportFC"   'appends to an archive

    strImportFilename = PickFile

    If strImportFilename <> "" Then
        Set xl = CreateObject("Excel.Application")
        xl.Workbooks.Open strImportFilename
        xl.Run "Transpose2"
        xl.ActiveWorkbook.Close (True)
        xl.Quit
        Set xl = Nothing
        CurrentDb.Execute "delete * from [Import_FC]"
        strName = "Transpose"
    Else
        MsgBox "No file picked."
    End If

End Sub

Function PickFile() As String
Dim fDialog As Object
Dim varFile As Variant

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog

        .AllowMultiSelect = False
        .Title = "Please select a workbook to import"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*"

        If .Show = True Then

            varFile = .SelectedItems(1)
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
        
    End With

    If varFile <> "" Then
        PickFile = varFile
    End If
    
End Function

Select allOpen in new window

Thank you Norie.  I will try it when at a computer...typing from phone.  Appreciate the response.

The top function helps to find the file name to open.

The sub is your sub modified, to call the function returning the name getFileName.
you put getFileName in place of the actual path of the file name.

Norie:
It says "Variable not defined" and highlights:
Function PickFile() As String

hnasr:
What shall I use for the top row?  Not sure how to explain but I think its the Function line.  I think its missing.

hnasr:  works great!  I modified the top line to add the function Function getFileName() As String.

it does seem to select the file 2x though but I can live with that.

Sorry, forgot to comment unneeded statements in sub, these were moved to function.

Private Function getFileName()
   Dim fDialog As office.fileDialog
   Dim varFile As Variant
   Set fDialog = Application.fileDialog(msoFileDialogFilePicker)

    With fDialog
      .Title = "Please select one or more files"
       .Filters.add "Access Databases", "*.xls"
      .Filters.add "Access Projects", "*.xlsm"
      .Filters.add "All Files", "*.*"

      If .Show = True Then
        getFileName = .SelectedItems(1)
      Else
         getFileName = ""
      End If
   End With
End Function

Private Sub fileDialog_Click()
Dim FName As String
FName = getFileName()
If FName = "" Then
    MsgBox "You clicked Cancel in the file dialog box."
    Exit Sub
End If
    Dim xl As Object
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Open FName ' Modified line =========================
            xl.Visible = True
'f = getFileName()
   'Dim fDialog As office.fileDialog
   'Dim varFile As Variant
   'Set fDialog = Application.fileDialog(msoFileDialogFilePicker)

    'With fDialog
      '.Title = "Please select one or more files"
       '.Filters.add "Access Databases", "*.MDB"
      '.Filters.add "Access Projects", "*.accrd"
      '.Filters.add "All Files", "*.*"

      If FName = "" Then
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   'End With
End Sub

Select allOpen in new window

hnasr,

thank you for the changes made.  I have followed howeverit still loops through the file selection 2x.  Let me know what is the next step.

pdvsa

How exactly did you use the code I posted?

I've tested it multiple times and have never had the problem you describe occur.

PS Do you want the user to select one file or multiple files? The code I posted only allows the user to select one file, just saying.:)

Sorry again, here is a sample database.
It is always very helpful to send a sample database. It takes less time and effort to resolve issues.
fileDialog.accdb

Hello Hnasr, sorry for the confusion.  

In the db attached, I do not see the docmd transfer spreadsheet code and append.  

Let me copy here what I have.  Maybe it will be more clear hopefully.  Possibly the placement of the append and docmdtransferspreadsheet is not correct.  

Private Sub cmdImport_Click()

  Dim FName As String 'added lin ***
    FName = getFileName() 'added lin ***
    If FName = "" Then 'added lin ***
         MsgBox "You clicked Cancel in the file dialog box."
            Exit Sub 'added line ***
    End If 'added line ***
    
'    If MsgBox("Do you want to Append data to Archive?", vbYesNo) <> vbYes Then
        DoCmd.OpenQuery "qryAppend_ImportFC"   'appends to an archive
 '           Exit Sub
  '  End If
    
         Dim strName As String
         Dim xl As Object
            Set xl = CreateObject("Excel.Application")
            xl.Workbooks.Open (getFileName()) ' Modified line ***
            xl.Run "Transpose2"
            xl.ActiveWorkbook.Close (True)
            xl.Quit
            Set xl = Nothing
             
   '          If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then
                CurrentDb.Execute "delete * from [Import_FC]"

    '         Exit Sub
     '        End If
             
            strName = "Transpose"
        
        DoCmd.TransferSpreadsheet acImport, , "Import_FC", "C:\Users\pdvsa\Desktop\Import_FC.xlsm", True, strName & "!"
        DoCmd.OpenQuery "qryDeleteNullsImportFC"

End Sub

Select allOpen in new window

Function getFileName() As String
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog
      .Title = "Please select your file"
       .Filters.Add "Access Databases", "*.xlsm"
     ' .Filters.Add "Access Projects", "*.accrd" add any other ***
      .Filters.Add "All Files", "*.*"

      If .Show = True Then
        getFileName = .SelectedItems(1)
      Else
         getFileName = ""
      End If
   End With
End Function

Select allOpen in new window

Hi Hnasr,

Just checking in.  Not sure if you read my comment above.  

thank you.  talk to you soon.

I commented n response to prompt for file name to import from.

If you need to integrate it in other code, that will be another requirement.

Uploading the file that has Form1 and Copy Of Form1 which includes old code.

To expand on this, you may open a new thread, upload the file and explain the requirement.
fileDialog.accdb

Ok I posted another question here:'

https://www.experts-exchange.com/questions/28982461/Allow-user-to-choose-the-file-to-import-loops-2x.html

thank you....

Please don't forget to upload a sample database, showing what is working and explain any extra requirement.