Macro to Import Text File: GetOpenFilename???

[bethanika]

I don't really know VBA but am trying to figure out enough to create automation for importing text files into an existing workbook. I recorded a macro and tried to decipher the code, but where I am stuck has to do with wanting a different file name each time. I want to specify which file to import, yet have the rest of the work done for me. Is there a general name to specify a text file? I saw somewhere that GetOpenFilename might be a function to use, but how? This is what the macro recorder did:

Sub ImportEval()
'
' ImportEval Macro
' Macro recorded 3/31/2009 by BJ
'
' Keyboard Shortcut: Ctrl+e
'
    Sheets("Sheet1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;F:\Performance Eval Grids\Pro Co Serv Eval_NM.txt", _
        Destination:=ActiveCell)
        .Name = "Pro Co Serv Eval_NM"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 4
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveCell.Offset(0, 9).Range("A1:Y1").Select
    Selection.Copy
    Sheets("Grading Grid").Select

 

[Von Pookie]

This is basically using the code from the VBA help file entry for "getopenfilename", but should at least get you started:

Dim fName As String

'display dialog for user to select a text file
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

'if they click cancel, macro ends here
If fName = False Then Exit Sub

'otherwise, code continues here
With Sheets("Sheet1").QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=ActiveCell)

Also, I would highly suggest replacing ActiveCell with the actual range you want to start it at.

 

[ZVI]

This is basically using the code from the VBA help file entry for "getopenfilename", but should at least get you started:

Dim fName As String

'display dialog for user to select a text file
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

'if they click cancel, macro ends here
If fName = False Then Exit Sub

'otherwise, code continues here
With Sheets("Sheet1").QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:=ActiveCell)

Also, I would highly suggest replacing ActiveCell with the actual range you want to start it at.

A little correction:

Dim fName As Variant
…
If fName = False Then Exit Sub

Or:

Dim fName As String
…
If fName = "False" Then Exit Sub

 

[Von Pookie]

That's true. I didn't test that so didn't even catch it

 

[bethanika]

Thanks for both replies. I tried it and it got me part of the way...but it's giving me a runtime error--a type mismatch on the query tables part. Could it have to do with the fact that I filled in the sheet title and specified the active cell? That's all I changed! This is where I get the error:

'otherwise, code continues here
With Sheets("ImportData").QueryTables.Add(Connection:= _
"TEXT;" & fName, Destination:="A1")

 

[Von Pookie]

A thought: if all you are doing is essentially "writing over" the existing pivot table, you don't need to add a new one. You can just change the connection in the existing table:

With ActiveSheet.QueryTable 'change to whatever sheet/range your table is on
    .Connection = "TEXT;" & fName
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    ' etc. ...

 

[ZVI]

B.J., you can start with such working code:

<font face=Courier New>
Sub ImportTxt()
  
  ' Modify the constants below according to your import configuration
  Const qName = "Pro_Co_Serv_Eval_NM" ' <-- Don't use space chars in name!
  Const SheetImport = "ImportData"
  Const CellImport = "A1"
  
  Dim fName As Variant

  ' Display dialog for user to select a text file
  fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

  ' If cancel then macro ends here
  If fName = False Then MsgBox "Canceled": Exit Sub

  ' Import text file
  With Sheets(SheetImport)
    .Range(CellImport).CurrentRegion.ClearContents
    With .QueryTables.Add(Connection:="TEXT;" & fName, Destination:=.Range(CellImport))
      .Name = qName
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFilePlatform = 1252
      .TextFileStartRow = 4
      .TextFileParseType = xlDelimited
      .TextFileTextQualifier = xlTextQualifierNone
      .TextFileConsecutiveDelimiter = False
      .TextFileTabDelimiter = False
      .TextFileSemicolonDelimiter = False
      .TextFileCommaDelimiter = False
      .TextFileSpaceDelimiter = False
      .TextFileOtherDelimiter = "|"
      .TextFileTrailingMinusNumbers = True
      .Refresh BackgroundQuery:=False
      .Delete ' <-- Close quire
    End With
    
    ' Remove all names added by quire calls
    For Each n In .Names
      If InStr(1, n.Name, qName, 1) > 0 Then
        n.Delete
      End If
    Next
    
  End With
  
  ' Something else
  ' ...
  
End Sub</FONT>

Regards,
Vladimir

 

[bethanika]

Thank you--I have it working now! By the way, can anyone recommend the best (and fastest) way to learn the world of Excel macros/VBA? It's still quite confusing for me, but I need to learn it for my job.