Hi, I'll be happy to help with this issue - is this a general query or is there a specific make and model of printer in mind?
There are at least three different models. One of which is an HP Officejet 8600 Pro. I do not know the model of the others. The program will be used on all three at various times.
Thanks - and the idea is to use it between multiple spreadsheets?
Yes, it would be used between several spreadsheets within the same file. But, the spreadsheet selection is not the problem!
Ok - I've added this VBA based on this Microsoft KB and seems good here. Can you test with it similarly (I've used Excel 2007 and 2013)
Well, It much more complicated than what I had hoped for. First, After copying the macro into a newly created module, I get a compiler on teh following statements:
Public Declare Function ClosePrinter Lib "winspool.drv" _ (ByVal hPrinter As Long) As Long
Secondd, I am not looking to create a form. I haved added my macro to a button on the ribbon. Thr macro selects and prepares the sheets to be printed. I want to add the code to my macro to set or unset the duplex value based upon the logic within my macro.
In addition, I do not see what code I need to add to my macro. So, I am confused!
Thanks - I'm going to open this out to other experts who can look at those errors as they're not ones I'm seeing
you should get a message from them shortly
If it would help, I can somewhat sanitize the file and send it to you. Currently, it is not that big of a file. Then, you will be able to see what I am trying to do within the Print_Data macro, which selects filters data based on the date on one sheet, prints it. Then, the macro select another sheet and does the same thing. The macro loops through the data performing these tasks until it reaches the end.
I will sanitize the file tomorrow morning and then send it afterwards. But, I do not understand your last message when you said "....post here FileID you get." Can you explain this statement in more detail?
The file ID is 617302There are several macros included in the file. The one of interest to you is the “Print_Data” macro. Currently, I have the three (3) “ActiveSheet.PrintOut Copies = 1” statements commented out in order to support the checkout of the rest of the macro logic without actually printing. If I manually set the printer duplex to “Short” before running the macro, everything works great. My intent is to have the macro control the duplex setting to eliminate the manual step. Call me at (719) 487 – 8927 if you have questions. I am still working on the Set_Up macro. So, It may not work. All the others appear to be working.
I have one other issue, which I have not begun to work as yet. I want to add two (2) new macros to control the “Share Workbook” capability – one set the workbook to “Shared” and the other to set the workbook to “Un-Shared.” Both of these macros need to work correctly with the other macro that I have in place.
Hello!I apologize, the above code will not work, please ignore it.Please download the corrected code from here:http://ge.tt/3uOAkQT/v/0?cThe problem was that the Printer object does not exists in VBA, so instead of Printer.DeviceName the printer name can be obtained from Application.ActivePrinter.The corrected line looks like this:SetPrinterDuplex Trim$(Left$(Application.ActivePrinter, InStr(ActivePrinter, " on "))), 3 'AAATomI am sorry for the inconvenience.As I said, I'll test the code my self as soon as I get to the PC where I have more recent Excel version.Thank you,Tom
Okay, it works! Thank you very much. I assume that I need to keep the new module 1 that you sent.
Now, can you help on the other issue that I mentioned earlier about controlling the ”Share Workbook” capability using two macros – one to share and the other to un-share. I had tried recording the processes, but I did not get anything recorded for some reason.
Okay, I just ran test 4 and executed testduplex as requested. First, I made sure the printed duplex mode was set to single sided mode (or None). I set this duplex mode ar the printer level on the Devices and Printers menu. I ran the test and got the following results: "Current duplex setting is; 1" and "New duplex setting is: 3". But, I checked the printer duplex properties and it was still none. I printed the page and got single sided print out, which is consistent with the current printer duplex setting. So, it appears that your logic gets the correct setting (at least the on the first run). However, the logic still has noeffect on the actual printer duplex setting.
When I ran the test again without closing the file, I got the following results: "Current duplex setting is: 3" and :New duplex settingis: 3". The printer is still in single sided mode.
So, I guess this means that the sofware thinks that is controlling the duplex settings correctly, but the hardware isn't receiving the data.
Sorry, I took so long in getting back to you. I was out running some errands.
I did that already. The printer stays in whatever mode I manually set it to. The code does not affect the hardware at all.
No, It says 1 on the first run. On subsequent runs, it says 3 as long as I do not close the file.
Those are some good suggestions and I will keep them in mind. For now, I will continue to manually set the printer to short duplex before executing the macro. However, if you actually come across changes that would made the macro switch the duplex mode to the printer correctly, then please send me the changes. So, I guess that we are right where we started on this issue. As such, I do not beleive that I should be charged the $38.00. If you have to charge me the $38.00, then please cancel any future charges. Thanks for all your help.
Even though we have put considerable effort and time in trying to solve the duplex problem, I am essentially back to square one. Yes, I have gained some more knowledge, but it is not something that has helped. Maybe in the future, the added knowledge will be of some benefit. At this point, I am disappointed that we had not solved the duplex problem. So, it is time to move forward on to other problems!
As such, I still beleive that I should not be charged the $38.00. If you have to charge me the $38.00, then please make sure that you cancel any future charges. Thanks for all your help, anyway.
Yes, I want the refund