Excel 2000 : application-defined or object-defined error

[scottsanpedro]

Hi,
I have a client who has just had an upgrade to server 2008! Bit quicker than I would do it.
Anyway, an excel spreadsheet has some vba included. When its run it get the above error. It stops on the forth line of code each time (Selection.QueryTable.Delete)

Here's the full code...any ideas would be most welcome.

Scott

----------------------------

Sub ImportData()
'
' ImportData Macro
' Macro recorded
'
'
Workbooks.Open Filename:="N:\Pdoxdata\Fix2000\fixturesprogcheck.xls"
Cells.Select
Selection.ClearContents
Selection.QueryTable.Delete
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;H:\Pdoxpriv\FixExport.txt", _
Destination:=Range("A1"))
.Name = "daily_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.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)
.TextFileFixedColumnWidths = Array(22, 8, 11, 8, 10, 11, 10, 6, 9, 10, 6, 8)
.Refresh BackgroundQuery:=False
End With
Rows("4:4").Select
Selection.ClearContents
Range("E4").Select
ActiveCell.FormulaR1C1 = "Ford Aviation - Fixtures Programme Check"
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
With Selection.QueryTable
.Name = "daily_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
End With
Range("A6").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B6").Select
ActiveCell.FormulaR1C1 = "Flight No."
Range("C6").Select
ActiveCell.FormulaR1C1 = "From"
Range("D6").Select
ActiveCell.FormulaR1C1 = "To"
Range("F6").Select
ActiveCell.FormulaR1C1 = "STD"
Range("G6").Select
ActiveCell.FormulaR1C1 = "STA"
Range("H6").Select
ActiveCell.FormulaR1C1 = "Seats"
Rows("7:8").Select
Selection.Delete Shift:=xlUp
Range("E4:F4").Select
Selection.Cut Destination:=Range("F4:G4")
Range("A7").Select
Columns("B:B").EntireColumn.AutoFit
Range("E4").Select
Selection.Cut Destination:=Range("F4")
Range("E1").Select
Selection.EntireColumn.Insert
Columns("H:H").Select
Selection.Cut
Columns("J:J").Select
Selection.Cut
Columns("I:N").Select
Selection.ClearContents
Range("H7:H200").Select
Selection.NumberFormat = "General"
Range("I6").Select
Columns("I:I").ColumnWidth = 6.43
Columns("J:J").ColumnWidth = 6.43
Columns("K:K").ColumnWidth = 6.43
Columns("L:L").ColumnWidth = 6.43
ActiveCell.FormulaR1C1 = "ATD"
Range("J6").Select
ActiveCell.FormulaR1C1 = "ATA"
Range("K6").Select
ActiveCell.FormulaR1C1 = "TOB"
Range("L6").Select
ActiveCell.FormulaR1C1 = "Delay"
Range("M6").Select
ActiveCell.FormulaR1C1 = "Code"
Range("N6").Select
ActiveCell.FormulaR1C1 = "Comments"
Columns("N:N").Select
Columns("N:N").EntireColumn.AutoFit
Range("L7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]-RC[-6])"
Range("L7").Select
ActiveWindow.LargeScroll Down:=5
ActiveWindow.SmallScroll Down:=12
Range("L7:L200").Select
Selection.NumberFormat = "[$-F400]hh:mm"
Columns("L:L").EntireColumn.AutoFit
Columns("L:L").ColumnWidth = 8.29
Selection.FillDown
Columns("L:L").ColumnWidth = 9.14
Rows("4:4").Select
Selection.ClearContents
Range("E4").Select
ActiveCell.FormulaR1C1 = "Ford Aviation - Fixtures Programme Check"
Range("A1").Select
Columns("E:E").ColumnWidth = 5.43
Range("L7").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",SUM(RC[-3]-RC[-6]))"
Range("L7").Select
ActiveWindow.LargeScroll Down:=6
Range("L7:L200").Select
Selection.FillDown
ActiveWindow.LargeScroll Down:=-6
Range("I7").Select
ActiveWindow.LargeScroll Down:=0
Range("I7:J7").Select
ActiveWindow.LargeScroll Down:=5
ActiveWindow.SmallScroll Down:=7
Range("I7:J200").Select
Selection.NumberFormat = "[$-F400]hh:mm"
Range("A1").Select
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Range("E6").Select
ActiveCell.FormulaR1C1 = "STD"
Range("F6").Select
ActiveCell.FormulaR1C1 = "STA"
Range("G6").Select
ActiveCell.FormulaR1C1 = "Seats"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Ford Aviation - Fixtures Export"
Range("A1").Select
ActiveWorkbook.Save
Windows("Import02.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[PHV]

Replace this:
Selection.QueryTable.Delete
with something like this:
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottsanpedro]

many thanks for your quick resonse
will give this a go and let you know
Scott

 

[scottsanpedro]

Yep..took me a bit of time to get back, but that worked perfectly.
Many thanks
Scott

 

[xlbo]

Nothing to do with SQL Server upgrade - most likely to do with file being saved with wrong sheet active. That's horrible code btw - macro gebnerated I'd say as it includes scrolling the window with the scroll bar. Could probably be chunked down to about 1/4 of the lines you currently have...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[scottsanpedro]

Thanks xlbo...
Luckily I'm re-writting this system in SQL Server from Paradox and the shelf life of these particular excel spreadsheets is days!
The company just needed it running in the interim.
Bye Bye horrible code!