Macro: Excel 2000 does not work, XP does...

[Martin Eckart]

Hi NG,

I created a Macro which is running succesfully with Office XP/2003. Using
Office 2000 I get the error message: "Runtime Error '438' Object does not
support this property or method)"

I searched msdn for that and I found an article where issues with Excel 2000
in conjunction with the Selection Method are known. (as far as I unsderstood
that correctly there has been an issue regarding the return of a boolean
variable, but I am not too sure if I got that correctly)

I did not find a solution for my problem. I will poste my Code and I hope
someone could tell me what is wrong (I cut the Server data):

Public Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("Mannschaften").Activate
ActiveSheet.Range("A2:B200").Clear
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www......", Destination:=ActiveSheet.Range("A2"))
.Name = "rpcserver........"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Dim i As Integer
i = 1

While Sheets("Mannschaften").Cells(i, 1).Value <> ""
i = i + 1
Debug.Print i
Wend
Sheets("Mannschaften").Range("a1", "a" & i - 1).Select
ActiveWorkbook.Names.Add Name:="Mannschaften", RefersToR1C1:= _
"=Mannschaften!R1C1:R" & i - 1 & "C1"
Sheets("Liste").Activate
Application.ScreenUpdating = True
End Sub


Thanks a lot for your time and effort,

Martin

 

[Tom Ogilvy]

Excel 2002 and then Excel 2003 have added options to some of Excel's
commands. These are not know to Excel 2000 and earlier versions. Thus if
you record code in the latest version and try to use it in earlier versions,
you sometimes run into this error message. It isn't an issue - you just
need to recognize that these options are not supported and remove them from
your code. If they are critical to the operation of your macro, then you
will have to figure out how to apply the functionality using commands
supported in earlier versions.

You can run the command in Excel 2000 and when you get the error message,
the line highlighted is a command not recognized in xl2002. You can remove
that command or comment it out. Repeat until you have done this for all
unsupported commands.

 

[Leo Heuser]

Hi Martin

It looks like Excel 2000 doesn't support
..WebDisableRedirections = False
Give the line a single quote '
'.WebDisableRedirections = False
and see, if it solves the problem.

 

[Brad]

Looking at the properties of the queryTable object in
Excel 2000, I didn't see a
property "WebDisableRedirections". That must be a new
thing in 2003.

Try commenting that guy out.

HTH.
-Brad

 

[Martin Eckart]

That is exactly what was wrong!
Thanks to Brad and Leo,

Martin

 

[Leo Heuser]

You're welcome, Martin, and thanks
for the feedback.

LeoH