Hi Adam
Another advantage of Early Binding is that you have the use of the Object Browser. Press F2 in the VB editor window and the OB window will appear. From there you can search your project for named entities, whether or not you are using them. You can also narrow down your search from "<All Libraries>" to say just "Excel".
If you search the Excel library for ActiveWindow, you will see it is a member of the Application class, not the Worksheet class. You are referring to ActiveWindow (in a With statement)as a member of xlSheet, which is presumably declared "As Excel.Worksheet"
What you need is:
xlApp.ActiveWindow.FreezePanes = True
Actually, for reasons I have outlined before, I would avoid the use of ActiveWindow if possible (and it turns out it *is* possible!)
FreezePanes is a method of a Window object (not of a Worksheet, where a sensible designer would have placed it!) A Worksheet does not have a Windows collection, but a Workbook does. Therefore my preferred method to freeze the panes in a given Worksheet would be to access its Workbook's Windows collection through its Parent property:
With xlSheet
.Range("C1").Select
.Parent.Window(1).FreezePanes = True
End With
I notice you are still avoiding declaring and using a separate Workbook object:
Any reason? I would go for this option:
Set xlApp = CreateObject("Excel.Application")
Set xlDoc = xlApp.Workbooks.Open(stFileName)
Set xlSheet = xlDoc.Sheets("NameOfSheet")
… where xlDoc is declared "As Excel.Workbook"
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 4 February 2015 07:13
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Error controlling Excel from Access
Graham,
Again, thanks so much for the detail. I have most of this working (this is for several applications). However, I'm still getting RT error 438, "Object doesn't support this property or method" at the FreezePanes line:
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(stFileName).Sheets(1)
With xlSheet
.Range("C1").Select
.ActiveWindow.FreezePanes = True
End With
Adam
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (21) |
Tidak ada komentar:
Posting Komentar