K
Karin
My user has 2 applications they programmed and distributed to several other
user's pc's sometime ago. One of the users received a new pc which is
running Excel 2003. The code was originally written in Excel 2002 & was
working with no problems. The user who is running Excel 2003 receives a vb
error message Compile Error: Named argument not found and it highlights the
TrailingMinusNumbers:=. Are there any differences between the 2002 and 2003
versions of excel and the Workbooks.OpenText command that would generate this
error?
Below is the code from one of the applications generating the error.
Sub NewInfo()
Application.ScreenUpdating = False
Dim Start As String 'Denotes the start of the line where info will
be erased
Dim Finish As Integer 'Denotes the end of the line where info will be
erased
Dim FormatEnd As Integer 'Gives a name to the last cell that needs
currency formatting
'This section of the macro generates an excel file from the text file
call "easbos1"
'and saves it as "New Backlog Report". It is then formatted for
readability.
Workbooks.OpenText Filename:= _
"\\DataLocation\easbos1.dat", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(10 _
, 1), Array(20, 1), Array(38, 1), Array(43, 3), Array(53, 1),
Array(63, 1), Array(70, 1), _
Array(76, 1), Array(106, 3), Array(116, 3), Array(126, 1),
Array(136, 1), Array(140, 1), _
Array(142, 1), Array(148, 1), Array(154, 1), Array(160, 1)),
TrailingMinusNumbers:= _
True
Columns("E:E").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").ColumnWidth = 8.29
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
'saves the new file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"\\DataLocation\New Backlog Report.xls", FileFormat:= _
xlNormal
Application.DisplayAlerts = True
'activates the correct window
Windows("Backlog Application.xls").Activate
Sheets("Old").Select 'this section deletes the old sheet
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("New").Select 'this section changes the "new"
ActiveSheet.Name = "Old" 'sheet to the "old" one
'the section below pulls the information from a known location and changes
'the name of the recently copied sheet to "New"
Workbooks.Open Filename:="\\DataLocation\New Backlog Report.xls"
Windows("New Backlog Report.xls").Activate
Worksheets("easbos1").Activate
Sheets("easbos1").Name = "New"
Windows("New Backlog Report.xls").Activate
Sheets("New").Select
Sheets("New").Copy before:=Workbooks("Backlog Application.xls"). _
Sheets(3)
Application.DisplayAlerts = False
Windows("New Backlog Report.xls").Close
Application.DisplayAlerts = True
'The section below places the columns: Full Qty Shipped, 1st-3rd Repromise
'Date, Times Exceeded 3rd Repromise Date, Total Days Late
'(Excluding Weekends) and Notes
Sheets("Old").Activate
Rows("1:1").Select
Selection.Copy
Sheets("New").Select
Rows("1:1").Select
Selection.Insert shift:=xlDown
Columns("H:H").Select
Selection.Insert shift:=xlToRight
Range("H1").Select
Selection.Delete shift:=xlToLeft
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("H2").Select
Selection.Style = "Currency"
Selection.Copy
Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F2").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Name = "FormatEnd"
Range("F2:FormatEnd").Select
Selection.Style = "Currency"
Range("FormatEnd").Name.Delete
Columns("H:H").EntireColumn.AutoFit
'The section below formats the "New" Sheet for readability
Columns("T:V").Select
Selection.NumberFormat = "m/d/yyyy;@"
Columns("X:X").Select
Selection.NumberFormat = "General"
Range("S1:Y1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Font.Bold = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:Y").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:Y").Select
Columns("A:Y").EntireColumn.AutoFit
Columns("S:X").Select
Selection.ColumnWidth = 10.57
Columns("S:S").ColumnWidth = 14.14
Columns("T:T").ColumnWidth = 15.57
Columns("U:U").ColumnWidth = 15.57
Columns("V:V").ColumnWidth = 14.29
Columns("X:X").ColumnWidth = 6.86
Columns("Z:Z").ColumnWidth = 20#
Range("Z1").Select
Selection.Font.Bold = True
Columns("G:G").ColumnWidth = 9.71
Columns("I:I").ColumnWidth = 9.57
Rows("1:1").RowHeight = 51
Columns("Y:Y").ColumnWidth = 10.71
Columns("Z:Z").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Call FindSM
Call RemoveExtra
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
'putting borders on page and formatting headers
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'The section below sorts the "New" Sheet according to: Schedule date,
'Sales order # and line item (all ascending)
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Key3:=Range("N2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
'The section below selects a line from the "Old" Sheet and loops through
'all lines to find and erase all lines that have been shipped
Sheets("Old").Activate
Range("A2").Select
ActiveCell.Offset(0, 18).Select
Do Until ActiveCell.Value = "Full Qty Shipped" Or ActiveCell.Offset(0,
-18).Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, -18).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
'format sheet
Sheets("New").Activate
Range("A2").Select
Do Until ActiveCell.Offset(1, 0).Borders.LineStyle = xlNone
ActiveCell.Offset(1, 0).Select
ActiveCell.Name = "Start"
ActiveCell.Offset(0, 25).Name = "Finish"
Range("Start:Finish").Select
'color lines for ease of reading
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
'delete the name
Range("Start").Select
ActiveCell.Offset(1, 0).Select
Range("Start").Name.Delete
Range("Finish").Name.Delete
Loop
End Sub
user's pc's sometime ago. One of the users received a new pc which is
running Excel 2003. The code was originally written in Excel 2002 & was
working with no problems. The user who is running Excel 2003 receives a vb
error message Compile Error: Named argument not found and it highlights the
TrailingMinusNumbers:=. Are there any differences between the 2002 and 2003
versions of excel and the Workbooks.OpenText command that would generate this
error?
Below is the code from one of the applications generating the error.
Sub NewInfo()
Application.ScreenUpdating = False
Dim Start As String 'Denotes the start of the line where info will
be erased
Dim Finish As Integer 'Denotes the end of the line where info will be
erased
Dim FormatEnd As Integer 'Gives a name to the last cell that needs
currency formatting
'This section of the macro generates an excel file from the text file
call "easbos1"
'and saves it as "New Backlog Report". It is then formatted for
readability.
Workbooks.OpenText Filename:= _
"\\DataLocation\easbos1.dat", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(10 _
, 1), Array(20, 1), Array(38, 1), Array(43, 3), Array(53, 1),
Array(63, 1), Array(70, 1), _
Array(76, 1), Array(106, 3), Array(116, 3), Array(126, 1),
Array(136, 1), Array(140, 1), _
Array(142, 1), Array(148, 1), Array(154, 1), Array(160, 1)),
TrailingMinusNumbers:= _
True
Columns("E:E").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").ColumnWidth = 8.29
Columns("J:J").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
'saves the new file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"\\DataLocation\New Backlog Report.xls", FileFormat:= _
xlNormal
Application.DisplayAlerts = True
'activates the correct window
Windows("Backlog Application.xls").Activate
Sheets("Old").Select 'this section deletes the old sheet
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Sheets("New").Select 'this section changes the "new"
ActiveSheet.Name = "Old" 'sheet to the "old" one
'the section below pulls the information from a known location and changes
'the name of the recently copied sheet to "New"
Workbooks.Open Filename:="\\DataLocation\New Backlog Report.xls"
Windows("New Backlog Report.xls").Activate
Worksheets("easbos1").Activate
Sheets("easbos1").Name = "New"
Windows("New Backlog Report.xls").Activate
Sheets("New").Select
Sheets("New").Copy before:=Workbooks("Backlog Application.xls"). _
Sheets(3)
Application.DisplayAlerts = False
Windows("New Backlog Report.xls").Close
Application.DisplayAlerts = True
'The section below places the columns: Full Qty Shipped, 1st-3rd Repromise
'Date, Times Exceeded 3rd Repromise Date, Total Days Late
'(Excluding Weekends) and Notes
Sheets("Old").Activate
Rows("1:1").Select
Selection.Copy
Sheets("New").Select
Rows("1:1").Select
Selection.Insert shift:=xlDown
Columns("H:H").Select
Selection.Insert shift:=xlToRight
Range("H1").Select
Selection.Delete shift:=xlToLeft
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("H2").Select
Selection.Style = "Currency"
Selection.Copy
Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F2").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Name = "FormatEnd"
Range("F2:FormatEnd").Select
Selection.Style = "Currency"
Range("FormatEnd").Name.Delete
Columns("H:H").EntireColumn.AutoFit
'The section below formats the "New" Sheet for readability
Columns("T:V").Select
Selection.NumberFormat = "m/d/yyyy;@"
Columns("X:X").Select
Selection.NumberFormat = "General"
Range("S1:Y1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Font.Bold = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:Y").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:Y").Select
Columns("A:Y").EntireColumn.AutoFit
Columns("S:X").Select
Selection.ColumnWidth = 10.57
Columns("S:S").ColumnWidth = 14.14
Columns("T:T").ColumnWidth = 15.57
Columns("U:U").ColumnWidth = 15.57
Columns("V:V").ColumnWidth = 14.29
Columns("X:X").ColumnWidth = 6.86
Columns("Z:Z").ColumnWidth = 20#
Range("Z1").Select
Selection.Font.Bold = True
Columns("G:G").ColumnWidth = 9.71
Columns("I:I").ColumnWidth = 9.57
Rows("1:1").RowHeight = 51
Columns("Y:Y").ColumnWidth = 10.71
Columns("Z:Z").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Call FindSM
Call RemoveExtra
Range("A2:Z2").Select
Range(Selection, Selection.End(xlDown)).Select
'putting borders on page and formatting headers
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'The section below sorts the "New" Sheet according to: Schedule date,
'Sales order # and line item (all ascending)
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Key3:=Range("N2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
'The section below selects a line from the "Old" Sheet and loops through
'all lines to find and erase all lines that have been shipped
Sheets("Old").Activate
Range("A2").Select
ActiveCell.Offset(0, 18).Select
Do Until ActiveCell.Value = "Full Qty Shipped" Or ActiveCell.Offset(0,
-18).Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, -18).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
'format sheet
Sheets("New").Activate
Range("A2").Select
Do Until ActiveCell.Offset(1, 0).Borders.LineStyle = xlNone
ActiveCell.Offset(1, 0).Select
ActiveCell.Name = "Start"
ActiveCell.Offset(0, 25).Name = "Finish"
Range("Start:Finish").Select
'color lines for ease of reading
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
'delete the name
Range("Start").Select
ActiveCell.Offset(1, 0).Select
Range("Start").Name.Delete
Range("Finish").Name.Delete
Loop
End Sub