Automating using Excel...Freeze panes error (1 Viewer)

[SamDeMan]

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
    MsgBox MyExcelInstance
    
    'freeze panes
    MySheet.Range("A5").Select
    ActiveWindow.FreezePanes = True

 

[RoyVidar]

You are using unqualified references to Excel objects, which might cause anomalities like this, leave an instance of Excel in memory (check task manager), provide some 1004 automation error, make the code fail the second time... Here are some small attempts at correction, hopefully they will suffice

Set MyExcelInstance = New Excel.Application
    If intCount = 1 Then
        Set MyWorkbook = MyExcelInstance.Workbooks.Add
        'Excel.Application.DisplayAlerts = False
        MyExcelInstance.DisplayAlerts = False
        MyWorkbook.SaveAs "C:\" & ReportName
        'Excel.Application.DisplayAlerts = True
        MyExcelInstance.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
    'MsgBox MyExcelInstance
    
    'freeze panes
    MySheet.Range("A5").Select
    'ActiveWindow.FreezePanes = True
    MyExcelInstance.ActiveWindow.FreezePanes = True

 

[SamDeMan]

Thanks Roy, It worked...This Worked:
MyExcelInstance.ActiveWindow.FreezePanes = True

i sought of knew that the referencing is a problem, but i still don't quit understand it. the explanation you gave doesn't really explain it to me, since i close up the excel connection entirely each time i exit the module (otherwise it would open the spreadsheet in read only and give me a whole buch of errors).

Thanks a lot,

sam

 

[RoyVidar]

Whenever you use excel objects, methods and properties, you need to "anchor" them to their parent object. When automating Excel, the variable representing the Excel application, is the "father" of all the objects you're using. You use that to create a workbook object, on which you may create a sheet object etc. Whenever you use an object belonging some place in this hierarchy, you will need to "anchor" it to it's parent object. The freezepanes property, is a property of the window object, here represented by the ActiveWindow, which should have been anchored to the application object.

If not, what will usually happen is that Access will create a second instance of the automated object, to which the "un-anchored" actions are applied, causing both us, Access and Excel to be confused

I'm probably not good at explaining this, but here's a MS KB article with some more info http://support.microsoft.com/default.aspx?kbid=178510

 

[SamDeMan]

That was a pretty good explanation. now it makes sence to me.

thanks,

sam