Freeze Panes in Excel

[Kenln]

Does anyone know how to freeze panes in Excel from Access.

I can bold, autofit both rows and columns, I can even select the cell I want the FreezePanes to work from.

But every combination I can think of to set the property to true does not work.

I have

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
XlSheet.ActiveWindow.FreezePanes

Which does not work at all and I tried

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
ActiveWindow.FreezePanes

Which acts like it works ONCE but when you close Excel it is actually still running (Task Manager) so you cannot open (see it visible) it a second time.

 

[boblarson]

A couple of things for you -

What I will usually do to find out the code I need for Excel, is to do Tools > Macros > Record New Macro and then I'll do what I'm wanting it to do and then I go look at the code that was created. Then, I'll modify it (and usually shorten to remove the extraneous stuff) for use in Access.

I know that there have been several postings here on Excel not quitting when you run your code. One thing I did note in your other post is that you never specifically say objExcel.Quit before you try to set it to nothing. The other thing is that if any process holds it open it won't be able to be terminated. So, you will probably want to close the recordset and then set it to nothing prior to trying to quit and set the Excel object to nothing.

Oh, and also you don't need to specify a worksheet object as you can work with the sheets in the workbook object.

 

[boblarson]

I just tried the macro and it would appear that you would want to select a cell and then use:

    ActiveWindow.FreezePanes = True

At least you should try that to start.

 

[Kenln]

I can't use the Obj.Quit because the Excel is for the User to view, print whatever then quit when they are done.

As for rs.close, odd thing??? When I return to Access there is no longer any recordset in my form. nothing but #Name in all fields. Hmm?

I can get the FreezePane function to work in a macro within Excel, with problem is performing a FreezePane from Access. I may endup just calling an Excel macro from Access but of course that would not be my first choice.

 

[boblarson]

Here's a sample that works:

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook

Set objExcel = New Excel.Application
objExcel.Visible = True
Set xlWB = objExcel.Workbooks.Add
xlWB.Worksheets("Sheet1").Select
ActiveSheet.Range("B4").Select
ActiveWindow.FreezePanes = True

but you'll still have the Excel process running in the task manager because it would appear that Access has some hold on it and until you close the database it stays there. If you close the database, it terminates the process.

I know that there are some other posts here regarding that and there might be some solutions in there, but I don't remember.

 

[Kenln]

It has something to do with the ActiveSheet and definately the ActiveWindow method.

If I use a macro within Excel called from Access to freeze the panes no problem. If I use the ActiveWindow within Access VB it works once, but calling it again leaves an instance running that is not visible. So it's unusable.

I tried several different prefixes and I finally got it working (I think) using

Dim Xl As Excel.Application
Set XlBook = GetObject(MyPath + MyName)

Xl.ActiveSheet.Range("B2").Select
Xl.ActiveWindow.FreezePanes = True

I'll keep trying so see if this is consistant. Wow! I would have thought this one would be easier.

 

[boblarson]

Yes, I just tested it. Using the explicit Xl.ActiveSheet instead of ActiveSheet by itself let it terminate the Excel process when Excel was closed, instead of it waiting until Access closed.

Good catch!

 

[Kenln]

I have it working if:
No Excel is open, but if excel is already open with another workbook. It doesn't seem to leave an instance running but it does not freeze the panes either.

Do you know how to select a specific workbook as active first?

 

[boblarson]

This would work to open an existing workbook. Use my other code above if you want a brand new workbook.

Dim objExcel As Excel.Application
Dim xlWB As Excel.Workbook

Set objExcel = New Excel.Application
objExcel.Visible = True
Set xlWB = objExcel.Workbooks.Open("Filename")
xlWB.Worksheets("WhateverSheetYouWantSelected").Select
objExcel.ActiveSheet.Range("WhateverCellYouWantToSelectForFreeze").Select
objExcel.ActiveWindow.FreezePanes = True

This will work as many times as you use it and it will open it even if there is an existing workbook open. I would not use the GetObject method, but use the above instead, and maybe set a string variable to the file name you want to open.

Also, I would get out of the habit of using + signs for concatenation and use & instead as + can cause you some unwanted side effects depending on the items you are concatenating.

 

[Kenln]

I have done everything I can think of including your code.

It works great is there if no Excel already running. If excel is already running most things still work, Bold text, recordset, etc... but the FreezePane and AutoFormat will not work no matter what I try.

Even if I embed a macro in Excel in will not run if another instance of Excel is already loaded.

I am truely stuck.

 

[boblarson]

What version of Access and Excel are you using? I can run the exact same code that I have posted and it will open the same document over and over and over again depending on how many times I run it. Maybe we're not communicating every exact step completely. Are you trying to freeze the panes of an Excel Workbook that is currently open?

 

[Kenln]

I have been using Acess 2000 for compatibility, but I have also tested in with 2002-2003 with no sucess.

- If Excel is not open then everything works fine. All formating, everything including FreezePane is okay.

- I also test to see if my Workbook is already loaded, if so I call a msgbox and don't open it again.

- If Excel is open with another Workbook and the one I need is not open then... everything goes poorly.

 

[boblarson]

Can you share your database and Excel sheet, so I can try to replicate your problem? I don't have any problem with it right now and I'm wondering if it's your machine(s) or something else not even related to the workbook stuff. Using the exact same code I posted I can do everything you just described (with the exception of checking to see if the workbook is currently open).

I'm a bit baffled at your situation since I do this kind of thing with Excel frequently and haven't had the problems you describe. I wish I still had the tool that I had at my last job (Citrix's GoToAssist) where I could have you connect up with me and I could actually view things on your computer to see exactly what is happening. I am way more visual and troubleshooting by text message, or email, is way harder for me than actually seeing it.

Sorry I can't be more help.

 

[Kenln]

The spreadsheet is an empty sheet with nothing on it.

I don't believe I could send the entire db would the module to excel be okay? Even that has several line of code.

1. Check to see if the spreadsheet exist
2. Check to see if Excel is running
3. Check to see if the required workbook is loaded
4. Open the workbook if not.
5. Load Excel with data

So... my problem may be in something I missed but because if it's lenght I didn't just want to put it here to start.


Do I post here or send you an email???

 

[boblarson]

I'll PM you my email address.

 

[Kenln]

Bob,

Did you get the email?

 

[boblarson]

No, I didn't. Not sure why. Did you make sure to zip your files? Sometimes it doesn't like particular file types.

 

[Kenln]

I'll send the excel module via here.

I don't know what you didn't get it via regular email.

Thank you for your help.

Ken