mechanize
12-23-2011, 08:18 PM
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
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