ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Importing Text File (https://www.excelbanter.com/excel-programming/296221-importing-text-file.html)

dan
Importing Text File
 
I need to import a text file (weekly), to my workbook and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan

Bob Phillips[_6_]
Importing Text File
 
Does it look the same on your machine as it does here in the NG?
Specifically, these lines

.TextFileTextQualifier =
xlTextQualifierDoubleQuote

.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, 1, 1)

should each be one single line.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
I need to import a text file (weekly), to my workbook and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan

Cecilkumara Fernando[_2_]
Importing Text File
 
Dan,
What is the error massage?
I got one when the file name is incorrect
Cecil

"Dan" wrote in message
...
I need to import a text file (weekly), to my workbook and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan

No Name
Importing Text File
 
All are single lines except the .TextFileColumnDataTypes
= Array and it has the _ at the end of the line where
it's split.

-----Original Message-----
Does it look the same on your machine as it does here in
the NG?
Specifically, these lines

.TextFileTextQualifier =
xlTextQualifierDoubleQuote

.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, 1, 1)

should each be one single line.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in
message
...
I need to import a text file (weekly), to my workbook
and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text
Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan


.

No Name
Importing Text File
 
Cecil,

I get run time error 1004, Cannot find the text file to
refresh this external data range.

I get this error even though the file exsists and I
select it with the myFile= . The debug routine highlights
the last line of code.

Thanks,
Dan
-----Original Message-----
Dan,
What is the error massage?
I got one when the file name is incorrect
Cecil

"Dan" wrote in
message
...
I need to import a text file (weekly), to my workbook
and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text
Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan


.

No Name
Importing Text File
 
Your Solution solved the problem, Thank you very much!
Thanks to all.
Dan
-----Original Message-----
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range("A1"))
.Name = MyFile

Looks for F:\MyFile.Txt

you need to change to

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & MyFile, Destination:=Range("A1"))
.Name = "MyFile" & Activesheet.QueryTables.count
+ 1

--
Regards,
Tom Ogilvy



wrote in message
...
Cecil,

I get run time error 1004, Cannot find the text file to
refresh this external data range.

I get this error even though the file exsists and I
select it with the myFile= . The debug routine
highlights
the last line of code.

Thanks,
Dan
-----Original Message-----
Dan,
What is the error massage?
I got one when the file name is incorrect
Cecil

"Dan" wrote in
message
...
I need to import a text file (weekly), to my
workbook
and
I'm having a problem getting the macro to function
properly. The macro stops with the last line, Any
help
would be greatly appreciated.

MyFile = Application.GetOpenFilename("Text
Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\MyFile.txt", Destination:=Range
("A1"))
.Name = MyFile
.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, 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
End Sub


Thanks,
Dan


.



.


All times are GMT +1. The time now is 11:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com