This is a guest post by Daniel Ferry of Excelhero.com.
Have you ever wanted to fetch live stock quotes from excel? In this post we will learn about how to get stock quotes for specified symbols using macros.
One method that has worked well for my clients can be implemented with just a few lines of VBA code. I call it the ActiveRange.
An ActiveRange is an area on a worksheet that you define by simply entering the range address in a configuration sheet. Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update. You can specify any of 84 information attributes to include as columns in the ActiveRange. This includes things such as Last Trade Price, EBITDA, Ask, Bid, P/E Ratio, etc. Whenever you add or change one of these attributes in the first row of the ActiveRange, the range will automatically update as well.
Sound interesting, useful?
In this post, you can learn how to use excel macros to fetch live stock quotes from Yahoo! Finance website. It is also going to be a crash course in VBA for the express purpose of learning how the ActiveRange method works so that you can use it yourself.
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Background – Understanding The Stock Quotes Problem:
The stock information for the ActiveRange will come from Yahoo Finance. A number of years ago, Yahoo created a useful interface to their stock data that allows anyone at anytime to enter a URL into a web browser and receive a CSV file containing current data on the stocks specified in the URL. That’s neat and simple.
But it gets a little more complicated when you get down to specifying which attributes you want to retrieve [information here]. Remember there are 84 discreet attributes available. Under the Yahoo system, each attribute has a short string Tag Code. All we need to do is to concatenate the string codes for each attribute we want and add the resulting string to the URL. We then need to figure out what to do with the CSV file that comes back.
Our VBA will take care of that and manage the ActiveRange. Excel includes the QueryTable as one of its core objects, and it is fully addressable from VBA. We will utilize it to retrieve the data we want and to write those data to the ActiveRange.
Before we start the coding we need to include two support sheets for the ActiveRange. The first is called “YF_Attribs”, and as the name implies is a list of the 84 attributes available on Yahoo Finance along with their Yahoo Finance Tag Codes. The second sheet is called, “arConfig_xxxx” where xxxx is the name of our sheet where the ActiveRange will reside. It contains some configurable information about the ActiveRange which our VBA will use.
All of the VBA code for this project will reside inside of the worksheet module for the sheet where we want our ActiveRange to be. For this tutorial, I called the sheet, “DEMO”.
Writing the Macros to Fetch Stock Quotes:
Press ALT-F11 on your keyboard, which will open the VBE. Double click on the DEMO sheet in the left pane. We will enter out code on the right. To begin with, enter these lines:
Private rnAR_Dest As Range
Private rnAR_Table As Range
Private stAR_ConfigSheetName As String
Always start a module with Option Explicit. It forces you to define your variable types, and will save you untold grief at debugging time. In VBA each variable can be one of a number of variable types, such as a Long or a String or a Double or a Range, etc. For right now, don’t worry too much about this – just follow along.
Sidebar on Variable Naming Conventions
Variable names must begin with a letter. Everyone and their brother seems to have a different method for naming variables. I like to prefix mine with context. The first couple of letters are in lower case and represent the type of the variable. This allows me to look at the variable anywhere it’s used and immediately know its type. In this project I’ve also prefaced the variables with “AR_” so that I know the variable is related to the ActiveRange implementation. In larger projects this would be useful. After the underscore, I include a description of what the variable is used for. That’s my method.
In the above code we have defined three variables and their types. Since these are defined at the top of a worksheet module, they will be available to each procedure that we define in this module. This is known as scope. In VBA, variables can have scope restricted to a procedure, to a module (as we have done above), or they can be global in scope and hence available to the entire program, regardless of module. Again we are putting all of the code for this project in the code module of the DEMO worksheet. Every worksheet has a code module. Code modules can also be added to a workbook that are not associated with any worksheet. UserForms can be added and they have code modules as well. Finally, a special type of code module, called a class module, can also be added. Any global variables would be available to procedures in all of these. However, it is good practice to always limit the scope of your variables to the level where you need them.
In that vein, notice that the three variables above are defined with the word Private. This specifically restricts their scope to this module.
Every worksheet module has the built-in capability of firing off a bit of code in response to a change in any of the sheet’s cell values. This is called the Worksheet_Change event. If we select Worksheet from the combo box at the top and Change in the other combo box, the VBE will kindly define for us a new procedure in this module. It will look like this:
End Sub
Notice that by default this procedure is defined as Private. This is good and as a result the procedure will not show up as a macro. Notice the word Target near the end of the first line. This represents the range that has been changed. Place code between these two lines so that the entire procedure now looks like this:
The Heart of our Excel Stock Quotes Code – Worksheet_Change()
ActivateRange
If Worksheets(stAR_ConfigSheetName).[ar_enabled] Then
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
If Target.Column <> rnAR_Dest.Column And Target.Row <> rnAR_Dest.Row Then
PostProcessActiveRange
Exit Sub
End If
ActiveRangeResponse
End If
End Sub
That may look like a handful but it’s really rather simple. Let’s step through it. The first line is ActivateRange. This is the name of another sub-procedure that will be defined in a moment. This line just directs the program to run that sub, which provides values to the three variables we defined at the top. Again, since those variables were defined at the top of the module, their values will be available to all procedures in the module. The ActivateRange procedure gives them values.
Next we see this odd looking fellow:
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
All this does is check to see if the Target (the cell that was changed on the worksheet) is part of our ActiveRange. If it is the procedure continues. If it’s not, the procedure is exited.
The next line checks to see if the cell that was changed is in the first column or first row of the ActiveRange. If it is, the post processing is skipped. If the change is any other part of the ActiveRange, another sub-procedure (defined below) is run to do some post processing of the retrieved data, and then exits this procedure.
If the cell that changed was in the first column or the first row, the program runs another sub-procedure, called ActiveRangeResponse, which is also defined below. ActiveRangeResponse builds the URL for YF, deletes any previous QueryTables related to the ActiveRange, and creates a new QueryTable as specified in our configuration sheet.
That’s it. The heart of the whole program resides here in the Worksheet_Change event procedure. It relies on a number of other subprocedures, but this is the whole program. When a change is made in the ActiveRange’s first column (stock symbols) or its first row (stock attributes), ActiveRangeResponse runs and our ActiveRange is updated.
Understanding other sub-procedures that help us get the stock quotes:
So let’s look at those supporting subprocedures. The first is ActivateRange:
stAR_ConfigSheetName = “arConfig_” & Me.Name
Set rnAR_Dest = Me.Range(Worksheets(stAR_ConfigSheetName).[ar_range].Value)
Set rnAR_Table = rnAR_Dest.Resize(1, 1).Offset(1, 1)
Worksheets(stAR_ConfigSheetName).[ar_YFAttributes] = GetCurrentYahooFinancialAttributeTags
End Sub
Again, all this does is give values to our three module level variables. In addition it builds the concatenated string of YF Tag Codes required for the URL. It does this by calling a function that I’ve defined at the very bottom of the module, called GetCurrentYahooFinancialAttributeTags.
The next subprocedure is ActiveRangeResponse:
Dim vArr As Variant
Dim stCnx As String
Const YAHOO_FINANCE_URL = “http://finance.yahoo.com/d/quotes.csv?s=[SYMBOLS]&f=[ATTRIBUTES]”
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count – 1, 1).Offset(1))
stCnx = Replace(YAHOO_FINANCE_URL, “[SYMBOLS]”, Replace(WorksheetFunction.Trim(Join(vArr)), ” “, “+”))
stCnx = Replace(stCnx, “[ATTRIBUTES]”, Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
AddQueryTable rnAR_Table.Resize(UBound(vArr)), “URL;” & stCnx
End Sub
Notice that here we have variables defined at the top of this procedure and consequently their scope is limited to this procedure only. This means that we could have the same variable names defined in other procedures but those variables would not be related to these and would have completely different values.
Next notice that we have defined a constant. This is good practice, as it forces us to specify what the constant value is by naming the constant. I could have just used the value where I later use the constant, but then the question arises as to what is this value and where did it come from. Here I have named the value, YAHOO_FINANCE_URL, removing all doubt as to its purpose.
The next line is this:
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count - 1, 1).Offset(1))
and it deserves some explanation. Let me back up by saying that whenever we write or read multiple cells from a worksheet we should always try to do it in one go, rather than one cell at a time. The more cells involved the more important this is. Otherwise we pay a massive penalty in processing time. One of the best optimization techniques available is to replace code that loops through cell reads/writes and replace it with code that reads/writes all the cells at once. It can literally be hundreds to thousands of times faster.
Here we are interested in getting the list of all of the stock symbols in the first column of the ActiveRange. So how do we get them in one shot? We use something called a variant array. Notice that we defined vArr at the top of this procedure. A variant array is a special kind of variable that holds a list of values and it DOES NOT CARE what variable types those values are. This is important when retrieving data from a sheet because the data could be numbers, text, Boolean (True or False), etc. Variants are powerful, but they are much slower than other variable types, such as a Long for numeric data for example. However, in the case of retrieving or writing large chunks of data from/to a sheet the slight penalty of the variant is dwarfed by the massive increase in the speed of data transfer.
It’s very simple to retrieve range data (regardless of the size) into a variant array. All you do is:
v = range
where v is defined as a variant and range is any VBA reference to a worksheet range. And magically all of the values in that range are now in v. Note that v is not connected to the range. A change in any of v’s values does not propogate back to the range, and likewise a change to the range does not make it’s way to v all by itself. v will ALWAYS be a two-demensional array. The first dimension is the index of the rows, the second dimension is the index of the columns. So v(1,1) will refer to the value that came from the top left cell in the range. v(6,9) will hold the value that came from the cell in the range at row 6 and column 9.
For most circumstances this two-dimensional format is fine. But we are only retrieving one column of stock symbols. The procedure will still give us a two-dimensional array, with the column dimension being only 1 element wide. This is a shame because VBA has a wonderful function called Join that allows you in one step (no loop) to concatenate every element of an array into a string. You can even specify a custom string to delimit (go in-between) each element in the output string. The problem is that Join only works on single dimensioned arrays 🙁
But there’s always a way, right? We can use the Application.Transpose method on the 2-D array and presto we get a 1-D array. The rest of the line just specifies what range (the stock symbols) to grab.
The next two lines are:
stCnx = Replace(YAHOO_FINANCE_URL, "[SYMBOLS]", Replace(WorksheetFunction.Trim(Join(vArr)), " ", "+"))
stCnx = Replace(stCnx, "[ATTRIBUTES]", Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
Again a handful, but all we are doing here is replacing the monikers, [SYMBOLS] and [ATTRIBUTES] in the YAHOO_FINANCE_URL constant with the list of stock symbols (delimited by a plus sign) and the string of attributes.
In the final line of the procedure:
AddQueryTable rnAR_Table.Resize(UBound(vArr)), "URL;" & stCnx
we are running another subprocedure called, AddQueryTable and we are telling it where to place the new QueryTable and providing the connection string for the QueryTable, which in this case is the YF URL that we just built.
Nothing unusual happens in the AddQueryTable sub. It just deletes any existing AR related QueryTables and adds the new one according to the options in the configuration sheet.
The PostProcessActiveRange sub is interesting:
If rnAR_Dest.Columns.Count > 2 Then
Application.DisplayAlerts = False
rnAR_Table.Resize(rnAR_Dest.Rows.Count).TextToColumns Destination:=rnAR_Table, DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Worksheets(stAR_ConfigSheetName).[ar_LocalTimeLastUpdate] = Now
End If
End Sub
Processing Yahoo Finance Output using Query Table & Text-Import Utility:
As mentioned before the data from YF comes back as a CSV file. The QueryTable dumps this into one column. If you were only retrieving one attribute for each stock this would be fine as is. However, two or more attributes is going to result in unwanted commas and multiple attribute values squished into the first column of the QueryTable output. Unfortunately this is poor design by Microsoft, especially when you consider that the QueryTable does not behave like this when it is retrieving SQL data or opening a Text file from disk. You can actually specify this operation to be a text file and it will properly spread the output over all of the columns. To do so, you specify the disk location as being the URL of the YF CSV file, but as Murphy would have it, it’s unbelievably slow and pops up a status dialog as it slowly retrieving the CSV. Using the URL instruction instead of the TEXT instruction at the beginning of the connection string is incredibly fast in comparison, but dumps all of the data into the first column.
So what to do? We’ll just employ Excel’s built-in TextToColumns capability and bam, our data is where we want it.
Our finalized stock quotes fetcher worksheet should look like this:
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Final Thoughts on Excel Stock Quotes
The ActiveRange technique is quite versatile. It can be implemented with other data sources such as SQL, or even lookups to other Excel files, or websites.
In this example it provides a nice way to easily track whatever stocks you may have interest in and up to 84 different attributes of those stocks. You can enable and disable the activeness of the ActiveRange on the fly. You can set the AR to AutoRefresh the data at periods that you set or to not refresh at all.
This is a basic implementation. For example, changing the AutoRefresh setting will have no effect until a new QueryTable is built. That won’t happen until you also add or change a stock symbol or add or change an attribute. An easy enhancement would be to add a little code to the arConfig_DEMO code module to respond to changes to the ar_AutoRefresh named range cell.
Another enhancement would be to eliminate the slight flicker of the update by moving the QueryTable destination to the arConfig_DEMO and then doing the TextToColumns with the destination set to the DEMO sheet. In an effort to simplify this tutorial I have left these easy enhancements as an exercise for you to implement.
Have a question or doubt? Please Ask
Do you have any questions or doubts on the above technique? Have you used ActiveRange or similar implementations earlier? What is your experience? Please share your thoughts / questions using comments.
I read Chandoo.org regularly and will be monitoring the post for questions. But you can also reach me at my blog:
Further References & Help on Excel Stock Quotes [Added by Chandoo]
- Fetching Stock Quotes using Research Pane
- Stock Portfolio Tracker using Google Docs
- QueryTable Object Model & Properties
- Using QueryTable to Generate Dynamic Reports
- Yahoo Finance API Documentation & Example
Excel Hero is dedicated to expanding your notion of what is possible in MS Excel and to inspiring you to become an Excel Hero at your workplace. It has many articles and sample workbooks on advanced Excel development and advanced Excel charting.
89 Responses to “Hui’s Excel Report Printer”
Woa!
This is a really impressive macro, I will probably use it in one of my KPI Dashboard I'm currently building in my company
Many, many thanks to you Hui. You're a invaluable contributor !!! (have you ever think of a fork of Chandoo.org ??? 🙂
We have a colour printer at work which defaults to B&W (company policy), however, changing this from Excel would be handy, but I suspect this aspect of the print setup is outside of the Excel object...
this comprehensive tutorial is so unexpectedly wonderful. thank you. i particularly appreciated the insight into the use of "With ActiveSheet.PageSetup" and being able to pick up anything required for footers and headers. this will save me me a ton of time. thank you.
you could copy this formula down the rows in column "O":
=IF(D5="Off","-","Page: "&COUNTIFS($D$5:D5,"On")&" of "&COUNTIFS($D$5:$D$14,"On")&" -Print date: "&TEXT(NOW(),"mmm dd, yyyy")&" Copyright 2011 - Hui Corporation")
and then reference the appropriate cell in the VBA call. This formual does not take into consideration of "pages" more than 1 page wide or 1 page tall. converting more of this formula to VBA and making the formula in column "O" a simple count of pages could probably take care of that. probably doing all the math in VBA is even better. i do not know how to write this formula in VBA.
also, Hui, do you know of links to more information about pdf's in general? i find myself wanting to print a package of reports into the same pdf file from Excel. i can't figure out how to do that. at times. i want to take a pdf file and add the pages to a Word document (images are fine as long as there is some automated way to split the pdf into Word document "pages"). sometimes i just want to add a few of the pages of a pdf (as images) to a Word document. as far as i can tell this can't be done.
Chandoo and Hui: as we say in the US: Providence! I was just today sitting down to start creating a excel report writer and lo behold, what pops into my Inbox? A beautiul Excel report writer! Again, Providence!
Thanks so much, this is super sweet and tasty.
Super sweet and tasty! A real time saver. thanks so much
This is very cool Hui.
For me the fact that many "non Excel" users can't understand why a report may print, but the graphs don't print at the same time or why just the graph prints without the data tables etc, also the fact that each work sheet may have separate print formatting.
It is a real confidence builder when people can use your files without having little issues or bugs that even printing a file can create for non frequent Excel users.
It allows quick, easy & user friendly use of your files.
Thanks
Hey, could Hui or someone else give a short description of exactly what steps to follow to use this in a different workbook.Seems really useful but I'm not able to use it by just copying over the the sheet Print_Control.What do i need to add or replace to the code?
If you've copied the sheet to a new workbook
Did you select the small button described in the instructions above?
.
If it didnt work keep reading below
.
Run tha VBA subroutine called
Setup_Print_Control_Named_Formula
.
or
.
Right click on the small button
Assign Macro
Select Setup_Print_Control_Named_Formula
Apply
.
or
.
Manually add the two named formulas described in the post above
neat. another thing as i was looking at the page size comment is that you could have drop down lists in various places that would limit choices
On/Off; Landscape/Portrait; Page Size: Letter/Legal/etc. and Rows: 1-8
@All, Thanx for the appreciative comments
@Rich, I'm a minimalist, So don't like controls, but you can add them if you desire.
@Bill, I do have a version that does allow you to include auto-incrementing page numbers into comments using a code #Pg#, But I stripped it out of this to keep it simple and functional and just demonstrate the techniques.
@Bill, in VBA you can use
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF
to print a package of Excel reports into one PDF (as long as all the reports are in the same Excel workbook).
Please help me also.... i need to put page # of # in printing excel...
i already has a code
Sub Button2_Click()
ActiveSheet.Range("A1:K45").Select
ActiveSheet.PageSetup.Orientation = xlLandscape
Selection.PrintOut Copies:=1, Collate:=True
MsgBox "Successful!"
End Sub
Hi Simon... where in the current macro would you include this code to combine the pages into one report?
“=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))” piece has a problem.
In Russian locale formula parameters need to be separated by semicolons. So if the code throws error in Setup_Print_Control_Named_Formula subroutine, you would want to change commas to semicolons.
Pls discard my previous comment. Not true.
Great tool. Appears that the VBA code currently limits the number of worksheets it will print to 20 (sheets listed on rows 5 to 24 (Excel 2010 version). How do I modify so it will print more than 20? Also, it you have a version that allows for more flexibility with headers and footers, I would appreciate seeing it. One last item - are paper margins set in the normal Excel fashion or somewhere in the Print_Control worksheet? Again, wonderful tool - just made my life much easier - thanks.
[...] Automatically Generate Report Variations using Excel [...]
I have a workbook that includes 18 sheets with 160 named ranges. My desire is to print to a pdf with each range showing up on it's own sheet. I inserted your sheet into my workbook, removed your information and inserted mine pertaining to the workbook. Some of my named ranges contain graphs, by the way, and I still included them. When clicking the "Setup Print Control" button, I get the message that because of my security settings macros have been disabled. I get the same message when clicking the "Print all on areas" button. I saved and reopened the file, enabling macros and still get the error message.
I don't use macros very much, and your VBA code discussion is greek to me. I saw the one comment about using "ActiveWorkbook.ExportasFixedFormat Type-xltype.pdf" and will try to incorporate that - if I can get this macro to run. Any suggestions? Thank you - PS - does your hair really look like that?
Hi Hui,
This is great. I am amending the code to add more page setup parameters and also to automatically populate the "visible" worksheets. When done, I shall post a link back at this thread for others. Thanks to you for this and it is indeed saving on tons of hours.
I have retained the credit to you in the code.
Regards,
Ninad.
This macro works for Excel 2011 for MAC, with the following caveats:
In the print macro:
The EvenOddHeaderFooter errored out for me, deleting those lines (numerous) caused the rest of macro to work perfectly
in the SetupPrintControl macro, the lines attempting to "insert a comment" error out. Deleting those permitted the macro to work as intended.
(i'm not familiar with VBS, so others feel free to correct anything unclear, or erroneous)
@Bret
Thanx for the feedback
I don't have access to Excel 2011 Mac to test these type of things
Unfortunately MS keep adding features to different versions which can result in small inconsistencies between versions
I'm glad you worked it out in this case
I thought about you and this blog post last night! I was tinkering around with linked images (via the Camera) when it occurred to me that they could be used to give the user a "print preview" of another spreadsheet. Since this spreadsheet program you've posted allows the user to define a print area - do you think a linked image (ie a "print preview" window) that automatically updates based on the print area supplied is useful feature? or, do you think it might just get in the way?
As you say This doesn’t fix the printing multiple pages to multiple files when printing to PDF issue
I wonder if I could ask how ou do overcome this problesm. Is it caused ourely and simply by having different formatting/page setup settings oer worksheet?
This is not working for me when I got to install it. I have may worksheets.
Any advice?
@John
What version of Excel are you using ?
Can you email me your 2 files
Click on Hui... above, email at bottom of page
Hello Hui,
Been looking forward using your print-areas macro, but I encounter a problem; When I open the 'Print-Areas.xlsm' file and then hit the 'Setup Print Control Named Formula' I get an error (visual basic 400 error). I looked at the code but can not solve it. I tried it on another PC as well, but the same error.
As all people above do have it working, can it be a version problem of my software? I use Windows 7 and Excel 2010 professional Plus.
Hope you can help me, the print feature will save me lots of work printing all the different pages in my excel file.
Thanks,
Marc.
@Marc
can you email me the file
Click on Hui...
email add at bottom of page
Hui,
This sounds almost to good to be true but I am having the same 400 error. Sadly I cannot upload the file (company policy) can you still help me?
Also I was wondering if I can define multiple print areas on the same sheet with this.
Many thanks in advance!
@Daniel
The macro only sets up 2 named Formula
These need to be
Copies: =Print_Control!$M$26
ie: The Number of Copies cell
Print_Control: =OFFSET(Print_Control!$B$4,1,,COUNTA(Print_Control!$B$5:$B$24),COUNTA(Print_Control!$4:$4))
This is the range from B5 to the lower right corner of the populated data area
in the download file it is N14
If these named Formula exist there is no need to run the "Setup print controls named Ranges" macro
"Can define multiple print areas on the same sheet with this" - Absolutely
Including collapsed or expanded groups
This is all described in the post.
BTW I am using Excel 2013 if that changes anything
nice code very halpful
thanks
Hi All,
Using the 2003 version i receive a VB error, "Object doesn't support this property or method". If I rem out the following lines of the Setup_Print_Control_Named_Formula sub routine it will run but does nothing:
'ActiveWorkbook.Names("Print_Control").Comment = _
' "Used by the Print_Reports Subroutine"
'ActiveWorkbook.Names("Copies").Comment = _
' "Specifies the No. of Copies for the Print_Reports Subroutine"
Not good with VBA so any ideas would be appreciated.
Printing side of things is working fine if I manually enter the sheet names.
Cheers,
Brad
@Brad
Those two lines only add comments to two Named Ranges and so aren't required
The macro adds two Named Ranges
If they are there the main macro will now run ok
If it doesn't can you email me and I'll check
@Hui
Thanks for the fast reply mate, yeah the named ranges are there. A bit of confusion by me, I thought it populated the table with the sheet names automatically. Pretty sure I have a code snipet around that can do that for me though 😉
Cheers,
Brad
@Brad
You might also want to have a look at the last few comments in:
http://chandoo.org/forums/topic/print-macro-for-selected-sheets-from-200-worksheets
This builds the list automatically, but allows less control and only uses the existing Print Area on each page
Hi All,
I have added a button and the following macro to populate the sheet names on the Print_Control table............ However it runs backwards, so it pulls the last sheet name and adds it to the first slot of the table.
Anyone know how to reverse the order in which it pulls the sheet names?
Cheers,
Brad
CODE:
Sub PopSheets()'Populates Print_Control with workbook sheet names
Dim Counti
Dim SheetName As Variant
Dim Cell
Dim i As Integer
Dim r
r = "E"
i = 3 + 1
Counti = 1
Worksheets("Print_Control").Range("E5:E24") = ""
Application.DisplayAlerts = False
For Counti = Sheets.Count To 1 Step -1
If Sheets(Counti).Name "" And Sheets(Counti).Name "Print_Control" Then
SheetName = Sheets(Counti).Name
Worksheets("Print_Control").Activate
With ActiveSheet
i = i + 1
Cell = r & i
ActiveSheet.Range(Cell) = SheetName
End With
End If
Next
Application.DisplayAlerts = True
End Sub
@Brad
Change the line
For Counti = Sheets.Count To 1 Step -1
to
For Counti = 1 to Sheets.Count
Please let me know how to use it since it returns a syntax error whenever i copy this code in the project & run it
@Rajeev
It relies on a few named formula being setup
Make sure they are in place and point to the correct ranges
@Hui
Sorry, I didn't see your post before I put up my code. Thanks for the assist though 😉
Geez I should have seen that, as soon as i read your reply it clicked.
Cheers,
Brad
Hello Hui & Chandoo, it is wonderfull, amazing. I was just trying to find out a way for printing solution like this. It is really interesting, but you have contributed with your very hard work and dedicative effort. I thank you so much for sharing this one.
With warm regards,
Mani
@Mani
I hope you also have a look at my solutions to printing many sheets at this post:
http://forum.chandoo.org/threads/print-macro-for-selected-sheets-from-200-worksheets.5795/
Hi All,
I am using Excel 2010 and need the Page & P of & N to show in footer, where P and N changed based on the number of pages turned on? Also my print areas are variable so the number of pages printed for each sheet is not consistent.
Any help is appreciated.
Thanks sir This Is very Help full but i want to ask one thing suppose i Have an Excel worksheet C08 long than how can i print that sheet in one page or ..can i print long excel sheet one by one on different Pages..an excel Sheet having 30 wide Length than can i print 6 column in one page and 6 another page automatically..
Thanks
Rahul Kumar
@Rahul
Can you please send me a copy of the file with specific instructions so I can review
ihuitson at gmail dot com
I just created 100 pages of daily lesson plans for my son's homeschooling this year using your routine.
Thank you Jesus!
I've never been comfortable with excel cause it's math-we're not a very good mix. But I am learning a lot from your newsletters, thank you. I have a workbook with 10 worksheets that I tried this formula on. It does work but it prints out 2 pages onto one. I also can't figure out how to make it print with different headings, ie: sheet b, section one is cars, sheet b section two is trucks. each has to include the headings but print out on separate sheets. Maybe this isn't the formula to use?
I'm curious as to whether the whole printing multiple pages to multiple files when printing to pdf thing ever got a work around? Is there a way to have all of them automatically save in numerical order to a specified folder? That way you could just ctrl - a and put them in a pdf merger and do it quickly or do I actually have to sit there and name each pdf manually for them to save? I have a 70 sheet monthly board package that has to be ordered in certain ways for sum(start:ends) - index match add ups for the 6 companies bogs the speed down too much. The on/off report is great for when I print, but ultimately I need that fresh pdf.
Thanks,
Carolyn
@Carolyn
It would be fairly simple to change the code to save each sheet to a Sequentially numbered PDF file
Give me a few minutes
awesome! thank you!
Hello,
As Carolyn, I too want to send all the ("ON" status) worksheets to a .pdf file but I want them in one file.
In other words, I want to run your macro and end up with all the same sheets in a single .pdf file rather than printed.
Any insights would be appreciated.
Regards,
PS. Thank you for such a useful macro!
Hi Hui,
Sign me up for this solution...this would save a good amount of time in consolidating a monthly 50 pg pdf report that we generate. Could you send me the solution you came up with for Carolyn?
Much thanks!
Ben
thank you SO MUCH - - so kind of you to share this with us, and it's SO HELPFUL!!!!!
@David
Delete that line and the next one
i get an error when trying to run that says:
ActiveWorkbook.Names("Print_Control").Comment = _
Any thoughts?
Hello Hui. Looking to set up just the same thing and found your post. Cant quite get the Print_Range macro to work. When I copy this tab from your demo into my workbook when I first ran, it opened your original workbook again. No matter how I try I just can't seem to save the VBA into my local workbook. Now after trying for a while I get the VBA 400 error code that some others describe. I must be doing something really silly...
Any help welcomed
@Chris
Can you either email me the file or post it in the forums
http://chandoo.org/forum/
Hui thanks for your help and looking at the file. Managed to figure thesis the end. Again many thanks for all the excellent posts and help you supply.
KR. Chris
Please help me also…. i need to put page # of # in printing excel…
i already has a code
Sub Button2_Click()
ActiveSheet.Range(“A1:K45?).Select
ActiveSheet.PageSetup.Orientation = xlLandscape
Selection.PrintOut Copies:=1, Collate:=True
MsgBox “Successful!”
End Sub
Dear,
from the following code, I want 2 copies of "PROFORMA A" and 4 copies of "FORM NO. 16" to print. So would you modify the following vb code and send me to my mail id i.e. bhaiswarpravin@gmail.com
please do it for me......I shall be very thankful to you.
Sub print_sheet()
'
' print_sheet Macro
'
'
Sheets(Array("PROFORMA A", "FORM NO 16")).Select
Sheets("PROFORMA A").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Hui -
This is great stuff. One question, what about fitting the print area to size of the paper? I am printing legal, letter, legal. They are printing on the correct paper types, but the data is not adjusting.
Hello. Thank you so much for an awesome post and routine!
I would love it if you could please send me the solution you did for Carolyn to print multiple pages to a single PDF. I have a large workbook and need to print several pages as one print job to both a printer and PDF.
It seems from reading the VBA code that there could be the case where another's print job can get pages inserted since your printing is done with a loop. Is that true?
I had read that by using the Sheets.Printout method multiple sheets are sent out as one job, however I have the problem that it only uses the first sheet’s layout, zoom, etc for all the other sheets and ignores the printer settings manually put on. I really need to meet both requirements: one print job, multiple orientation, zoom, etc. Do you think this is possible? I appreciate so much your comments. Thank you!
Hello, i have a range and i named as Front_PrintArea and another range named Back_PrintArea. Those areas are in the same sheet of excel. Can i have in first area 75% scale for printing and in second 55%?
@Nick
Yes
But you will need to add another column to the Table with Print Size
The adjust the code accordingly
Please can you help me with this? I record a macro but isnt too clear for me..
Sub PrintDouplexPages()
'
' PrintDouplexPages ?a???e?t???
'
'
Range("B2:AF44").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$44,$AH$2:$AR$64"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.078740157480315)
.RightMargin = Application.InchesToPoints(0.078740157480315)
.TopMargin = Application.InchesToPoints(0.21)
.BottomMargin = Application.InchesToPoints(0.15)
.HeaderMargin = Application.InchesToPoints(0.07)
.FooterMargin = Application.InchesToPoints(0.11)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = True
.Zoom = 75
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Range("AH2:AR64").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$AF$44,$AH$2:$AR$64"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.078740157480315)
.RightMargin = Application.InchesToPoints(0.078740157480315)
.TopMargin = Application.InchesToPoints(0.21)
.BottomMargin = Application.InchesToPoints(0.15)
.HeaderMargin = Application.InchesToPoints(0.07)
.FooterMargin = Application.InchesToPoints(0.11)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlOverThenDown
.BlackAndWhite = True
.Zoom = 55
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = False
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
End Sub
I dont know if i was clear for what i want so, in the same sheet i have the range B2:AF44 and i named this range "Front_PrintArea" and the range AF2:AR59 i named "Back_PrintArea". Can i have for Front 75% scale and for Back 55% scale? Because i want to print with duplex mode.
Thanks
@nick
Can you email Me the file
Ok just one moment please to delete sensitive data.. Thanks in advance
For some reason whenever I use this when it goes to print anything beyond page 2 I get a VBA error 400 warning. Any thoughts as to why?
Thanks for any help you can provide. This is going to be incredibly helpful when it gets up and running.
Thanks.
Hi Hui. Late to the party, but just wanted to say This is cool!.
Funnily enough I arrived here after finding the code in a spreadsheet I'm helping someone troubleshoot, and the previous owner of the sheet had removed your name from the comments at the top and inserted his own. I googled some of the code, which led me here.
Sooo busted!
Cheers, Jeff
Damn,
No wonder my Royalty Cheques are so low
They sent you a royalty check?!?
Sadly No...
This is so very wonderful. The only request is to make it compatible with duplex printing.
Thanks Hui
Hi
I'm also getting the VB 400 error. I am running Excel 2013 so had to save my spreadsheet as an xlsm. I also note when I run the "setup print control" button, it opens your demo file and is not creating the named ranges in my spreadsheet because the button is linked to your spreadsheet name not mine. Have manually re-assigned macro and its now created named ranges, but still giving VB400 error. Will see if I can track it down with a few breakpoints
.
ok sorted. VB400 error is because I had an invalid print range. Also had to manually reassign the macro for the print button as it was also still referencing to your demo spreadsheet macro.
@Grant
Great News
I hope you enjoy this system
Hmm...VBA for Excel is pretty clumsy compared to Access!
Does not seem to be any way to assigned macros specifically to the current spreadsheet. If you copy the print_control worksheet into another spreadsheet the command buttons are still linked to the macro in the source spreadsheet!@#$ so the user then has to manually reassign the macros.That is just plain dumb!
ok here is modified code to fix the macro reassignments
--------------------------------------------------------
Sub Setup_Print_Control_Named_Formula()
' reassign macros to this spreadsheet
ActiveWorkbook.Activate
ActiveWorkbook.Sheets("Print_Control").Shapes.Range(Array("ButtonPrint")).Select
Selection.OnAction = "'" & ActiveWorkbook.Name & "'!sheet0.Print_Reports"
ActiveWorkbook.Sheets("Print_Control").Shapes.Range(Array("ButtonSetup")).Select
Selection.OnAction = "'" & ActiveWorkbook.Name & "'!sheet0.Setup_Print_Control_Named_Formula"
' Setup Print Control Named Range
' not sure if this is necessary as named ranges come accross when worksheet is copied
GoTo xx
ActiveWorkbook.Names.Add Name:="Print_Control", RefersToR1C1:= _
"=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))"
ActiveWorkbook.Names("Print_Control").Comment = _
"Used by the Print_Reports Subroutine"
ActiveWorkbook.Names.Add Name:="Copies", RefersToR1C1:= _
"=Print_Control!R26C13"
ActiveWorkbook.Names("Copies").Comment = _
"Specifies the No. of Copies for the Print_Reports Subroutine"
ActiveWorkbook.Names.Add Name:="PrintToPDF", RefersToR1C1:= _
"=Print_Control!R27C13"
ActiveWorkbook.Names("PrintToPDF").Comment = _
"Specifies wheter to print to pdf or paper"
ActiveWorkbook.Names.Add Name:="OpenWithPDF", RefersToR1C1:= _
"=Print_Control!R28C13"
ActiveWorkbook.Names("OpenWithPDF").Comment = _
"Specifies whether to open pdf after being created"
xx:
MsgBox ("Macros reassigned ...your ready to rock!")
End Sub
--------------------------------------
you can see I have remmed out the named ranges as you dont need to create these in new workbook as they come over when the print_control worksheet is copied across.
I've also changed following code to print routine so can choose print or pdf
------------------------------------------
If Worksheets("Print_Control").Range("PrintToPDF").Value = "Yes" Then
' save to pdf
Set ws = ActiveSheet
'check/make directory
strPDF = ThisWorkbook.Path & "\" & "PDF\"
'check if tmp folder exists
If Dir(strPDF, vbDirectory) = "" Then
MkDir (strPDF)
End If
'define pdf filename
strFile = Replace(Replace(ws.Name, " ", ""), ".", "_") _
& ".PDF"
'& Format(Now(), "yyyymmdd\_hhmm") _
'& ".pdf"
strFile = ThisWorkbook.Path & "\PDF\" & Format(i, "00") & "-" & strFile
If Worksheets("Print_Control").Range("OpenWithPDF").Value = "Yes" Then
bnOpenWithPDF = True
Else
bnOpenWithPDF = False
End If
'create pdf's
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=bnOpenWithPDF
'Debug.Print strFile
'MsgBox strFile & " created"
Else
'print to paper
ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True
End If
-----------------------------------------
This will create a subfolder called pdf and drop each print range in there as separate pdf, and open them after. I use Bluebeam pdf editor, which has a combine function so its only a couple of clicks to combine all the pdfs into one file.
There are a few free pdf utilities that support VBA so could in theory write code to append all the pdf's into one pdf as part of this process.
Hui...great printing tool here. This has been in the back of my mind for a number of years now, so great to finally find your site and solution
Cheers
Grant
@Grant
Many Thanx Grant
Fixing these issues has been on my to do list for a while
I will be incorporating your code into the sample files with the appropriate recognition very soon
Hui...
Its really humming now.
I've added defaults for margins, 4 row header, 2 row footer that accepts formating and & codes for filemane, sheet name date page no etc and printing to PDF. PDF option prints each sheet to temp PDF file, then shells out to PDFTK.exe to combine the PDF's into one PDF with same name as spreadsheet, then deletes temp PDF's
About then only thing I'd still like to add would be to be able to list a number of external pdf files to also combine as part of the single pdf doc....hmm I can probably do that too. Will report back if I can get that to work.
Hi Hui
Just found your Excel Report Printer which could really help me produce a monthly invoice and statement run with a few mods if possible. Saving to individual PDF files is good but could they automatically save each file as the names listed in column C (Description Header) to a single pre determined file folder?
Thank you!
@Steve
This happens because of the way Microsoft Excel sends the print job. Excel assumes that all your individual sheets have different page setups, so it sends them as multiple print-jobs.
You can override this using the technique here:
http://www.tracker-software.com/know...n-a-single-PDF
If the pages do have different setups, some PDF drivers allow printing of multiple pages to a single PDF file
Have a read of http://www.novapdf.com/kb/printing-a...-file-135.html
particularly Pt 5
Another technique is that discussed here:
http://www.clearlyandsimply.com/clea...d-version.html
Hui
I have looked at the links but they are not like your routine in that they offer little control or flexibility like your sheet does. Thanks anyway for your input.
I Think I have a solution for the pdf's. I use PDFCreator. It can be selected as a printer. After you press the print button it starts a print action for al the sheets. PDFCreator lines up the prints in a que and it also has an option to merge the que.
I hope it helps.
Hui,
I have a different problem. When I input a number in the copies field in the "K" column it only prints one copie of that sheet. I used your Demo as it is.
I have created a solution.
I placed the line "ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True" in a for loop.
It looks like this:
End With
Application.ScreenUpdating = True
End If
For c = 1 To NCopies
ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True
Next c
End If
End If
Next i
Next j
I hope others will benefit from this too.
Dear,
from the following code, I want 2 copies of "PROFORMA A" and 4 copies of "FORM NO. 16" to print. So would you modify the following vb code and send me to my mail id i.e. bhaiswarpravin@gmail.com
please do it for me......I shall be very thankful to you.
Sub print_sheet()
'
' print_sheet Macro
'
'
Sheets(Array("PROFORMA A", "FORM NO 16")).Select
Sheets("PROFORMA A").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Sub print_sheet()
'
' print_sheet Macro
'
Sheets("PROFORMA A").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
Sheets("FORM NO 16").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=4, Collate:=True, _
IgnorePrintAreas:=False
End Sub
Hello i have a excel in that i have a path where my all PDF File already save i just want to open that pdf file one by one and print it but i have a problem in that before printing that pdf i have to setup page sizing and handling setting to multiple and page order in vertical