OpenText bug ?
 

Hi luc P. Faucheux,

Try to put extra quotation marks on the file name like this:

Workbooks.OpenText filename:= _
"""" & the_file_name & """", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1))


HTH

---
Orlando Magalhães Filho

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)

"luc P. Faucheux" escreveu na mensagem
om...
Hi,
I have a macro that opens up text files (see code below). Most of the
times it works fine. However (especially for files that have been
around a few days), the macro stops with an error message indicating
that Excel cannot open the file "the_file_name.xls" and the macro
breaks on the Workbooks.OpenText line. However, my the_file_name does
not have ".xls" appended to it, and going into the debugger returns
for:
?the_file_name
the correct file name without the ".xls" at the end.
I am at a loss trying to make it work.
Thanks again,
Luc.



Code :
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False


the_date = Worksheets("Front").Range("d14").Value
the_date = Trim(the_date)
wkb_name = ThisWorkbook.Name
the_window_name = "summary." & the_date
the_file_name = "\\drvsm1\data_market\" & the_window_name

Workbooks.OpenText filename:= _
the_file_name, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1),
Array(13, 1), Array(14, 1), Array(15 _
, 1), Array(16, 1), Array(17, 1))
Range("A1:Q121").Select
Application.CutCopyMode = False
Selection.Copy
Windows(wkb_name).Activate
Worksheets("import").Select
Range("CA1").Select
ActiveSheet.Paste
' Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
' False, Transpose:=False
Range("a1").Select
Windows(the_window_name).Activate
ActiveWorkbook.Close
Windows(wkb_name).Activate