Hey Group,
I have conducted several searches looking for similar problems but
was unable to find anything. I (Excel 2003) am helping a buddy (who is
on Excel 2000) import a text file he receives each quarter. I have it
working on Excel 2003, but it errors out in Excel 2000.

It produces a run-time error 1004 Method Open Text of Object
Workbooks Failed. Below is the code I am using, can anyone spot
something in the Workbooks.OpenText area that would cause an error in
Excel 2000 but not 2003?

Thanks
Harry


Sub ImportText()
Dim ImportWbk As Workbook
Dim newWbk As Workbook
'Using workbooks.opentext will import the file to a new workbook, so we
'process the imported data then copy it to desired workbook and sheet.

If InputBox("Please enter the password", "Password Needed") < "*******"
Then
MsgBox ("Wrong Password!")
On Error GoTo 0
Exit Sub

Else

Set ImportWbk = ThisWorkbook
Application.ScreenUpdating = False
Sheets("Data").Select

Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Please wait while importing and cleaning
up data..."

' Adjusted Array(53,1) to (54,1)
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:=myFile, _
Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(5, 1), Array( _
12, 1), Array(44, 1), Array(47, 1), Array(54, 1), Array(64, 1),
Array(73, 1), Array(82, 1), _
Array(92, 1), Array(102, 1), Array(115, 1), Array(120, 1),
Array(130, 1)) ', _
TrailingMinusNumbers:=True

Rows("1:4").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set newWbk = ActiveWorkbook

Cells.Select
Selection.Columns.AutoFit

'Insert Code to find and delete by product

Call DelByProd

'Insert Code to find and delete next area

Cells.Find(What:="CODE", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False).Activate ', SearchFormat:=False

Cells(ActiveCell.Row, 1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
'Code to select area for copy
Range("N5000").Select
Range(Selection, Cells(1)).Select
Selection.Copy

'Change for active worksheet
ActiveSheet.Paste Destination:=ImportWbk.Sheets("Data").Range("A1")
Application.CutCopyMode = False

'Set New workbook active to close it
'Set newWbk = ActiveWorkbook

newWbk.Close SaveChanges:=False

'Go to working worksheet and force it to recalculate
Sheets("Pricing Worksheet").Select
Worksheets("Pricing Worksheet").Calculate
Range("B1").Select

Application.ScreenUpdating = True
Application.StatusBar = "Done!"
Application.Wait Now + TimeValue("00:00:01")
Application.StatusBar = False

End If
End Sub

Hi HaSt2307

TrailingMinusNumbers does not exist in Excel 2000

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"HaSt2307" wrote in message ...
Hey Group,
I have conducted several searches looking for similar problems but
was unable to find anything. I (Excel 2003) am helping a buddy (who is
on Excel 2000) import a text file he receives each quarter. I have it
working on Excel 2003, but it errors out in Excel 2000.

It produces a run-time error 1004 Method Open Text of Object
Workbooks Failed. Below is the code I am using, can anyone spot
something in the Workbooks.OpenText area that would cause an error in
Excel 2000 but not 2003?

Thanks
Harry


Sub ImportText()
Dim ImportWbk As Workbook
Dim newWbk As Workbook
'Using workbooks.opentext will import the file to a new workbook, so we
'process the imported data then copy it to desired workbook and sheet.

If InputBox("Please enter the password", "Password Needed") < "*******"
Then
MsgBox ("Wrong Password!")
On Error GoTo 0
Exit Sub

Else

Set ImportWbk = ThisWorkbook
Application.ScreenUpdating = False
Sheets("Data").Select

Application.DisplayStatusBar = True
' makes sure that the statusbar is visible
Application.StatusBar = "Please wait while importing and cleaning
up data..."

' Adjusted Array(53,1) to (54,1)
myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:=myFile, _
Origin:=437, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(5, 1), Array( _
12, 1), Array(44, 1), Array(47, 1), Array(54, 1), Array(64, 1),
Array(73, 1), Array(82, 1), _
Array(92, 1), Array(102, 1), Array(115, 1), Array(120, 1),
Array(130, 1)) ', _
TrailingMinusNumbers:=True

Rows("1:4").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Set newWbk = ActiveWorkbook

Cells.Select
Selection.Columns.AutoFit

'Insert Code to find and delete by product

Call DelByProd

'Insert Code to find and delete next area

Cells.Find(What:="CODE", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:= _
xlNext, MatchCase:=False).Activate ', SearchFormat:=False

Cells(ActiveCell.Row, 1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
'Code to select area for copy
Range("N5000").Select
Range(Selection, Cells(1)).Select
Selection.Copy

'Change for active worksheet
ActiveSheet.Paste Destination:=ImportWbk.Sheets("Data").Range("A1")
Application.CutCopyMode = False

'Set New workbook active to close it
'Set newWbk = ActiveWorkbook

newWbk.Close SaveChanges:=False

'Go to working worksheet and force it to recalculate
Sheets("Pricing Worksheet").Select
Worksheets("Pricing Worksheet").Calculate
Range("B1").Select

Application.ScreenUpdating = True
Application.StatusBar = "Done!"
Application.Wait Now + TimeValue("00:00:01")
Application.StatusBar = False

End If
End Sub

Ron,
I know it is not clear, but I have it commented out. Is there
something else in the Workbooks.Open that is also not part of 2000?

Thanks
Harry

Ron de Bruin wrote:
Hi HaSt2307

TrailingMinusNumbers does not exist in Excel 2000

aha, I see now

If I remember correct change the number to xlWindows

Origin:=xlWindows



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"HaSt2307" wrote in message ...
Ron,
I know it is not clear, but I have it commented out. Is there
something else in the Workbooks.Open that is also not part of 2000?

Thanks
Harry

Ron de Bruin wrote:
Hi HaSt2307

TrailingMinusNumbers does not exist in Excel 2000