Importing data connection from text (Excel) FAST WAY

Question

Is there a faster way to import data from text into Excel sheet then clicking and defining parameters. There are users who do not know how to use import wizard. Is there a possibility to do that via Macros??

Imported data is always in the same format (in notepad) and the values need to be stored always in the same range so that formula on the other sheet would work.

Thanks

Answer 1

As suggested by others you can easily do it with the recording. Here's a sample to satisfy your need.

 Dim src_file_name As String
 src_file_name = openDialog
    ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(1)).Name = "temp_text"
    With ThisWorkbook.Worksheets("temp_text").QueryTables.Add(Connection:= _
        "TEXT;" & src_file_name _
        , Destination:=ThisWorkbook.Worksheets("temp_text").Range("$A$1"))
        .Name = "text_to_excel"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

As you mentioned you feel to ask the user to pick the text file. Just include the below method -

Function openDialog() As String
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

  .AllowMultiSelect = False

  ' Set the title of the dialog box.
  .Title = "Please select the file."

  ' Clear out the current filters, and add our own.
  .Filters.Clear
  .Filters.Add "Text Files", "*.txt"
  .Filters.Add "All Files", "*.*"

  ' Show the dialog box. If the .Show method returns True, the
  ' user picked at least one file. If the .Show method returns
  ' False, the user clicked Cancel.
  If .Show = True Then
    openDialog = .SelectedItems(1)

  End If
End With
End Function

(Note - Don't forget to call this function above as I did.)