Excel VBA split panes not working consistently 1

[PeDa]

I am generating an Excel spreadsheet in Microsoft Access. I am trying to set split panes with the following code:

ActiveWindow.SplitColumn = 2
ActiveWindow.SplitRow = 0
ActiveWindow.FreezePanes = True

This works fine the first time I use it, but thereafter it yields a "Object variable or With block variable not set" error message. I have to shut down and restart Access before it works again.

 

[SkipVought]

Hi,

Thereafter doing what?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

Try to use full object's path, for instance (by object type):
[tt]ExcelApplication.Workbook.Window[/tt]
Have you tested existence of ActiveWindow?

combo

 

[PeDa]

Combo's suggestion dit the trick:

Dim ObjXL As Object
Dim ObjWkb As Object
Dim ObjSht As Object
Set ObjXL = CreateObject("Excel.Application")
ObjXL.Application.Workbooks.Add
Set ObjWkb = ObjXL.Application.ActiveWorkbook
ObjXL.Visible = True
Set ObjSht = ObjWkb.Worksheets(1)
ObjSht.Activate
ObjSht.Cells(1, 1) = "Bla bla bla"
...do more stuff
ObjXL.Application.ActiveWindow.SplitColumn = 2
ObjXL.Application.ActiveWindow.SplitRow = 0
ObjXL.Application.ActiveWindow.FreezePanes = True
Set ObjXL = Nothing
Set ObjWkb = Nothing
Set ObjSht = Nothing

I don't really understand why my original code did wordt the first time (only)

PeterDa

 

[combo]

I would slightly shorten the code:

Dim ObjXL As Object
Dim ObjWkb As Object
Dim ObjSht As Object
Set ObjXL = CreateObject("Excel.Application")
Set ObjWkb = ObjXL.Application.Workbooks.Add [COLOR=#4E9A06]'Workbooks.Add returns added workbook[/color][COLOR=#4E9A06][COLOR=#4E9A06][/color][/color]
ObjXL.Visible = True
Set ObjSht = ObjWkb.Worksheets(1)
ObjSht.Activate
ObjSht.Cells(1, 1) = "Bla bla bla"
...do more stuff
ObjXL.ActiveWindow.SplitColumn = 2 [COLOR=#4E9A06]'ObjXl is excel application[/color]
ObjXL.ActiveWindow.SplitRow = 0 [COLOR=#4E9A06]'ObjXl is excel application[/color]
ObjXL.ActiveWindow.FreezePanes = True [COLOR=#4E9A06]'ObjXl is excel application[/color]
Set ObjXL = Nothing
Set ObjWkb = Nothing
Set ObjSht = Nothing

combo

 

[Andrzejek]

Looks to me you can accomplish this:

ObjXL.Application.ActiveWindow.SplitColumn = 2
ObjXL.Application.ActiveWindow.SplitRow = 0
ObjXL.Application.ActiveWindow.FreezePanes = True

Just in 2 lines like this:

ObjXL.Application.Range("C1").Select
ObjXL.Application.ActiveWindow.FreezePanes = True

Have fun.

---- Andy

There is a great need for a sarcasm font.