Automating using Excel...Freeze panes error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I was successful with freezing the panes with my code. my code is in a
module which is called a few times by a sub. the first time the module
creates a new spreadsheet and then every other time it opens the existing
spreadsheet. on these subsequent iterations, the spreadsheets opens and
everything works other than the freeze panes. i will copy/paste a snippet of
the code.

thanks

sam

Set MyExcelInstance = New Excel.Application
If intCount = 1 Then
Set MyWorkbook = MyExcelInstance.Workbooks.Add
Excel.Application.DisplayAlerts = False
MyWorkbook.SaveAs "C:\" & ReportName
Excel.Application.DisplayAlerts = True
Else
Set MyWorkbook = MyExcelInstance.Workbooks.Open("C:\" & ReportName &
".xls")
End If
Set MySheet = MyWorkbook.Worksheets.Add
MySheet.Name = strPaygroup
MySheet.Paste Destination:=MySheet.Range("A1")
MyWorkbook.Save

'freeze panes
MySheet.Range("A5").Select
ActiveWindow.FreezePanes = True
 
Hi Sam

I suggest you use this code to freeze the panes:

With MyExcelInstance.ActiveWindow
.SplitColumn = 1
.SplitRow = 5
.FreezePanes = True
End With

Adjust SplitRow and SplitColumn to suit - this will freeze the left column
and the top five rows (which I *think* is what you want).

Note that you should never refer to a property or method of the "foreign"
application without qualifying it.

For example, ActiveWindow is unambiguous in the context of code running in
Excel, but if the code is running in Access then it may produce
unpredictable results, so you should qualify it with your Excel.Application
object variable:
MyExcelInstance.ActiveWindow

The same with application methods such as DisplayAlerts. You should be
using:
MyExcelInstance.DisplayAlerts = False
 
Thanks. this solved the problem.

sam

Graham Mandeno said:
Hi Sam

I suggest you use this code to freeze the panes:

With MyExcelInstance.ActiveWindow
.SplitColumn = 1
.SplitRow = 5
.FreezePanes = True
End With

Adjust SplitRow and SplitColumn to suit - this will freeze the left column
and the top five rows (which I *think* is what you want).

Note that you should never refer to a property or method of the "foreign"
application without qualifying it.

For example, ActiveWindow is unambiguous in the context of code running in
Excel, but if the code is running in Access then it may produce
unpredictable results, so you should qualify it with your Excel.Application
object variable:
MyExcelInstance.ActiveWindow

The same with application methods such as DisplayAlerts. You should be
using:
MyExcelInstance.DisplayAlerts = False

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

SAm said:
Hi

I was successful with freezing the panes with my code. my code is in a
module which is called a few times by a sub. the first time the module
creates a new spreadsheet and then every other time it opens the existing
spreadsheet. on these subsequent iterations, the spreadsheets opens and
everything works other than the freeze panes. i will copy/paste a snippet
of
the code.

thanks

sam

Set MyExcelInstance = New Excel.Application
If intCount = 1 Then
Set MyWorkbook = MyExcelInstance.Workbooks.Add
Excel.Application.DisplayAlerts = False
MyWorkbook.SaveAs "C:\" & ReportName
Excel.Application.DisplayAlerts = True
Else
Set MyWorkbook = MyExcelInstance.Workbooks.Open("C:\" & ReportName
&
".xls")
End If
Set MySheet = MyWorkbook.Worksheets.Add
MySheet.Name = strPaygroup
MySheet.Paste Destination:=MySheet.Range("A1")
MyWorkbook.Save

'freeze panes
MySheet.Range("A5").Select
ActiveWindow.FreezePanes = True
Click to expand...
Click to expand...
 
Back
Top