Text import

djgenesis · July 31st, 2007, 10:49 AM

Hello all,
I am trying to import a text file onto Excel, and I would like the user to point where the file is:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\HERE_IT_IS.txt", _
        Destination:=Range("A1"))
        .Name = "1.RNC_EXPORT"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True

I would like to add a command that throws a browser window where the user can click on and locate the file himself. Like the "File - Open" command in excel.

Is this doable?

Cheers

allenm · August 1st, 2007, 11:59 AM

This is relatively easy.

Make a command button called GetFile_Button as below:
-----------------------------------------------------
Private Sub GetFile_Button_Click()

  Dim sFile As String, fsDialog As FileDialog
  Set fsDialog = Application.FileDialog(msoFileDialogFilePicker)
  With fsDialog
    .Title = "Select txt file to open"
    .Filters.Add "Text Files", "*.txt", 1
    .Show
    If .SelectedItems.Count < 1 Then sFile = "" Else sFile = .SelectedItems(1)
  End With
  MsgBox sFile

End Sub
-----------------------------------------------------

You'll have to trap the value(s) yourself and determine if it's an appropriate file or not. I find .InitialFileName useful to trim off the path.

Hope this helps.

djgenesis · August 2nd, 2007, 05:18 AM

Hello allenm

Thank you very much for your response. Much appreciated.

As for the file check, I'll train my users to do it themselves.

Again, it was very helpful ! Thanks