Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Importing Text File
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
dan dan is offline
external usenet poster
  
Posts: 113
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 93
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default 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


.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default 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


.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default 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


.



.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing CSV file (saved as Text) into XL as Text -- over 60 colu sbp Excel Discussion (Misc queries) 1 October 14th 06 11:50 PM
Importing text file schildb Excel Discussion (Misc queries) 8 July 25th 06 10:57 PM
Importing text file, only option to edit existing file smokey99 Excel Discussion (Misc queries) 8 April 26th 06 09:08 PM
Importing text file Jeff Excel Discussion (Misc queries) 1 April 4th 06 01:45 PM
importing text file, removing data and outputting new text file Pal Excel Programming 8 February 27th 04 08:32 PM


All times are GMT +1. The time now is 09:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement