Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
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. -- Regards, Tom Ogilvy "Martin Eckart" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
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. -- Best Regards Leo Heuser Followup to newsgroup only please. "Martin Eckart" skrev i en meddelelse ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
That is exactly what was wrong!
Thanks to Brad and Leo, Martin "Brad" schrieb im Newsbeitrag ... 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 -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: Excel 2000 does not work, XP does...
You're welcome, Martin, and thanks
for the feedback. LeoH "Martin Eckart" skrev i en meddelelse ... That is exactly what was wrong! Thanks to Brad and Leo, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work Week, Excel 2000 & 2003 | Excel Worksheet Functions | |||
Excel 2000, RAND and IF functions do not work together for me | Excel Worksheet Functions | |||
formula in Excel 2000 to work %'s | Excel Worksheet Functions | |||
will macro created in excell 2003 work if run using 2000 | Excel Discussion (Misc queries) | |||
Can't get SUM function to work in Excel 2000 | New Users to Excel |