Dynamic path for importing data

[SurVie]

Hello,

I've made a macro for importing data into my excel sheet. I'm now trying to make the path dynamic based on cell values from my excel sheet. However I can't seem to get the code right for this. Can anyone help me with this?

Below i've posted the code, the red part i need to make dynamic based on cell value's in my sheet. C:\prog\data\ is always the same, but the part "4220" and "2010" and "filename.asc" are always different, and i would like to base these 3 on 3 different cell values. Any help would be appreciated!

Sub Import()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\prog\data\4220\2010\filename.asc", Destination _
:=Range("$A$1"))
.Name = "422000-filename_4"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 3
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ";"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[njimack]

Welcome to the Board. Something like this:
"TEXT;C:\prog\data\" & Range("A1") & "\&Range("A2") & "\" & Range("A3"),

 

[SurVie]

I've tried doing this myself and came up with this:

Sub Macro2()

path1 = "4220"
Year1 = "2010"
Path2 = "422000-filname.Asc"

Filename = "C:\prog\data\" & path1 & "\" & Year1 & "\" & Path2

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;filename", Destination _
:=Range("$A$1"))



But this doesn't seem to be working for me.

 

[SurVie]

I'll give your way a try! Thanks

 

[SurVie]

Thanks, it works like a charm now!

I changed your code a little bit because i still got syntax errors but now it works like this:

& Range("A1") & "\" & Range("A2") & "\" & Range("A3")

Thanks for your help!!

 

[njimack]

Cool, glad it worked.