Solved: Variable .txt file import, Please help!

[mechanize]

Hey everyone! I am needing help with importing a .txt file with a variable name. I am not a very good visual basic programmer. Problem seems to be in the red part, but it may be somewhere else. I want to have a macro automatically populate a shopping list depending on the meal chosen. So if the meal is Italian Chicken, I want all the ingredients to be added to another sheet. The text file will be named the same as the meal title. If someone could help me please, this would make my next semester food preparation be so much easier! Here is the macro code I currently have:

Sub Import_txt()
'
' Import_txt Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("D5").Select
ActiveCell.FormulaR1C1 = "Item 1"
Range("D14").Select
With ActiveSheet.QueryTables.Add(Connection:= _"TEXT;C:\Users\Kal\Desktop\" & Range("D5").Value , Destination:=Range("$D$14"))
.Name = "Item 1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

[GTO]

Could you zip the text file and the wb with the code as you have it?

[mechanize]

I have attached the workbook as well as a sample .txt file that I'm trying to import. Let me know if I didn't explain something very well. I tried to explain some things on the first page of the workbook. Thanks!

[mdmackillop]

The underscore before "TEXT" is a line contination character. Remove it and all should be well.
BTW, Please use the green VBA button to format yout code as shown.

[mechanize]

I deleted the under score. Something still isn't correct when trying to import the .txt file using a variable name.

Sub populate_list()
'
' populate_list Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("B5:C5").Select
ActiveCell.FormulaR1C1 = "Chicken Patties"
Range("D14").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Users\Kal\Desktop\Menu Planner\" & Range("B5:C5").Value, Destination:=Range("$D$14"))
.Name = "Chicken Patties"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Do you have any other suggestions? Could it be that I am using a ".Value" at the end of the cell callout on the 10th line when it is a string? Should this have a different ending?

[Kenneth Hobs]

Set a string and look at it in debug to be sure that you build the proper string. You did not concatenate a ".txt". When referring to joined cells, the first cell has the value.

Sub populate_list()
'
' populate_list Macro
'
' Keyboard Shortcut: Ctrl+a
'

Dim pFolder As String, ws As Worksheet
pFolder = "C:\Users\Kal\Desktop\Menu Planner\"
'pFolder = ThisWorkbook.Path & "\"
Set ws = ActiveSheet
'ws.Range("B5").Value = "Chicken Patties"

Worksheets("Shopping List").Activate
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & pFolder & ws.Range("B5").Value & ".txt", _
Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1))
.Name = ws.Range("B5").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

ws.Activate
End Sub

[mechanize]

Thanks a lot Kenneth! You solved all of my problems. I will use variants of your code for the rest of my project. I appreciate your help!