Macro To Publish Print Area From Active Sheet To Web Page
I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.
I am using the the following macro:
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macro To Print Page Based On Active Page
I need a macro that will print a hidden page that is linked to the page I have active. It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View Replies!
View Related
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
Publish File On The Web
I developed a small program using Microsoft Office Excel 2007 and VBA. The program contains some user interface items such as command buttons and userforms for user input. During the distribution of the program I realised that most users do not have Excel 2007 yet. When I try to convert the file into Excel 97/2003 it is not possible because I used features not available in this version. I was wondering if there is any way of publishing the Excel file in the web. In this way Excel 2007 would exist in the server and the users could use the file directly from the server without needing Excel 2007 installed in their computers. I tried to save the file as "Web page" or "Single File Web Page" but the "buttons" stopped working, I guess the VBA code is not saved..
View Replies!
View Related
Print Area With A Macro
I am looking to set the print area with a macro, below is what I had hoped would work. It does not. I get unable to get Vlookup property of worksheetFunction class error. Sub Printzed() Start = Range("C6").Address Finish = Res = Application.WorksheetFunction.VLookup(I5, Range("prlu"), 2).Address myPrintArea = Start & ":" & Finish ActiveSheet.PageSetup.PrintArea = myPrintArea End Sub
View Replies!
View Related
Using Macro To Set Print Area
My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now: Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76" The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?
View Replies!
View Related
Non-contiguous Print Area Macro
I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?
View Replies!
View Related
Adjust Print Area Macro
The macro below works perfectly except for one problem. I only want it to set the print area from Column A - Column M . It currently selects data i have in Columns N and beyond and i do not need that to print. From A - M is 13 columns.
View Replies!
View Related
Macro: Paste Special From Web Page
I need to create a macro to "paste special - values", therefore I have coded "Selection.PasteSpecial Paste:=xlPasteValues" It works well if copy and paste special within the same excel sheet. However, when I ctrl C on any other website and revert back to excel to use my macro to paste special, it cannot work.
View Replies!
View Related
Open Web Page Macro Code
I am currently using the following code in order to open a web page by clicking a button: Private Sub Timetable18_Click() ActiveWorkbook.FollowHyperlink Address:="http://journeyplanner.tfl.gov.uk/user/AHF/JP15__00006e8e.pdf" NewWindow = True End Sub However, when this is executed it returns the following error: Run-time error '-2146697210 (800c0006)': Method 'FollowHyperlink' of object '_Workbook' failed Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.
View Replies!
View Related
Macro To Select An Area, And Print Selection
I need a macro to select the area within the blue invoice sheet box, so everything within the blue invoice sheet box is selected , and then the selection should be printed. I will have hundreds of these invoice slips made, right below each other, i was wondering once this is done, is there an easier way to to have each invoice selected, instead of making a custom button for each sheet ( which selects just that invoice , and prints that selection ). ( Column m through v, starting at row 2 stoping at row 68, is the selection required in this example, everythign within the blue box. ).
View Replies!
View Related
User Defined Print Area Macro
I am looking to create a macro that will ask the user to define the row number of the beginning and ending locations that they would like to print. The columns are fixed in that the first starting row will be from column A and the ending row will be column M. I have built a pipe tally used in the oilfield that keeps track of large amounts of pipe figures but very often is the case that there is the need to print a section of the tally data in reference to a certain depth. With the vast knowledge base located here I was able to build the first macro that prints the whole active tally but now I need something more refined. Below is one of the copies of the macro that I was trying to get to work and it is missing a couple of things but the most important part shows up and that is how to get the inputbox variables into the defined print area. Sub TallyVariable() Dim StartRow As String Dim EndRow As String StartRow = InputBox("Please Enter Starting Row you would like to print") EndRow = InputBox("Please Enter Last Row you would like to print") ActiveSheet.PageSetup.PrintArea = "A" & StartRow.Address:"M" & EndRow.Address End Sub
View Replies!
View Related
Macro Code To Log Onto Web Page Form
Im using information from oracle to do applications on excel. As im nothing related to IT department and I'm working on a BIG company, i did it by myself, i dont have support. What i did is to investigate about oracleas (portal), then from my excel application i create the URL of the search i need, query it and paste on the document the table i need. I did this from months and worked great. But now portal have been upgraded and need to do login before use it. I have some amateur knowledge about some HTML, PHP, CSS but i dont get how to fill the username and password for this new portal. I will copy part of the code from portal that i think is the key: <SCRIPT TYPE="text/javascript"> <!-- Comment out script For old browsers Function setFocus() { document.forms[0].ssousername.focus(); }; window.onload = setFocus; //--> </SCRIPT> <NOSCRIPT> </NOSCRIPT> <TABLE WIDTH="90%" BORDER="0" CELLPADDING="0" CELLSPACING="0"> <TR> <TD> <BLOCKQUOTE> <BLOCKQUOTE>......
View Replies!
View Related
Macro To Set Print Area W/o Hidden Rows
We run a small gardening shop and use a simple spreadsheet to track of various things. Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need. The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up. So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?
View Replies!
View Related
Setting Variable, Discontinuous Print Area In A Macro
I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data. Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.
View Replies!
View Related
Page Number On Sheet Without Print It
I'm trying to figure out for my boss also I've done some search in this forum but no avail. He want each page to have large faded page number in the background like 'Page 1', Page 2' etc(there's 12 pages in one sheet) for presentations but do not want the page numbers to appear in the printed copies. Is it possible? I know about the watermark but he does'nt want page numbers in the prints
View Replies!
View Related
Extracting Web Data From A Sub Web Page
I am using the "CreateObject("InternetExplorer.Application")" method to extract data from a webpage. This webpage is a sub page that I cannot find the data for, even after filtering through all the ".document.all(x)" items? Any ideas on how to obtain this "sub page" data, since there is other data that I need which is on the "main" page? I don't want to have to call up this sub page separately since I'm already seeing it (physically able to view it on the main page but not able to extract it) from the main page?
View Replies!
View Related
Page Setting Print Macro
I have a excel sheet with multiple sheets in it.And I have a VBA macro which split all those sheets into one file each.When I am trying to print one file(SHeet) which has more than 15 columns it is printing in two pages(Width).Is there any code i can use so that when ever i open a file which was split by me and give print command the width should fit to the page and peint.
View Replies!
View Related
Print Previewed A Worksheet And Find A Tiny Image With A Small Portion Of The Print Area
I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
View Replies!
View Related
XL4 Print Macro. Count Page Breaks
I'm using a procedure (PageSetupXL4M) which uses the XL4 Page.Setup function to format a sheet in VBA--since it's much faster than VBA's current PageSetup function. (It's explained here: [url] The problem is that the XL4 Page.Setup function seems to clear all the automatic page breaks. I'll use "MsgBox Sheets(1).HPageBreaks.Count" to display the page break count both before and after the XL4 function. It'll show the correct # of PageBreaks before the function, but shows 0 after. I need to return the correct # of PageBreaks AFTER the function is run because the formatting of the page can sometimes alter the number of PageBreaks. how to get "HPageBreaks.Count" to recalculate and return the correct number of breaks?
View Replies!
View Related
Macro To Print Pivot Table Page Fields
I want to print out, for a range of 'Page Fields', from a Pivot table automatically. The Macro must be able to: Automatically go through and select the 'Page Fields' in the pivot table Select the print area range automatically, which will change each time the pivot is refreshed/reselected. Set the print area to fit the page for each category selected in the pivot table Finally print. Can't find code for this anywhere, but i must admit got fed up with looking
View Replies!
View Related
Vb Code To Set Print Area And Print Out
with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default. The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each. e.g. header_____header_____header data_______data_______data data_______data_______data data_______data_______data header_____header_____header data_______data_______data data_______data_______data data_______data_______data and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
View Replies!
View Related
Active Area
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area. Sub Insert_for_SQs() Do Selection.EntireRow.Insert ActiveCell.Offset(6, 0).EntireRow.Activate Selection.EntireRow.Insert ActiveCell.Offset(12, 0).EntireRow.Activate Loop End Sub
View Replies!
View Related
Macro Copy Of The Active Sheet And Then Rename The New Copied Sheet
I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004'). "O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3)) Sub NewMonth() ActiveSheet.Copy Before:=Sheets(Sheets.Count) ActiveSheet.Name = Range("O4").Value ActiveSheet.Range("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
View Replies!
View Related
Sort Area Around Active Cell By Two Columns
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy. Dave supplied me with a macro that will sort a range by the first column: Sub SortThis() With ActiveCell. CurrentRegion .Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With End Sub I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View Replies!
View Related
Print Only Used Area
I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page. I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish.
View Replies!
View Related
Print Area Always Changes -
I use the code below to print a portion of a sheet. My problem is that the area always changes. The $A$476 should be the first cell in that column that contains "HEA" The "536" in $F$536 is the last row that contains data. There are conditional formats all the way down to row 1,000 but not data Private Sub CommandButton1_Click() Dim strCurrentPrinter As String, strNetworkPrinter As String ****strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF") ****If Len(strNetworkPrinter) > 0 Then ' found the network printer ********strCurrentPrinter = Application.ActivePrinter ********' change to the network printer ********Application.ActivePrinter = strNetworkPrinter ********ActiveSheet.PageSetup.PrintArea = "$A$476:$F$536" ********ActiveSheet.PrintOut** 'print something ********' change back to the previously active printer ********Application.ActivePrinter = strCurrentPrinter ********ActiveSheet.PageSetup.PrintArea = "" ****End If End Sub Obviously there is a function also that finds the full network printer name, I didn't think it was important, because my issue is the print area. Everything else works fine. If you need it let me know and I will post it.
View Replies!
View Related
Data Validation Input Message - Always In The Same Area Of The Page
I have multiple data validations with input messages. For the new user, this is handy, but for the person that does this a lot, they get in the way. When I open the sheet, I can move one message to the side of the page and the rest of them come up at the same spot but when I save, exit, and reopen, the message comes up beside the cell and I would like it to always stay to the side of the page.
View Replies!
View Related
Save As Web Page
Hi, i am trying to save a range on Sheet1 as a web page. i have managed to save the entire workbook as a webpage but am struggling to get the right syntax for just the range on sheet1. when i do this manually i select the range, set the print area, then choose file-save as web page- and choose selection:print area or republish if i have done this before. i am not sure also where to add the directory i wish to save it to. the file name always changes, so i can either: use the name of the file or i can use the same file name that is entered into a cell (cell never changes). i hope this makes some sort of sense. here is the code i used to save the entire workbook
View Replies!
View Related
Web Page Interaction
I would like to be able to turn the following worksheet in to an inspection scheduler. It already displays job information pertaining to the job & lot number entered into cells C2 & C3 in cells F2:F14. Now, I would like to write a script that will do the following... If lets say I click on "Groundwork", it will know that I am trying to schedule a groundwork inspection for the permit number in cell F11, and it will know it needs to be scheduled through Fairfax County's website, because it says "FFX" in cell F2. (the only two counties I really deal with online are Loudoun & Fairfax...I'm not worried about the others). So it will then automatically jump to Fairfax County's website (links to the websites are in cells E22:E26), conduct the appropriate steps to schedule an inspection through that county, then kick back some sort of confirmation that it was scheduled. The same steps apply each time for each inspection type, but each county has their own steps....
View Replies!
View Related
Get Elements From Web Page
Dim struserID As String Dim strPassword As String Dim strUploadFile As String Dim strQueryURL As String Dim objIE As SHDocVw.InternetExplorer Dim htmlDoc As MSHTML.HTMLDocument Dim htmlInput As MSHTML.HTMLInputElement Dim htmlColl As MSHTML.IHTMLElementCollection Set objIE = New SHDocVw.InternetExplorer struserID = "xxxxxx" strPassword = "abcdef" strUploadFile = "C:Misc estupload.txt" Code navigates to sign on page, enters userID and password, clicks on submit. The password used causes upload page to load. This all works ok. After upload page is loaded, the code does not enter either of the two following "for" routines.
View Replies!
View Related
Get Number From Web Page
I am trying to get a single number from a web query table without downloading the table to a worksheet. I would like to either turn the table into an array in vba or just extract one number from the table. In this case it would be the "This Week" result or array(2,2) position in the table. The website:[url] I have tried messing around with the destination for the webquery object but cannot get it to work unless the destination references (directly or indirectly) a worksheet range.
View Replies!
View Related
Link To Web Page
I want to create a sheet where the weather is updated automatically. for example, CITY TEMPERATURE PERCIPATATION Toronto -9 20% rain Montreal -20 40% rain thats what it should look like or similar. The problem is I want to have a formula for the temperature where it gets an update from the internet. So everytime I open or refresh this sheet, it would change to whatever the website is.
View Replies!
View Related
Variable Print Area
I'm using Excel 2003. I have a spreadsheet used for costing jobs giving a detailed breakdown of parts and time. Sometimes the customer requests a copy so I'm creating a macro for my boss that prints the spreadsheet, less the hidden columns that I don't want the customer to see. This I can manage. My question is this: Is there a way within the macro to set the print area to change to where the last cell is automatically? Each spreadsheet is a different length dependent upon the work required and setting a larger print area to compensate would use excess paper and ink.
View Replies!
View Related
Print Area Dimension
I used the macro recorder for this: ActiveSheet.PageSetup.PrintArea = "$A$1:$P$246" How would I change this to use variables? I need to set the print area to A( FR ) to P( LR ) Of course FR is first row, LR is last row
View Replies!
View Related
Auto Print Area
I have a hard time transitioning from other examples to my workbook. What I am after, is for the print area on 2 sheets to be set by the count of non-empty cells in column B on the first sheet. I have a maximum number of entries of 200 (rows) plus a header row. So, my entry #200 is in worksheet row 201 (cell B201) In cell B203 I use formula "COUNTA" to tell me how many cells are not empty. As an example, this number might be 36. The print area should be limited to worksheet row 37 (1 more than 36 due to the header row). My columns are always the same, A thru I. On a second worksheet, measurements are recorded, 10 per row. So, if I have 36 items to be measured, I use 4 rows (10 per row=3 full rows and 6/10 of the 4th row). I would like to have the print areas set automatically, based on this number in cell B203 (see above notes). On the 1st sheet (using example of 36 entries), my print area needs to be thru row 37, columns A thru I. On the 2nd sheet, my print area needs to increase 10 rows every time B203 increases by 10. (this 2nd sheet has other info on it, so when the print area increases, it needs to go in steps of 10 rows. as an example, if B203=8, then the print area on the 2nd sheet includes cell A1 thru cell K13, but if B203=12, then the print area would be cell A1 thru cell K23)
View Replies!
View Related
Print Area VB Code
I wish the print area to be set based on the number entered into cell "D58". My code so far is: Sub Worksheet_SelectionChange(ByVal Target As Range) Dim NumPages As Range Set NumPages = Range("D48") Worksheets("Sheet6").PageSetup.PrintArea = Worksheets("Sheet6").Range(0, 0).Resize(NumPages * 21, 47) End Sub
View Replies!
View Related
Print Area Code
I have some code to select a print area depending on wether or not there is data in a cell. The code I have so far partially works but not quite like I need it to. Here's what I have, Sub selectprintarea() Select Case True Case Range("C5").Value > 0 Range("A1:M47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$M$47" Case Range("Q5").Value > 0 Range("A1:AA47").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$AA$47" End Select End Sub It will select the print range if there is data in C5 but if there is data in C5 and Q5 it only selects the first print area. I know I need an "If - Else" or something of that nature but I cant figure that out. Also I need it to look at other worksheets not just the active one. I've tried a replacing the ActiveSheet with Worksheets (Sheet1). but that gives me an error.
View Replies!
View Related
Dynamic Print Area
I can define a name to calculate the desired Print Area for a page. For example, print_area_notes: =Notes!$A$1:INDEX(Notes!$A:$Z,final_Note,5). I can then use that name when defining the Print Area via Page Setup. The problem is it calculates the cell range immediately and substitutes that in the Print Area field. When print_area_notes changes the actual Print Area does not. Is there some formula I can enter in Print Area that will allow this value to be set dynamically? I have tried setting Print Area to =indirect("print_area_notes") but it doesn't like that. I could, but prefer not to, write a VBA function to set the Print Area but even then how do I set it to be called automatically? I'd prefer to be able to enter a formula and do it without VBA code because you then have the security issues every time the spreadsheet is opened.
View Replies!
View Related
Insert Cell Value To The Web Page
I have list of URL's (5000+) per country which need to be check whether they are working or broken. I have found this website http://www.seoconsultants.com/tools/headers.asp which solves my purpose but processing 5000+ URL's manually will consume lot of time So is it possible to have macro which will pick URL from each cell & run it in "URI:" textbox of the above link.
View Replies!
View Related
Import Web Page With VBA
I need to get VBA code to import a web page one at a time based on a condition. When one of the urls below has "1 mins to post", I want that page imported into a worksheet. The pages require a login and here is the source code for login - "acctnum" and password - "zipcode". The name for "1 mins to post" is name ="MTP". I would want to keep the page loaded until "MTP" = OFF. Then whenever the next URL goes to "1 mins to Post", load that web page and keep it loaded until "OFF" and so on. This would need to continually scan through all of the following urls: http://tote.phonebet.com/WPSARL-C.PHP http://tote.phonebet.com/WPSBEL-C.PHP http://tote.phonebet.com/WPSCAL-C.PHP http://tote.phonebet.com/WPSCAN-C.PHP http://tote.phonebet.com/WPSCTN-C.PHP http://tote.phonebet.com/WPSCTN-C.PHP http://tote.phonebet.com/WPSCOL-C.PHP http://tote.phonebet.com/WPSDPK-C.PHP http://tote.phonebet.com/WPSELL-C.PHP http://tote.phonebet.com/WPSEMD-C.PHP http://tote.phonebet.com/WPSFMD-C.PHP http://tote.phonebet.com/WPSFLK-C.PHP http://tote.phonebet.com/WPSFTE-C.PHP http://tote.phonebet.com/WPSHAS-C.PHP
View Replies!
View Related
|
TRACKER
