Import File issue

[NigelBrown]

Hi All,
I have the below code but have a couple of problems that I cannot solve, firstly when I select a file the dialog box will promt me to select the file a second time then import, even though it works would like it to import on first select ? also would anyone know how I would write this so it can either import a xls or a txt file depending on file type ? the xls import works fine but when i change it to the acImportDelim it will not (would like both though)

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
 
Dim dlgOpen As FileDialog
Dim InitialFileName As String
Dim retFile As String, strPath As String
 
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
    .Title = "Select File"
    .AllowMultiSelect = False
    .Filters.Add "CSV Files Only", "*.csv", 1
    .InitialFileName = "C:\"
    .Show
   If .Show = -1 Then strPath = .SelectedItems(1)
End With
   If strPath <> "" Then
   retFile = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
   retFile = strPath
 
  DoCmd.TransferSpreadsheet acImport, (acSpreadsheetTypeExcel8), "tbltest1", "Test"
  'DoCmd.TransferText acImportDelim, , "tbltest2", "MiscPLRep"
 
    MsgBox ("Finished importing File")
   End If
 
Exit_Command1_Click:
Exit Sub
 
Err_Command1_Click:
If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid file, please try again", vbExclamation, "Invalid File"
Else
MsgBox Err.Description
End If
Resume Exit_Command1_Click
 
 
End Sub
 

Thanks

[MikeTheBike]

Hi

I never use DoCmd.TransferSpreadsheet (or any DoCmd.Transfer...) commands so cannot comment on their operation but to eliminate double dialogue I suggest this

With dlgOpen 
    .Title = "Select File" 
    .AllowMultiSelect = False 
    .Filters.Add "CSV Files Only", "*.csv", 1 
    .InitialFileName = "C:\" 
    If .Show = -1 Then 
        strPath = .SelectedItems(1) 
    else
        exit sub
    end if
End With 
 

ie. you only need '.Show' once because the Show method dosed what is says (shows the Dialogue!)


MTB

[NigelBrown]

Thanks Mike, cannot beleive I did not see that !!! - works now just need to resolve the docmd piece.

[NigelBrown]

Hi All,

Does anyone know how to get the Docmd.Transfer to work regardless whether it is a txt or xls file ??? I would like both - also is it possible to not have a file name ? the file name I would be selecting changes daily (i.e. nigel_221209)

Thanks