Set VBA for default printer.

[mrattana]

To All Experts,

I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

Thank you.

 

[jproffer]

Nice one Joe.

I take it that would just be in a regular module and a button would replace the normal excel printing commands?

 

[Joe Was]

Yes, That would do it or you can use the Before Print Event in The ThisWorkBook coce module, to trap the print and use my code. Then cancel the system print.

 

[jproffer]

There ya go . That would be even better...and you wouldn't have to disable the "normal" print and print preview buttons and menu items.

Very nice.

 

[jerik_sean]

thanks again for your replies. I think I still have to go thru the paperwork of requesting our administrator. But then again thanks again!

 

[jproffer]

Afraid so. I don't think there's any way of stopping them from printing to an "out of area" printer if it's available to them.

 

[jerik_sean]

Hi Joe,

Can you give a much detail way on your instruction? Just a newbie here sorry

 

[Joe Was]

Store the users current default or currently selected printer name, switch to a different network printer, do a PrintOut to the new printer. Then return the users printer as it was before printing with the utility.

The trick is you need the network address of the printer you want to switch the user to!
This avoids installing a printer through Windows. Other wise you would need to have all users configured with the the same set of printers, the network printer address avoids this.

Now for the BeforePrint Event code, this will prevent the user from printing unless they use your utility!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'ThisWorkBook code module code only!

Call PrintToOther
Cancel = True
End Sub

Note: I did not test the Event, I do not have different network printers at home, need to check it at work. The actual print utility was tested before!

 

[jerik_sean]

Store the users current default or currently selected printer name, switch to a different network printer, do a PrintOut to the new printer. Then return the users printer as it was before printing with the utility.

The trick is you need the network address of the printer you want to switch the user to!
This avoids installing a printer through Windows. Other wise you would need to have all users configured with the the same set of printers, the network printer address avoids this.

Now for the BeforePrint Event code, this will prevent the user from printing unless they use your utility!

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'ThisWorkBook code module code only!

Call PrintToOther
Cancel = True
End Sub

Note: I did not test the Event, I do not have different network printers at home, need to check it at work. The actual print utility was tested before!

Hi Joe,

Thanks! I will have it tested once i'm at work tomorrow. will give you feedback on this. Thanks once again!

 

[melodramatic]

OK, I know that I'm bringing up a really old post, but I followed this, as it will help me, and I'm getting an error.

I'm sending all the charts in my spreadsheet to pdf. I know that the macro works, but I have to switch my default printer before I run it each time to make it work.

My old code (before I made this change), reads:
-----------------------------------------------

Sheets(Array("Temps", "North", "South", "Loc1", "Loc2", "Loc3", "Loc4", _
"Loc5", "Loc6")).Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="docuPrinter on Ne02:", printtofile:=True, Collate:=True, _
PrToFileName:=PSnme

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF PSnme, PDFnme, tempShowWindow

Kill PSnme
Kill Lognme

End Sub

-----------------------------------------------
My modified code, following this post, reads:
-----------------------------------------------

Sheets(Array("Temps", "North", "South", "Loc1", "Loc2", "Loc3", "Loc4", _
"Loc5", "Loc6")).Select

originalPrinter = Application.ActivePrinter
Application.ActivePrinter = "docuPrinter on Ne02:"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, _
ActivePrinter:="docuPrinter on Ne02:", printtofile:=True, Collate:=True, _
PrToFileName:=PSnme

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF PSnme, PDFnme, tempShowWindow

Kill PSnme
Kill Lognme

Application.ActivePrinter = originalPrinter

End Sub

-----------------------------------------------

The problem is that now, when I try to run the macro, I get a message,

Run-time Error '1004':
Method 'ActivePrinter' of object '_Application' failed

When I click on debug, it has the following line hi-lited:

Application.ActivePrinter = "docuPrinter on Ne02:"

-----------------------------------------------

Can you help me figure out what's wrong with my code? I'm using Excel 2003.

Thanks!

 

[Joe Was]

You must have the full network address to the printer, I don't think you do in your code?

You know: "\\xyz\printerName on NetworkServer X"
You just have "PrinterName On NetworkServer X"