Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

FreezePanes in Excel spreadsheet from within Access

Posted on 2011-09-26
5
Medium Priority
?
376 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
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 2000 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 49

Author Closing Comment

by:Dale Fye
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 36

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Implementing simple internal controls in the Microsoft Access application.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 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