Solved

FreezePanes in Excel spreadsheet from within Access

Posted on 2011-09-26
5
361 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)
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 33

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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