How JustAnswer Works:

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

Ask AAATom Your Own Question

AAATom
AAATom, Consultant
Category: Microsoft Office
Satisfied Customers: 448
Experience:  Master's in Electrical Engineering, 25+ yr exp. PCs & Win
Type Your Microsoft Office Question Here...
AAATom is online now
A new question is answered every 9 seconds

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

Resolved Question:

need to know how to use excel 2010 vba to control printer duplex
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Steve Herrod replied 1 year ago.

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.

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year 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?

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
Hello!

Thank you for sending the file!

Please give me few minutes to check it up.

Tom
Expert:  AAATom replied 1 year ago.
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
Expert:  AAATom replied 1 year ago.
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
Expert:  AAATom replied 1 year ago.

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, Consultant
Category: Microsoft Office
Satisfied Customers: 448
Experience: Master's in Electrical Engineering, 25+ yr exp. PCs & Win
AAATom and 5 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
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
Expert:  AAATom replied 1 year ago.
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.
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year ago.

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

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year 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.

Expert:  AAATom replied 1 year ago.
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
Customer: replied 1 year ago.

Yes, I want the refund

Expert:  AAATom replied 1 year ago.
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

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 Customer New York
< Last | Next >
  • 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 Customer New 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!!!! Alex Los 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. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, 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. Esther Woodstock, 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. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional