[AccessD] Freeze Panes

I only build Excel files via automation in Access, and use late binding
due to the inconsistency of versions across the enterprise. Everything is
formatted; multisheets, fontsize & color, column header colors wrapping
centering, row height and width, autofilters, logos, title rows, etc.

I always set excel to not be visible but that doesn't seem to matter to
the activewindow. And that is the activewindow in Excel not windoze,
because it's calling yourobject.activewindow.

I'm not sure if you can freeze a row and column and I think that is where
the problem lies. I believe that you can only freeze rows or columns, not
a combination. I can't seem to manage doing that in the user interface, so
you probably can't do it in code.

Have you tried what I suggested to see if that would work? Replace the
range statement with a rows statement. Also, if you want the top row to
remain static do the freeze on row 2.

Your code looks fine to me except for the .range statement.

Let me know.

Jim





> Jim:
>
> Will this work in Automation?
>
> In this program I wrote I create a spreadsheet and write a bunch of values
> to it.  And I do some formatting of column width and row height.  So far
> so
> good.  This spreadsheet BTW is not visible.  It's just being created and
> populated with the data.  Before I close it, I want to freeze the first
> row
> and column so when the user opens it and scrolls around the data in the
> first row and column stay put.
>
> Does your function only work if the spreadsheet is the active window?  In
> all the formatting I do I refer to this spreadsheet object.  It's never
> visible.  Don't I need to refer to the object to set the Freeze?
>
> Here's the code snip with the bad boy commented out:
>
> ' Formatting
>     objXLS.Rows(1).HorizontalAlignment = xlGeneral
>     objXLS.Rows(1).VerticalAlignment = xlBottom
>     objXLS.Rows(1).WrapText = False
>     objXLS.Rows(1).Orientation = 90
>     objXLS.Rows(1).AddIndent = False
>     objXLS.Rows(1).IndentLevel = 0
>     objXLS.Rows(1).ShrinkToFit = False
>     objXLS.Rows(1).ReadingOrder = xlContext
>     objXLS.Rows(1).MergeCells = False
>     objXLS.Rows(1).RowHeight = 200
>     objXLS.Columns.ColumnWidth = 2.6
>     objXLS.Columns(1).ColumnWidth = 60
>     'objXLS.Range("B2").Select
>     'objXLS.ActiveWindow.FreezePanes = True
>
>     objXLApp.ActiveSheet.PageSetup.PrintGridlines = True
>
> objXLApp.ActiveWorkbook.Save
> objXLApp.Quit
> Set objXLApp = Nothing
>
> MsgBox "Done. Where used Spreadsheet exported to " & vbCrLf & vbCrLf &
> strPath & "IntelWhereUsed.xls"
>
> I tried both ActiveWindow and ActiveSheet.  Neither will compile.
>
> Thanks and regards,
>
> Rocky
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
> Sent: Friday, March 02, 2007 1:11 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Freeze Panes
>
> This works on my machine HTH
> Jim Hale
>
> Function freez()
> Dim wr As Window
> Set wr = ActiveWindow
> wr.FreezePanes = True
> End Function
>
> -----Original Message-----
> From: Rocky Smolin at Beach Access Software
> [mailto:rockysmolin at bchacc.com]
> Sent: Friday, March 02, 2007 2:34 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Freeze Panes
>
> Jim:
> The problem is the next line which won't compile.  So I can't even test
> the
> Rows statement. :(
>
> Rocky
>
>
>
> ***********************************************************************
> The information transmitted is intended solely for the individual or
> entity
> to which it is addressed and may contain confidential and/or privileged
> material. Any review, retransmission, dissemination or other use of or
> taking action in reliance upon this information by persons or entities
> other
> than the intended recipient is prohibited.
> If you have received this email in error please contact the sender and
> delete the material from any computer. As a recipient of this email, you
> are
> responsible for screening its contents and the contents of any attachments
> for the presence of viruses. No liability is accepted for any damages
> caused
> by any virus transmitted by this email.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
> 2:43 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>