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.Add(Connection:= _
        "TEXT;C:\Users\JPowell\Desktop\TXT\APPLE 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
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
LVL 1
James PowellData Analyst / Online Retail DevelopmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Something like

  "TEXT;C:\Users\JPowell\Desktop\TXT\" & activecell.value & ".txt", Destination:=Range( _
        "$B$30"))
        .Name = activecell.value
0
James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
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
0
Saqib Husain, SyedEngineerCommented:
Can you upload a sample because i have not worked on tables.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
Here is the error...This is the error message
0
James PowellData Analyst / Online Retail DevelopmentAuthor Commented:
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
0
byundtMechanical EngineerCommented:
The following macro is working on my computer. Changes include addition of backslash before Users in statement defining the path, test using Dir function to see if file exists, test for .txt file extension and test for backslash at end of path.
Sub ImportIngredients()
Dim sFile As String, sPath As String
sPath = "C:\Users\JPowell\Desktop\TXT\"
sFile = Cells(ActiveCell.Row, 1).Value
If Right(UCase(sFile), 4) <> ".TXT" Then sFile = sFile & ".txt"
If Right(sPath, 1) <> Application.PathSeparator Then sPath = sPath & Application.PathSeparator
If Dir(sPath & sFile) = "" Then
    MsgBox "File not found"
    Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sPath & sFile, Destination:=Cells(ActiveCell.Row, 2))
    .Name = ActiveCell.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
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.