Solved

FreezePanes in Excel spreadsheet from within Access

Posted on 2011-09-26
5
364 Views
Last Modified: 2012-05-12
I've got a subroutine that exports data from Access to Excel and then (thanks to rorya) formats that data.

The only thing that isn't working is that I'm unable to use FreezePanes so that the top two rows and left two columns are always visible.  Both techniques (mine and rorya's) in the original post are successful at freezing the left two columns, but neither freezes the top two rows.

0
Comment
Question by:Dale Fye (Access MVP)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36600284
try this simple codes


Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
    xlApp.workbooks.Open <path to excel file>
    With xlApp
        .worksheets(1).select
        .range("C3").select
        .ActiveWindow.FreezePanes = True
        .activeworkbook.Save
    End With
        xlApp.Quit


with your codes

'    sht.Range("C3").Select
'    xlapp.ActiveWindow.FreezePanes = True

    With xlApp
        .worksheets(1).select
        .range("C3").select
        .ActiveWindow.FreezePanes = True
        .activeworkbook.Save
    End With
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36600297
Hi, fyed.

In your previous solution, please try replacing line 109 ("  xlapp.ActiveWindow.FreezePanes = True") by...

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

Open in new window


Regards,
Brian.
0
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 36600308
fyed,

I would make sure FreezePanes is off before selecting the pivot cell and turning it on:

If ActiveWindow.FreezePanes = True Then
    ActiveWindow.FreezePanes = False
End If
   
Range("C3").Select
ActiveWindow.FreezePanes = True

Also, if you ever have a direct reference to a window, use it instead of ActiveWindow to avoid confusion/errors.

HTH,
pT72
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 36600355
capricorn1:  Tried that method, same results

redmondb:  This is one of the EE responses I tested before posting, but I want to freezepanes, not split.

PT72:  That seems to have resolved the issue, although I cannot figure out why.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36600374
This has worked for me.
Sub FreezeExcel()
Dim appXL As Object
Dim wbXL As Object
Dim wsXL As Object
Dim windowXL As Object

    Set appXL = CreateObject("Excel.Application")
    
    Set wbXL = appXL.Workbooks.Open("C:\FreezeTest.xlsm")
    
    
    Set wsXL = wbXL.Worksheets("Data")
    
    appXL.Goto wsXL.range("A1"), True
    
    appXL.Goto wsXL.range("B2")
    
    Set windowXL = appXL.ActiveWindow
    
    
    windowXL.FreezePanes = True
    
    wbXL.Close True
    
    appXL.Visible = True
    
    appXL.Quit
    
    Set appXL = Nothing
    
End Sub

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question