×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel VBA split panes not working consistently

Excel VBA split panes not working consistently

Excel VBA split panes not working consistently

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

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.

RE: Excel VBA split panes not working consistently

Hi,

Thereafter doing what?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel VBA split panes not working consistently

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

combo

RE: Excel VBA split panes not working consistently

(OP)
Combo's suggestion dit the trick:

CODE

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

RE: Excel VBA split panes not working consistently

I would slightly shorten the code:

CODE -->

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


combo

RE: Excel VBA split panes not working consistently

Looks to me you can accomplish this:

CODE

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

Just in 2 lines like this:

CODE

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close