If this is your first visit, be sure to
check out the
FAQ
by clicking the
link above. You may have to
register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.
I am having difficulty with the Application.PrintCommunication property with early binding.
In the code below, the first DirectCast statement executes without any issues but the second DirectCast statement raises a COM error.
Code:
DirectCast(xlSheet.PageSetup, Excel.PageSetup).Application.PrintCommunication = False
With CType(xlSheet, Excel.Worksheet).PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.PrintArea = ""
End With '...PageSetup
'...setting PrintCommunication property to TRUE raises a COM error
DirectCast(xlSheet.PageSetup, Excel.PageSetup).Application.PrintCommunication = True
The error is "System.Runtime.InteropServices.COMException", which is not very helpful
Any insight/guidance on how to set the PrintCommunication property to True will be much appreciated.
Do you need to cast at all? You definitely don't need to cast twice. Do you not already have an Application object that you got the sheet from in the first place (probably via a workbook)? I don't do much, if any, Office Automation but I would have thought that the very first thing you did was create an Application object and that would be the same object you're getting from that PageSetup.Application property.
Ignoring all that, I doubt that it is actually the cast that the issue. You should break the statement up into parts and see which part fails:
vb.net Code:
Dim pageSetup = DirectCast(xlSheet.PageSetup, Excel.PageSetup)
Dim application = pageSetup.Application
application.PrintCommunication = False
'...
application.PrintCommunication = True
jmc -
Thanks for taking a look at this question. I modified my code to declare the PageSetup and Application objects per your suggestion. I still am getting the error when setting the PrintCommuncation property to True.
Here's the exception...
Attachment 185431
My application has several charts and I am using PageSetup for each one.
The weird thing is that setting PrintCommuncation True/False seems to work with the first chart only. Setting PrintCommunications = False works for subsequent charts but setting PrintCommunications = True for these charts fails.
Commenting out the PrintCommunications = True statements results in no errors.
I'm stumped...
Yeah, but I use early binding all the time and don't need to cast in most cases. It might be necessary - as I said, I don't use Office Automation - but if you already have the appropriate types then no casting should be required. What type is xlSheet? If it is Excel.Worksheet, which I would expect if you're using early binding, then is its PageSetup property not already type Excel.PageSetup? Also, do you need to go through that chain to get an Excel.Application object? I would expect that you had to create an Excel.Application object right at the start, in order to get a Workbook and/or a Worksheet. I would assume that that would be the same Application object, so you could probably just use that directly.
I'm not sure that any of this helps with your issue, but it will make your code cleaner and possibly easier to diagnose.
I'm trying to upload the attachment a second time, but I don't see the "go advanced" option.
The Worksheet object does not have a PrintCommunication property (per IntelliSence).
Passing the xlApp (Excel.Application) from my calling method to the method that manages PageSetup and setting
xlApp.PrintCommunication = True
in that method also fails.
I never suggested that it did. My point was that, if xlSheet is type Worksheet then I suspect that xlSheet.PageSetup is type PageSetup, so no cast is necessary. I could be wrong but that is what I would expect. That would mean that you can just do this:
vb.net Code:
Dim xlApp = xlSheet.PageSetup.Application
That's not completely unexpected, as it should be the same object. If you would otherwise have to pass the Application object to the method separately, i can see why you're getting it from the sheet. That makes sense.
I'll await more information about the exception and see where we can go from there. I might have to test the code myself but I'm working right now so I'll leave that for later.
I did a bit of reading and I saw one suggestion that setting PrintCommunication is probably pointless as you will see no real difference anyway. I also saw someone suggest that the second setting fails because the code actually executes faster than the initial communication with the printer so the first setting hasn't properly taken effect. You could try adding a delay before the second setting and see if it still fails to test that theory. I'd say probably just get rid of those lines and, unless you notice a particular delay with the printing process, leave them out.
My application has several sheets and several charts and I'm performing PageSetup on each of them. Setting PrintCommunications = False make the PageSetup execute a lot faster (haven't benchmarked it, but it is in the order of several seconds per PageSetup).
I tried adding System.Threading.Thread.Sleep(2000) after the PrintCommunications = False statements and also before the PrintCommunications = True statements but the error on PrintCommuncations = True still occurs. Adding similar delays in the calling method does not fix the error.
I appreciate your perseverance with this question.
Do the page setup commands still get sent to the printer though? If so then I guess it's fine. Maybe the page setup is forced to the printer when you actually print. I'm really just guessing. If it works then I guess it's OK.
I did see something in my searches where someone had an issue if the page setup was not done in VBA so I wonder whether this just won't work from VB.NET due to some internal bug. Again, just guessing.
Go through the individual Settings in the PageSetup (comment them out one by one)
I'm guessing, that you're trying to use a Setting your printer doesn't understand.
OR: Have you checked your Printer-Properties, if bidirectional communcation is even active?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Dim p As PageSetup
Set p = Sheets("sheet1").PageSetup
p.Application.PrintCommunication = False
though as print communication is a property of the application all you really need is
Code:
Application.PrintCommunication = false
or i would think, in your .net code
Code:
Excel.PrintCommunication = false ' or True
you can put any other pagesetup methods into your vba for testing, this should at least demonstrate if the problem is in .net, or with availability of printer options
if it works in vba then you can try to diagnose the problem in .net
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note
code snippets posted are just that and do not include error handling that is required in real world applications, but avoid
On Error Resume Next
dim all variables
as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
Advertiser Disclosure:
Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
