Workbooks(WorkbookName).Activate Failure

[JRJ]

I have a Word97 document linked to graphs on several Worksheets in an
Excel97 workbook, both in a binder (to keep the files together and
preserve the links). In an environment in which each client has an
individual Access97 data file, appropriate data are structured in
queries, copied and pasted (paste-special/text) onto worksheets
containing graphs. (I plan to keep a binder for each client.) All
graphs have a common X-axis (dates). On a special worksheet containing
constants and parameters, eg, start and end dates for the X-axis, there
is a button that runs a macro to update the X-axis on all the graphs.
The development took place largely on a Windows 2000 Pro system and was
completed on a Windows XP Pro system (both with Office 97 SR2b). Code,
consistent with several explorations done by recording macros, similar
to the following worked on both systems. The following code, condensed
by use of the For Each logic was developed on the WinXP system. This
final version fails on the Win2000 system:

Sheetlist = "SessParams,MySheet2,...,"
Do While (SheetsList <> "")
CurrSheetName = PopFromList(SheetsList, ",")
Set CurrSheet = Sheets(CurrSheetName)
CurrSheet.Select
For Each ChartObj In ActiveSheet.ChartObjects
ChartObj.Activate
With ActiveChart.Axes(xlCategory)
.Select
.MinimumScale = Date1
.MaximumScale = Date2
.BaseUnitIsAuto = True
.MajorUnit = DateIntrvl
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
End With
CurrSheet.Range("A1").Select
ActiveWindow.Visible = False
Windows(Workbooks(1).Name).Activate
Sheets(OrigSheetName).Select
Sheets(OrigSheetName).Range("A10").Select
Next ChartObj
Loop

The lines:
ActiveWindow.Visible = False
Windows(Workbooks(1).Name).Activate
were present in every macro as a kind of closure for the on a graph --
but the Workbooks(1).Name was represented by a literal.

Now on the Win200 system, I get the error, on the latter of these two
lines,
Runtime error '1004'
Activate method of Window class failed

Any debugging I do after this error seems to be erroneous, as if there
is something wrong that distorts subsequent debugging -- like repeats
of previous statements failing. I do not know enough about the states
of various objects to sort things out.

Any help would be eally appreciated.

jim

 

[JRJ]

I am brand new to Google groups, and do not know how to add to a
posting.

With WindowsXP, running a macro causes a popup requesting permission to
run the Excel workbook in a separate window. This does not happen with
Windows 2000.
I am sure that is significant, but don't know what to do on the Win2000
system.
(Also, where I have used the word "graphs" I should have used
"charts".)

Jim

 

[Tushar Mehta]

Thanks for the detailed explanation.

Since I don't have a way to duplicate your error (don't have Win2000 Pro),
this is somewhat speculative but it should work. In general, since the
macro recorder duplicates one's actions, it generates lots of 'activate' and
'select' statements. However, they are rarely needed.

The foll. two subroutines compile OK (with the introduction of a dummy
PopFromList function) but have not been tested further. Note that in your
code you have SheetsList and SheetList, which I suspect should have been the
same.

Sub showNoSelects()
Dim SheetList As String, CurrSheetName As String, _
CurrSheet As Object, _
ChartObj As ChartObject, _
Date1 As Date, Date2 As Date, DateIntrvl As Long
SheetList = "SessParams,MySheet2,...,"
Do While (SheetList <> "")
CurrSheetName = PopFromList(SheetList, ",")
Set CurrSheet = Sheets(CurrSheetName)
For Each ChartObj In CurrSheet.ChartObjects
With ChartObj.Chart.Axes(xlCategory)
.MinimumScale = Date1
.MaximumScale = Date2
.BaseUnitIsAuto = True
.MajorUnit = DateIntrvl
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
End With
Next ChartObj
Loop
End Sub

The following is not strictly necessary but is a simplifies the above by
removing variables that are used only once.

Sub showNoSelects2()
Dim SheetList As String, _
ChartObj As ChartObject, _
Date1 As Date, Date2 As Date, DateIntrvl As Long
SheetList = "SessParams,MySheet2,...,"
Do While (SheetList <> "")
For Each ChartObj _
In Sheets(PopFromList(SheetList, ",")).ChartObjects
With ChartObj.Chart.Axes(xlCategory)
.MinimumScale = Date1
.MaximumScale = Date2
.BaseUnitIsAuto = True
.MajorUnit = DateIntrvl
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
End With
Next ChartObj
Loop
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions