Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Way To Set The Printer's Properties Using VBA

Is there a way to set the printer's properties using VBA?

Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.

I want to be able to set the print properties to normal using VBA.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Selecting A Printer
I have a form that all the salespeople use. The last thing in the process is to print the completed form to a specific printer. I recorded a macro to get the proper name of the printer but I am finding out that it records differently for each computer. Is there a way around this?

View Replies!   View Related
Choosing A Printer
I want the user to be able to choose a printer but not print at that time.

1. Store the active printer as a variable using application.activeprinter
2. Have a dialog box (the printers available) popup and have them choose one, then store that

3. I know how to do the rest, but I will have the word document print out with the selected printer, then use the application.activeprinter reset to the original one

View Replies!   View Related
Set VBA For Default Printer
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?

View Replies!   View Related
Set Printer To Cell Value
I have a quality form that is produced from data on an Excel log file. I was wondering if it is possible to select a different network printer based upon the value of a certain cell.

I understand how to print using the local machines local printer but our quality department is located at one site and these forms need to be printed at our other locations.

View Replies!   View Related
Change Default Printer
I have got a buttion that changes various layout and borders for the page then shows a printpreview, after printprewiew closes.

Then a msgbox that askes if you would like print. Is there a way to change the defult printer between local printer and adobe distiller(.pdf) as a dropdown option box. Or Yes for Local printer and NO for adobe distiller(.pdf) using vbYesNoCancle.

View Replies!   View Related
Change The Printer Proprties Using VBA
i am using windows xp, and i am printing from a matrix printer, so i go to file then print then on the top right of the dialog box it say properties, and i go there to change the printers speed and darkness, the i click ok to print.

View Replies!   View Related
Find Active Printer
I have excel printing some sheets to pdf and it works fine on my PC. WHen someone else tries to print it wont work because they arent on "Adobe PDF on Ne02:"...they maybe on Ne03 or Ne08 or get my point.

I saw code on here before as to how to find the proper "port" but after a bit of searching, i cant find it again.

Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:"

View Replies!   View Related
Choosing Specific Printer In Vba
In my program I want some labels to be printed on printer P661 (black/white) and other labels on printer P656 (color).

Q: is it possible to do this in vba for my code?

The labels are made in Word and printed from within Excel.

Dim oWord As Object
Dim sPath As String
Dim iCnt As Integer

sPath = ThisWorkbook.Path & Application.PathSeparator & "NACL_LABEL.doc"

iCnt = Val(InputBox("Hoeveel exemplaren?", "NACL_LABEL", 1))

If iCnt >= 1 Then

Set oWord = CreateObject(Class:="Word.Application")

With oWord.Documents.Open(sPath)
.PrintOut Background:=False, Copies:=iCnt
.Close False
End With

oWord.Quit False
End If

Set oWord = Nothing
End Sub

The specific printers in the network are:

Color ---> "\PlantijnP656 op Ne00:"
Black/white ---> "\plantijn2000oldP661 op Ne02:"

View Replies!   View Related
Printing To A Cute Pdf Printer
how to automate the printing of Excel sheets each with a different name to a Cute pdf printer?

I have a VBA procedure which uses Windows API calls to write to a system ini file (which I understand is a necessary requirement of the Cute software), which loops through all the sheets I need to print, and sends them one by one to a Cute pdf printer. However each time the 'print' instruction is invoked - I say print but of course this is really a file save process, the Save As dialog box pops up asking the user to confirm the save.

What I want to achieve is the file gets saved automatically, with the relevant sheet name with no confimation dialog box.

View Replies!   View Related
Printer Detection Macro In VBA
i have a workbook system that is used on 25 standalone machines that all have different printers everytime i install on a new system i have to reconfigure all of the printers that i have set up in VBA i am curious to find out as to whether or not there is a macro i can use that will generate a list of all printers on a machine that i can then use to refrence a printer name in my VBA

View Replies!   View Related
Ask User To Select A Printer [{}]
I have a file for which I wish only selected pages to be printed, so I have disabled the print function in the ThisWorkbook module. Instead, the user chooses from a menu of available sheets to print, via a user form, and then requests the print.

I've got most of this sussed in my mind, but the users need the option of which printer to use (loads of networked printers available and the choice will depend upon monochrome for draft print or full colour for final print), so I want to be able to call up the printer dialog box using


I want all the options on the dialog box disabled, except for the choice of printer as clicking OK after choosing the printer adds the active sheet to the list of items to print.

View Replies!   View Related
Active Network Printer
Is there a way to test for an active default network printer?

In my module the code sets the paper size and a few other printer settings, if my default network printer is down or offline the code fails.

If I could test for active default printer I could remove this error.

View Replies!   View Related
Print Each Worksheet To Printer Of Same Name
I have a workbook with 150-some-odd sheets, each with a store number. I would like to loop through each sheet & print that sheet to the printer with the same name in my printers & faxes queue.

(eg. sheet 9604 would print to printer name oki9604)

View Replies!   View Related
VBA Code And Cutepdf Printer
I'm trying to save excel files as pdf using vba code and the cutepdf printer. If I do this manually it works, but when I write code it saves the output to a file that cannot be read or opened

Unfortunately I don't have access to the pdfdistiller so can't use this technique which I've seen described in a number of forums.

The code I've used is below.

[PHP]Sub pdfing()

Dim cntTrue As Long, cnt As Long
Dim rng As Range, bk As Workbook
Dim fName As String
Dim WB As Workbook
Dim FundName As String

Workbooks.Open ("S:SRQFSFCWNames")


FundName = ActiveCell.Value

fName = Dir("S:SRQFS" & FundName & ".xls")
Do While fName <> ""
Workbooks.Open ("S:SRQFS" & fName), UpdateLinks:=0
cnt = cnt + 1
fName = Dir()
Application. ScreenUpdating = False

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources


View Replies!   View Related
Identify Default Printer
how to identify in code which installed printer is set as the default printer? I can set/switch printers easily enough but don't seem to be able to identify the default printer as opposed to the active printer...

View Replies!   View Related
Install Network Printer Via VBA
I have a new workbook that needs to be printed to a specific printer regardless of who the user is. I am happy with the method used for doing this using the PrintOut function, but am trying to work out what to do in the event that the user does not have this printer installed. The logic, not in true VBA syntax, is as follows;

' Check Printer
If <This Printer> Is installed Then
Printout using <This Printer>
Install <This Printer>
Printout using <This Printer>

I found a useful post about ascertaining whether or not a printer is installed, but can't find anything about actually installing a printer via VBA.

View Replies!   View Related
VBA Code To Print To Specfic Printer
I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.

I have tried setting the active printer using:

Application.ActivePrinter = "\WP010110.117.3.36"

with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!

Ozgrid has been a great source of information and solutions in the past. Please help again!

View Replies!   View Related
Printing Two Sheets On Duplex Printer
Is if possible to set up two different sheets in the same workbook to print as front and back pages on a duplex printer?

View Replies!   View Related
Print Macro To Send To PDF Printer
I want to automate (with the click of a macro button, or hot key sequence) is to:
· take a pre-set print area (which is always the same – 1 page wide x 1 page tall)
· send to the printer
· select the PDF printer from the list (not the default printer for the computer)
· Automatically change the save PDF file name to “(Cell A1) – Weekly Update – (Today’s Date).pdf”
· Change location to save file on Desktop

Feels like a pretty lofty goal for a macro to do all of that – please let me know if that is even possible to set up.

View Replies!   View Related
Go Back Too Default Printer Each Time
I have my buttons working, but I need too have it return to the default printer after a button is used. If I send it too PDF it will not let me print it. Is there a way too go back too default printer each time?

View Replies!   View Related
Macro To Print To Specific Printer
I am trying to swith between different printers to print out sheets within a single workbook. Recording the macro doesn't work very well and the code I found online doesn't work either (error: object doesn't support property).

How can I set this up to be a dynamic printer setting?
Code I tried:

View Replies!   View Related
Macro Print To Default Printer
i have a macro that loads a userform for previewing and printing selected sheets. The macro works fine for previewing sheets but when i choose the print button the marco trys to save the file as a .mdi, i want this to select the default printer on the pc instead, the excel file will be onpened on many different pcs so the printers will be different on each machine, hence why i want a default printer selected. This is the section of code from the form

View Replies!   View Related
Print Sheet2 To Specific Printer
What code can I write to print Sheet1 and Sheet2 on specific Printer and Sheet 3 to another printer, then all other sheets to another printer.

View Replies!   View Related
Bring Back Complete Printer Name
create an array of active printers. I edited the code to write the array to a range, then populate a data validation list with the printer names. All works great. The goal was to select a printer name from the list and run a macro to print some sheets. However, the function doesn't bring back enough of the name to do this. how to edit the function to bring back the whole name.

For example: after running the function the 1st printer returned ="\rizzoHQ-ENG-04"...when i record a macro to print to this printer it needs ""\rizzoHQ-ENG-04 on Ne05:". Here is the function unedited, I bolded the part i think is controlling the name:

View Replies!   View Related
Print To A User's Default Printer?
Is there a Macro to automatically print the selected print area (print area is also established within the macro itself) to a user's default printer; whatever that printer may be? Note that this printer and/or printer name could change depending on the user.

I would like this to actually select and print to the default printer without Excel having to ask you. I don't even want the "OK" box to show up if possible. I just want them to run the macro, execute all it's duties, and print, all in 1 shot.

View Replies!   View Related
Print Out Spreadsheet In Dotmatrix Printer
I have developed one file for invoice printing using VBA coding.

When i take print out from excel workbook the print out is comming in Windows Font (i want like Dos prompt printing).

I want to take printout in Dot matrics printer for invoice printing and the print out has to come like Lotus123 spreadsheet print out (dos mode).

is it possible to get print out like Lotus123? If possible can u please tell me how to change the printing property in excel spreadsheet.

View Replies!   View Related
Convert Worksheets To PDF Without PDF Printer Installed
I would like to save a Sheet as a PDF, however I do not have access to install a PDF-printer service (like PDF995 etc) on all the PCs at work. I've been Google-ing for the past week, and there are a lot of options out there, however all the options I have found fall into 3 categories;

- Prohibitively epensive
- Install a Printer
- Convert text only

I need something that mimics the output of Distiller (which I have at home) as either an excel addin or a stand-alone program which I can run via Shell. (I should be able to cope with the VBA for this : D) I don't need charts etc printed, however I do need a simple graphic (logo) to be included.

View Replies!   View Related
Reset Default Printer Settings In Vba
We have printer mailboxes setup for all of the employees and sometimes the print settings get saved in the excel files. This means that when another employee uses the file their prints get sent to the wrong mailbox. We have hundreds of files like this and it causes all kinds of problems with prints going to wrong mailboxes!
Obviously their default print settings are all set to their own mailboxes.

Is there some way, using vba, to get an excel file to go back to using the user's default printer settings instead of the ones saved in it's file? (or get it to remove the printer settings saved in it's file). That way I could just make an application level open event to fix the problem! Either that or some way, in vba, of changing a user's storage box number for a specific printer.

View Replies!   View Related
Hp Printer Prints Blank Pages
When printing an Excel workbook/Worksheet that has been emailed via Lotus notes 7.02 the print job is all blank pages - PrintPreview is normal and correct - Page Breaks are set correctly - The correct number of pages are printed but, ALL Pages are blank. The printer is an HP 6310 All-in-One connected locally via USB. If Printed to Adobe, ALL pages are correct and can be printed on the HP 6310. If another printer is selected (ie: Any Network Printer) the results are the same. This occurrs only on certain spreadsheats received from only a few users.

View Replies!   View Related
Print Macro: The Data To Be Printed To Another Printer
I have the following code to print a selection of data which is fine but i need to have in the code a different printer. So, for example my default printer needs to stay the same but when this spreadsheet is open i need the data to be printed to another printer.

View Replies!   View Related
Print Labels To Continuous Roll Printer
We have a an existing spreadsheet which uses a query from Excel to Sage Line50 Accounting software to create labels for stock items. The number of times a label is printed is dictated by the number of items that are required for a particular sales order.

For example -
1006abc 100 No.
2003abc 20 No.

The program would produce 100 labels for item code 1006abc, and would produce 20 labels for item number 2003abc.

However we have recently acquired a heavy duty industrial printer which prints to labels on a continuous roll and I am unable to alter the spreadsheet to work with the new printer layout.

I have attached the spreadsheet and, I hope, the VBA code. PLease also see this link to my previous post - Print Labels Dependant on Content

View Replies!   View Related
VBA Print Preview Code Causes Crash If No Printer Installed
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?

View Replies!   View Related
Network Printer 'ActivePrinter' Of Object'_Application' Failed
I am having trouble printing a page to both a local printer (which works fine) and a network printer (which does not work) Both printers are installed on the Computers. The error message I get when I try to run the macro is Run-Time ERror '1004': 'ActivePrinter' of object'_Application' failed. The codes that I have used are

Application.ActivePrinter = _
"\SAUWTPFS01.sopac.pilkington.netDespatch Toughend & Batch reports (L164) on Ne05:"


Application.ActivePrinter = _
" \sauwtpfs01pauwtp0305 on Ne05:"


Application.ActivePrinter = _
" \sauwtpfs01pauwtp0305"

It works on the day I record the macro. As soon as the next day comes around I get the error message. When I click debug it is these lines that are highlighted in yellow. I have looked at this for several days on and off and I always get the same error.

View Replies!   View Related
Set Properties Value
storedPath = .CustomDocumentProperties("PathCertString").Value

Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.

The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?

View Replies!   View Related
Tab Properties
Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):

Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False

But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.

How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?

View Replies!   View Related
PivatTable Properties
I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:

This is a example table:

Sum of store_sales time_id product_id store_id 367 368 369 1 3 6 7 11,4 11 13 14

(the format is not real clear, but I will explain)

This is a part of a PivotTable where:
Sum of Store_sales is located in the datafield(one record; 11,4)
product_id and store_id are Row-Items
Time_id is a column item.

Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.

Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1)
Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1)
Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2)
But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.

Is someone able to give me a hint? Is there a method for this in VBA?

View Replies!   View Related
Offset And End Properties
I have attach the lab2.xls files below.

1. Download “ Range Data.xls”.

2. Use the Offset and End properties of Range object to name range from A2 to the end of the column as “NEmployees”, range B1 to the end of the row as “NScores”,and the rest of the range, B2 to F19, as “ScoreData”.

3. Do some formatting using the range names and the With-End With construction: make the font of the NEmployees Range in bold and blue color; change the font of the NScores Range to italic, in red and centralize the text (using the HorizontalAlignment property).

View Replies!   View Related
GetOpenFilename Properties
Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:

View Replies!   View Related
Message Box Properties
message box properties. i m using this

View Replies!   View Related
Add Properties Details
I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.

View Replies!   View Related
Cell Properties
where I can find a comprehensive list of '. Cells()' properties that I can Test for/Apply to Excel Cells?


I want to set Conditional Formatting using VBA,
to test for:

Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)

from an imported file.

View Replies!   View Related
Properties Of A Worksheet
I have a workbook with 10 worksheets and I need to know the memory size for each worksheet. I know from File/ Properties that the file is 3.7mb but that is much higher than I would have expected. I can't tell which worksheets are causing it to be so large.

View Replies!   View Related
VBA Read/write .MP3 & .WMA Properties()
I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-

1. READ : Run one macro to put data into a worksheet.....

2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....

3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.

Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.

Open "c:myfile.mp3" For Binary As #1

etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.


View Replies!   View Related
Mimic The Properties Userform
Is it possible to create a userform that mimics the Properties userform using VBA by itself. I'm not sure what the form style would be called otherwise I'd have googled it; but it looks pretty cool.

View Replies!   View Related
Customized Protection Properties
I need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.

View Replies!   View Related
Control Similar To The Properties One In The VBE
I am after a control similar to the Properties one in the VBE. The one where you modify properties for a control in design mode.

Does anybody know of a suitable Control that I can use in my project that has similar usage?

View Replies!   View Related
Properties :: Difference In Size
In the properties in one of my wb's the Size measures to 1,40 MB, but the Size on disk only measure 408 KB. If I make a copy of the same wb, the copy displays 1, 40 on both.

Why do I have a different size on the original?

View Replies!   View Related
Arrays As Object Properties--VBA
After much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.

I try the following ....

View Replies!   View Related
Set Worksheet Button Properties
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).

View Replies!   View Related
Combobox - Properties - Listfillrange
When in a Combobox from the Control toolbox toolbar, the data for the Listfillrange are in a column (e.g.: myrange = $A:$A), it works fine. But when the data in myrange are in a row (eg.: myrange is =$1:$1) , the combobox shows nothing!. What can I do to show the combobox all data (A1,B1,C1,D1 - last data?) ?

View Replies!   View Related
Copyright © 2005-08, All rights reserved