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)

[Code=vb]
Private Sub Command1_Click( )
On Error GoTo Err_Command1_Cl ick

Dim dlgOpen As FileDialog
Dim InitialFileName As String
Dim retFile As String, strPath As String

Set dlgOpen = Application.Fil eDialog(msoFile DialogOpen)
With dlgOpen
.Title = "Select File"
.AllowMultiSele ct = False
.Filters.Add "CSV Files Only", "*.csv", 1
.InitialFileNam e = "C:\"
.Show
If .Show = -1 Then strPath = .SelectedItems( 1)
End With
If strPath <> "" Then
retFile = Right(strPath, Len(strPath) - InStrRev(strPat h, "\"))
retFile = strPath

DoCmd.TransferS preadsheet acImport, (acSpreadsheetT ypeExcel8), "tbltest1", "Test"
'DoCmd.Transfer Text acImportDelim, , "tbltest2", "MiscPLRep"

MsgBox ("Finished importing File")
End If

Exit_Command1_C lick:
Exit Sub

Err_Command1_Cl ick:
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_C lick


End Sub
[/Code]

Thanks

[MikeTheBike]

Hi

I never use DoCmd.TransferS preadsheet (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