Excel Automation - specifying destination printer 3

[AndrewMozley]

have been developing applications in VFP6 & VFP8 to interface with Microsoft Excel and have found Tamar Granar's book "Microsoft Office Automation" a good starting point.
However I am finding it difficult to find the properties of the various objects which I create after code such as

oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Add()
oSheet = oWorkbook.WorkSheets(1)

If I use the VFP debugger I can watch oExcel and oWorkbook and these can be expanded with the plus icon to show lots of of properties. But oSheet shows very few and osheet.pagesetup does not show any.

I can get some help by invoking Intellisense when I key in (e.g.) "osheet." in the watch window but the list box often seems to be populated with names which I doubt apply to the object, e.g. if I key in "oSheet.printout."

The specific thing I want to do is to set the printer name (which I shall have established elsewhere) for a command to Printout a sheet. But in more general terms, is there a way of finding out what the names of the properties that I can use in Excel Automation?

Thanks. Andrew M.

 

[Mike Lewis]

Hi Andrew,

To answer your more general question ....

1. Open Excel.

2. Go to Tools / Macros / Visual Basic Editor.

3. From within the VB Editor, go to View / Object Browser.

4. In the combo box in the top left corner of the object browser, choose Excel.

5. Scroll down the classes list until you reach the class of interest. Highlight it, then read its properties and methods in the members list.

When you highlight a member (property or method), you can see more details at the foot of the window. Or, click the Help button to open specific help for the member (you might need to retro-install the VBA help file to get the help).

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndrewMozley]

Thanks Mike

That is certainly useful and I have made a note.

As far as setting the printer is concerned, I see that there is a property <application.activeprinter>. This currently shows "hp psc 1310 series on Ne01:" - which is my printer with the " on Ne01:" phrase appended.

However if I try to set this property with

oExcel.ActivePrinter = "pdffactory"

I get "OLE dispatch error - cannot set the Activeprinter property of the application class".

Any ideas? Thanks - Andrew M.

 

[Mike Gagnon]

Andrew

You may find that the syntax seems to be specific in this case.
Here is what I did.
1. Set Adobe PDF as my default Printer.
2. Used your code :

oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Add()
oSheet = oWorkbook.WorkSheets(1)
_cliptext=oExcel.ActivePrinter

Using CTRL+V after the last line I get
Adobe PDF on Ne04:
3. Then I switched my default printer to another one.
And I get
"Auto Microsoft Office Document Image Writer on JUSTIN on Ne02:" As the active printer.
4. Now I can alternate between the two using the exact syntax without errors

oExcel.ActivePrinter="Adobe PDF on Ne04:"
oExcel.ActivePrinter="Auto Microsoft Office Document Image Writer on JUSTIN on Ne02:"

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[AndrewMozley]

Thanks Mike. This is working pretty well but I have a problem with identifying ports. I have 2 printers known to Excel as

HP1317 on Ne01:
pdffactory on FPP2:

However if I look at Control Panel | Printers, the HP1317 has its port marked as USB001 And this is what APRINTERS() returns.

My problem is that I construct the name which I want to assign to .Activeprinter by invoking GETPRINTER() and then using the array returned from APRINTERS() to get the port. I then construct the required property.

On searching the web this dual identification (USB001 / Ne01 of ports is also a problem to other people.

Does anyone know how I can determine the port name (in this case Ne01 of a printer?

Thanks. Andrew Mozley

 

[Mike Gagnon]

Perhaps you are looking at things at the wrong end. Excel.activeprinter is mostly used to determine the active printer for output (I understand you can use the property also to set the active printer providing you know the name of the printer and the port, which does not seem to be the case here.
Perhaps you can force a Windows default printer change and forget trying to change it in Excel.

oNet = CreateObject('WScript.Network')
oNet.SetDefaultPrinter(GETPRINTER())
oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Add()
oSheet = oWorkbook.WorkSheets(1)
?oExcel.ActivePrinter

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)