Solved

FreezePanes in Excel spreadsheet from within Access

Posted on 2011-09-26
5
360 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 119

Expert Comment

by:Rey Obrero
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now