James Powell
asked on
Macro variable based on cell contents
I'm importing text files into Excel.
The file name I'm importing is listed in column A.
I need that file name to change, based on which cell I'm on, when running this macro.
The path "C:\Users\JPowell..... (always remains the same)
I recorded this simple sample below.
The next cell would contain something like "Blueberry Crumb".
I need the macro to pull that file name from the corresponding cell", based on where I run the macro. (i.e. my current row position)
Sub Macro3()
'
' Macro3 Macro
'
'
With ActiveSheet.QueryTables.Ad d(Connecti on:= _
"TEXT;C:\Users\JPowell\Des ktop\TXT\A PPLE CRUMB HORN.txt", Destination:=Range( _
"$B$30"))
.Name = "APPLE CRUMB HORN"
.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
.TextFileConsecutiveDelimi ter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimite r = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumb ers = True
.Refresh BackgroundQuery:=False
End With
End Sub
The file name I'm importing is listed in column A.
I need that file name to change, based on which cell I'm on, when running this macro.
The path "C:\Users\JPowell..... (always remains the same)
I recorded this simple sample below.
The next cell would contain something like "Blueberry Crumb".
I need the macro to pull that file name from the corresponding cell", based on where I run the macro. (i.e. my current row position)
Sub Macro3()
'
' Macro3 Macro
'
'
With ActiveSheet.QueryTables.Ad
"TEXT;C:\Users\JPowell\Des
"$B$30"))
.Name = "APPLE CRUMB HORN"
.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
.TextFileConsecutiveDelimi
.TextFileTabDelimiter = True
.TextFileSemicolonDelimite
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumb
.Refresh BackgroundQuery:=False
End With
End Sub
ASKER
I had something similar to that, but I get a debug error on the last line, because I'm setting the inserted text to not really be a "query". Just straight text. (under the advanced options of the final cell insert)
.Refresh BackgroundQuery:=False
.Refresh BackgroundQuery:=False
Can you upload a sample because i have not worked on tables.
ASKER
Here is a sample of the file.
you just need to create a folder on your own desktop TXT that contains any text.
IngredientsDBaseSample.xlsm
you just need to create a folder on your own desktop TXT that contains any text.
IngredientsDBaseSample.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"TEXT;C:\Users\JPowell\Des
"$B$30"))
.Name = activecell.value