H
halarikeyur
I am trying to open a file which has a path name based on use
requirement.
I am including that path name to a cell in my worksheet. I want m
macro to take that cell content everytime I run the macro and ope
another file based on that path.
First I import file 1.
i.e. file 1 => will have some thing like this c:\keyur\keyur.txt
which I am putting in cell A1
now I want macro to read the content of A1 and use as filepath name an
import keyur.txt in worksheet.
I tried following but did not work.
So everytime pathname changes in file 2 it will import different file.
Range("B6").Select
FileNm = Sheets("Sheet1").Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:="TEXT;FileNm", _
Destination:=Range("B6"))
.Name = "rpt_chg_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 = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-3
End Su
requirement.
I am including that path name to a cell in my worksheet. I want m
macro to take that cell content everytime I run the macro and ope
another file based on that path.
First I import file 1.
i.e. file 1 => will have some thing like this c:\keyur\keyur.txt
which I am putting in cell A1
now I want macro to read the content of A1 and use as filepath name an
import keyur.txt in worksheet.
I tried following but did not work.
So everytime pathname changes in file 2 it will import different file.
Range("B6").Select
FileNm = Sheets("Sheet1").Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:="TEXT;FileNm", _
Destination:=Range("B6"))
.Name = "rpt_chg_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 = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-3
End Su