Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

FreezePanes in Excel spreadsheet from within Access

Posted on 2011-09-26
5
Medium Priority
?
373 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
[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 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 48

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 35

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

604 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