H
HaSt2307
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
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