Microsoft Office

Microsoft Office Questions? Ask a IT Expert for Support ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Microsoft Office
This answer was rated:

need to know how to use excel 2010 vba to control printer

duplex...
need to know how to use excel 2010 vba to control printer duplex
Ask Your Own Microsoft Office Question
Answered in 7 hours by:
12/11/2012
Steve Herrod
Steve Herrod, Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 3,326
Experience: Trained in Microsoft Office from 2000 through to latest version 2010.
Verified

Steve Herrod :

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?

Steve Herrod :

cheers

Steve Herrod :

Steve

Customer:

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.

Steve Herrod :

Thanks - and the idea is to use it between multiple spreadsheets?

Customer:

Yes, it would be used between several spreadsheets within the same file. But, the spreadsheet selection is not the problem!

Steve Herrod :

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)

Steve Herrod :

Cheers

Steve Herrod :

Steve

Customer:

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:

Customer:

Public Declare Function ClosePrinter Lib "winspool.drv" _
(ByVal hPrinter As Long) As Long

Customer:

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.

Customer:

In addition, I do not see what code I need to add to my macro. So, I am confused!

Steve Herrod :

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

Steve Herrod :

you should get a message from them shortly

Customer:

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.

Ask Your Own Microsoft Office Question
AAATom
AAATom, Consultant
Category: Microsoft Office
Satisfied Customers: 468
Experience: Master's in Electrical Engineering, 25+ yr exp. PCs & Win
Verified
Hello!

I may be able to help you solve this problem but I need to have a look at the macro you are using.

Can you, please, upload the file you "sanitized" to www.wikisend.com and post here the FileID you get?

Also, I'm in Europe, and it is 3am here now so I'll soon go offline for the next 10 hours or so. Let me know if that will be a problem.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

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?

Hello!

I apologize for a delay.

Tomorrow will be fine. When you post here I'll respond as soon as I can (I'll be online in some 8 hours from now).

About FileID - after you upload a file to Wikisend you will be given a FileID number - just post that number here so I can download the file.
Let me know if you need more detailed instructions for Wikisend.

Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

The file ID is 617302
There 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!

Thank you for sending the file!

Please give me few minutes to check it up.

Tom
Ask Your Own Microsoft Office Question
OK.

From what I can see the Microsoft article my fellow expert pointed out is showing the way to go.

I'll integrate that code into your file and will add the appropriate lines into the Print_Data macro.

It will take me some half an hour with testing.
I'll post here when I have some results.

Tom
Ask Your Own Microsoft Office Question
OK, here I am.

Please download the modified Excel file from here (Wikisend is having problems at the moment):
http://ge.tt/9bZMZQT/v/0?c

The file is zipped with password "1234" since some servers filter out files with macros.

I added Module1 with code for the SetPrinterDuplex sub and added appropriate calls to Print_Data macro, like this one:
SetPrinterDuplex Printer.DeviceName, 3 'AAATom

It took me a bit longer because I was testing the code. The thing is, at this PC I have only Excel2003 which cannot run all the statements in the macro. I do have Excel2007 at home but will not be able to test it sooner than some 4 hours from now.

So, please test if the code works as expected.
The SetPrinterDuplex macro pops up message boxes if it encounters problems - write them down (or just look in the code) and let me know which ones popped up (if any).

If there are any problems, I'll try to sort them out when I get home to my PC.

Thank you,
Tom
Ask Your Own Microsoft Office Question

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?c

The 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 'AAATom

I 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

AAATom
AAATom, Consultant
Category: Microsoft Office
Satisfied Customers: 468
Experience: Master's in Electrical Engineering, 25+ yr exp. PCs & Win
Verified
AAATom and 87 other Microsoft Office Specialists are ready to help you
Ask your own question now
Customer reply replied 5 years ago

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.

You are welcome!

I'm glad that you are satisfied!

I would appreciate it very much if you would rate the answer above so I can get paid for my time. Smile

About, the sharing workbook macros - please open a new question for that issue.
If you want me to help you with it, please start the question with "For AAATom" to make sure that I get it.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Hello!

I have added a missing messagebox on an error in SetPrinterDuplex and have added checking the result of the call to SetPrinterDuplex in Print_Data macro.
I wrote a new macro named GetPrinterProperty to be able to check the value of the duplex setting.

I have also added the testduplex macro from my previous post and expanded it for more testing. It is in Module2.
Please set the duplex mode to none manually, then run the testduplex macro and let me know the result (the "current" and "new" values of duplex setting shown in messageboxes).

You can download the modified file from here:
http://wikisend.com/download/950178/printer duplex test-4.zip

or alternatively from here:
http://ge.tt/7S5G4WT/v/0?c

Thank you,
Tom

P.S. I have marked this as an Answer only to hide it from unauthorized users, you do not have to rate it.
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

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.

OK, thank you!

Yes, this is strange behavior.

Can you please set the printer do duplex manually and then run the testduplex macro to see if the code sees the manual settings?
Close the file first to make sure the code did not set the duplex.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

I did that already. The printer stays in whatever mode I manually set it to. The code does not affect the hardware at all.

Sorry for a delay, I'm getting ready to sleep (it's 3am here).

What I meant was to check whether the manual setting reflects in what the code sees, e.g. when you manually set to duplex, does the testduplex macro say "Current duplex setting is: 3" (when first run after opening the file).

That would show that the code gets the correct values from the printer driver but fails to send them back.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

No, It says 1 on the first run. On subsequent runs, it says 3 as long as I do not close the file.

Hello!

I apologize for a delay.
For the last couple of hours I was trying to reproduce the problem you describe.

From your information it appears as though the code "sees" different printer interface than that in the print setup dialog.

That may be due to the printer driver in which case there is nothing we can do regarding this approach.
This could be tested by using an other duplex capable printer or maybe by updating the printer drivers.

The code itself is correct and is working since you do not get any errors.

At this point I do not know what else could be done in the code.

Maybe you can try a workaround.
One of the typical workarounds used in cases like this is to install another printer of the same type with different name.
On that second printer the default duplex setting is set to desired value.
Then in the VBA code the used printer is switched between the two as necessary.

Let me know if you want to try this approach.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

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.

I am glad that you find my advice worth remembering.

On the other hand, I am really sorry that the solution I provided did not work in your specific case. That is so even more because I have put considerable effort and time in trying to help you solve the problem.
As a professional I definitely feel obligated to provide you with the solution if I do find it.

It is true that you are practically back to square one with this problem but it is also true that you now have more information about the problem than at the beginning.

If you still feel that you should not pay anything for this, let me know and I will forward your request to Customer Service.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

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.

Hello!

I'm really sorry that you are disappointed. Sometimes there are no practical solutions to a problem.

My goal is to provide you with a satisfactory answer but if you are not satisfied you are entitled to a refund. Please confirm that you actually want a refund so I can forward your request to Customer Service.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Customer reply replied 5 years ago

Yes, I want the refund

OK, I will forward your request to Customer Service right after I post this.

Once again, I'm really sorry that you did not get what you were looking for. Sometimes there are simply no solutions for some problems.

Thank you,
Tom
Ask Your Own Microsoft Office Question
Ask AAATom Your Own Question
AAATom
AAATom
AAATom, Consultant
Category: Microsoft Office
Satisfied Customers: 468
468 Satisfied Customers
Experience: Master's in Electrical Engineering, 25+ yr exp. PCs & Win

AAATom is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

JustAnswer in the News:

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

Meet the Experts:

jstinehelfer

jstinehelfer

Information Systems Manager

36 satisfied customers

A+ Comptia Certified computer repair

JasonJames122

JasonJames122

Computer Enthusiast

0 satisfied customers

I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business

Jess M.

Jess M.

Computer Support Specialist

701 satisfied customers

Computer Software Specialist for more than 10 years

The-PC-Guy

The-PC-Guy

Computer Manager

588 satisfied customers

20 years experience providing remote computer support

James K.

James K.

Consultant

260 satisfied customers

Technical Director of IT Company

IT Miro

IT Miro

Computer Scientist

157 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Chris L.

Chris L.

Support Specialist

147 satisfied customers

Certified Expert with over 10 years experience.

< Previous | Next >

Related Microsoft Office Questions
CAN YOU HELP ME RETRIEVE MY PRODUCT CODE I JUST PURCHASE FOR
CAN YOU HELP ME RETRIEVE MY PRODUCT CODE I JUST PURCHASE FOR MY MICROSOFT OFFICE … read more
Moses C
Moses C
Bsc
330 satisfied customers
I installed windows 10 update assistant and now I cannot
I installed windows 10 update assistant and now I cannot open Norton or office programs. click-2-run virtualisation handler keeps popping up. … read more
Richard
Richard
IT Manager
IT Manager, Consultant
3,275 satisfied customers
I can not open my skype account. No I did not rearranged my
No I did not rearranged my file folder, Microsoft did automatically … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
My fairly new computer does not recognize me and I don't
My fairly new computer does not recognize me and I don't remember ther password. … read more
Josh
Josh
It Support Specialist
Associate Degree
5,140 satisfied customers
I wish to add a gmail account to my outlook (Office 365) I
I wish to add a gmail account to my outlook (Office 365) … read more
UranIT
UranIT
IT Consultant, Network and System Admin
Associate Degree
567 satisfied customers
My Word doc has a black dot in the red circle on top LH
My Word doc has a black dot in the red circle on top LH corner. it seems to be locking it so I can not open the doc. Please help. … read more
Isaac Franklin
Isaac Franklin
Associate of Science in Social Sciences
960 satisfied customers
You have assisted me in the past. Do you have any experience
Hello, You have assisted me in the past. Do you have any experience pushing an application install using group policy? I need some assistance.Thank you,Yuri… read more
The-PC-Guy
The-PC-Guy
Computer Manager
Bachelor's Degree
588 satisfied customers
Can I open a Mac PowerPoint 2011 from a Mac PowerPoint 2013.
Can I open a Mac PowerPoint 2011 from a Mac PowerPoint 2013 … read more
Jason Jones
Jason Jones
AAS Information Technology
6,320 satisfied customers
I was just speaking with a tech support person about my
I was just speaking with a Bell tech support person about my computer data usage, which is mysteriously going over my pre-determined plan for 4 months now. He seems to think that Windows 10 is trying … read more
Chris L.
Chris L.
Support Specialist
Vocational, Technical or Trade Scho
147 satisfied customers
I have not been able to get to my documents. What so I need
Hi, I have not been able to get to my documents. What so I need to do to read them? … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
How to get free download. Free download for Microsoft word.
How to get free download JA: JustAnswer requires a modest payment. You'll see the amount on the next page. It's way less expensive than face-to-face would cost. And you're covered by our 100% satisfac… read more
Bryan
Bryan
IT Consultant
8,751 satisfied customers
Micorsoft Word is not working. Early December. I have had
I have had the problem before and work with a tech from Just Ans and it was fixed (i think in October) then in early December I worked with an Apple tech with mostly photo, i photo, and storage. During that time Microsoft Word stopped working. Apple tech told me to contact you. Holiday travel happened and I am just now getting to the problem. I have used the Microsoft Reporting Error to no avail. … read more
Jason Jones
Jason Jones
AAS Information Technology
6,320 satisfied customers
I had trouble with my computer several months ago, once I
I had trouble with my computer several months ago, once I got things going half way decent, I realized I no longer had Mircosoft office on my computer. How can I get it back? … read more
Byron
Byron
IT Support Specialist
Bachelor\u0027s Degree
410 satisfied customers
I use Windows 10 and Office 365 in my PC. I can not open it.
I use Windows 10 and Office 365 in my PC. I can not open it. When I try, the message "Getting Windows ready. Don't turn off your computer." After several hours I turned it off, but when I turn it on t… read more
jeffreybongers
jeffreybongers
ICT Support 1/2/3/ Line
Information Technology
193 satisfied customers
A few months ago I began getting a message when closing any
A few months ago I began getting a message when closing any Word (version 14.7.7)document: "Word Could Not Fire Event." I am running on a Mac, OS 10.11.6. I following the suggestion from the chat room… read more
Mr.Med
Mr.Med
939 satisfied customers
I need to reduce my spreadsheet to only the entries I put in
I need to reduce my spreadsheet to only the entries I put in one of my columns … read more
Pete
Pete
Engineer
Bachelor\u0027s Degree
1,124 satisfied customers
Am trying to get the picture in my Excel footer to go to the
Hey I am trying to get the picture in my Excel footer to go to the back so my text appears over it. But I can't seem to move it backward? … read more
Jess M.
Jess M.
Computer Support Specialist
Bachelor's Degree
701 satisfied customers
I GOT A CALL THE OTHER DAY SAYING THAT MY MICROSOFT LICENCE
I GOT A CALL THE OTHER DAY SAYING THAT MY MICROSOFT LICENCE WAS ABOUT TO EXPIRE WHAT DOES THAT MEAN AND ALSO ITS TAKING A LONG TIME FOR ME TO ACESS GOGLE … read more
bbao
bbao
Freelance IT Consultant
74 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x