FormatConditions Selection. S tFirstPriority With Selection. FormatConditions 1. Just select a range and run this macro and it will highlight top 10 values with the green color. Highlight Named Ranges. RefersToRange HighlightRange. Highlight Greater than Values. Delete Selection. Highlight Lower Than Values.
Highlight Negative Numbers. Highlight Specific Text. Offset I, 0. Value With myRg. Text Mid. Characters J, Len myStr. Highlight Cells with Comments. Sub highlightCommentCells Selection.
SpecialCells xlCellTypeComments. To highlight all the cells with comments use this macro. Highlight Alternate Rows in the Selection. Rows If rng. Highlight Cells with Misspelled Words. UsedRange If Not Application. Text Then rng. UsedRange If WorksheetFunction. Highlight Cells with a Specific Text in Worksheet. Highlight all the Blank Cells Invisible Space.
UsedRange If rng. Max Selection Then rng. It will check all the selected cells and highlight the cell with the maximum value. Min Selection Then rng. It will check all the selected cells and highlight the cell with the Minimum value. Highlight Unique Values. AddUniqueValues uv. This codes will highlight all the cells from the selection which has a unique value.
Highlight Difference in Columns. ColumnDifferences ActiveCell. Using this code you can highlight the difference between two columns corresponding cells. Highlight Difference in Rows. RowDifferences ActiveCell. And by using this code you can highlight difference between two row corresponding cells.
Print Comments. Sub printComments With ActiveSheet. Print Narrow Margin. InchesToPoints 0. Print Selection. Print Custom Pages. Please try again. Hide all but the Active Worksheet. Worksheets If ws. Name Then ws.
Unhide all Hidden Worksheets. Worksheets ws. Delete all but the Active Worksheet. Protect all Worksheets Instantly. Resize All Charts in a Worksheet.
ChartObjects i. Insert Multiple Worksheets. Protect Worksheet. Protect "mypassword", True, True End Sub. Un-Protect Worksheet. Unprotect "mypassword" End Sub. Sort Worksheets.
Name Then Sheets j. This code will help you to sort worksheets in your workbook according to their name. Protect all the Cells With Formulas. SpecialCells xlCellTypeFormulas. To protect cell with formula with a single click you can use this code. Delete all Blank Worksheets. Worksheets If Application. CountA Ws. Delete End If Next Application. Unhide all Rows and Columns. Sub UnhideRowsColumns Columns. Disable Page Breaks.
Workbooks For Each Sht In wb. Worksheets Sht. Create a Backup of a Current Workbook. Close all Workbooks at Once. Copy Active Worksheet into a New Workbook. Worksheets 1 End Sub. Active Workbook in an Email. CreateItem 0 With OutMail. Add Workbook to a Mail Attachment. Dialogs xlDialogSendMail. Show End Sub. Welcome Message. Closing Message. Don't forget to check other cool stuff on excelchamps. Count Open Unsaved Workbooks. These codes will help you to manage and make some changes in pivot tables in a flash.
Hide Pivot Table Subtotals. PivotTables ActiveCell. PivotFields pf. Refresh All Pivot Tables. PivotTables pt. RefreshTable Next pt End Sub. Create a Pivot Table. Auto Update Pivot Table Range.
PivotTables PivotName. Sub activateGetPivotData Application. Use these VBA codes to manage charts in Excel and save your lot of time. Change Chart Type. Paste Chart as an Image. Add Chart Title. Some of the codes which you can use to preform advanced task in your spreadsheets.
Create a Table of Content. Convert Range into an Image. Sub PasteAsPicture Application. Insert a Linked Picture. Sub LinkedPicture Selection. Use Text to Speech. Sub Speak Selection. Speak End Sub. Activate Data Entry Form. Sub DataForm ActiveSheet. ShowDataForm End Sub. Use Goal Seek. Activate With ActiveSheet.
Range "C7". Convert all Formulas into Values. HasFormula Then MyCell. Remove Spaces from Selected Cells. Remove Characters from a String. Add Insert Degree Symbol in Excel. Value Then ActiveCell. Reverse Text. Value End Function. Activate R1C1 Reference Style. Activate A1 Reference Style. Sub ActivateA1 If Application.
This macro code will help you to activate A1 reference style without using Excel options. Insert Time Range. Select Next i End Sub. With this code, you can insert a time range in sequence from to Convert Date into Day. Convert Date into Year.
Remove Time from Date. Int Rng. Value End If Next Selection. If you have time with the date and you want to remove it then you can use this code. Remove Date from Date and Time. Value - VBA. Fix Rng. Value End If NextSelection. It will return only time from a date and time value. Convert to Upper Case. IsText Rng Then Rng. Convert to Lower Case.
Convert to Proper Case. Proper Rng. Convert to Sentence Case. Remove a Character from Selection. Word Count from Entire Worksheet. Trim rng. It can help you to count all the words from a worksheet. Remove the Apostrophe from a Number. Sub removeApostrophes Selection. Value End Sub. Remove Decimals from Numbers.
Multiply all the Values by a Number. IsNumber rng Then rng. Add a Number in all the Numbers. Just like multiplying you can also add a number into a set of numbers.
Calculate the Square Root. Calculate the Cube Root. Add A-Z Alphabets in a Range. Convert Roman Numbers into Arabic Numbers. IsNonText rng Then rng. Remove Negative Signs. Replace Blank Cells with Zeros. Share 0. Tweet 0. About the Author. Hello guys thanks.. Hi and thanks for the previous examples! Any idea how I could solve this? Kath Reply. Dear Sir Please, what is the error in this program?
Thanks for sharing Reply. There is the change in below point Please help. Macro1 End Sub Reply. Select End Sub Reply. Is this something you can help me with? WK Reply. I must say. I know a bit about Excel. This is most enlightening. Well done. Cheers Reply. If you can help me with some tips I would be grateful. Thank you in advance. Hi Everyone, Can someone help me with my macro. Thank you so much. Hi Puneet Nice effort to allow users to use your codes.
I want to select range of cells as range and wish to give them some formula based on vlookup… For example I have in one sheet purchase entries done On the next page I am doing the calculation of gst… So every time I have to change the row reference…As one column I have 2.
Thankyou for this!!. Regards, Vinay Verma Reply. This one is golden Reply. Thanks Puneet, Excellent Work. You have given me the start I have been looking for. Many thanks, Reply. Looks similar but not the same. Tried two subs — neither worked — at least not in Hey Pwyller, which two? How to import a tif,pdf,img,etc… these types files by clicking button Reply. Hi Puneet, Thanks for gathering all this code!
Keep it up! Hi Sir, I have a query with regards to macros in excel, could I contact you via email? WIth Regards, Ankitha Reply. Thank you for sharing this make internet better!! Please I want you to help me Reply. Creating a balance sheet from trial balance Creating a customized bom… Reply.
What is the: save, Delet, Reset, and exit cods in macro Can you send me please Reply. Try Power Query for that Reply. Can you please post a code on how to attach a pdf document to outlook Reply. Thank for knowledge sharing. This is very useful page for VBA learner. Can you please help me Reply. I would love to have a macro code to replace a sheet name in a formula Reply.
Great work!!!! Save to PDF its really usefull. Hello, I have a macro which will consolidate all workbooks to single sheet but i need to have files names as well in each row to indentify how many lines from workbook Reply. Is there any formula to create different file from single work sheet?
Thanks for your words, I need to write an entire blog post for it, stay tuned. Project no wise split the data to convert the workbook if it is possible to share the coding Reply. Thank you so much sir. Thank you Reply. Thanks in advance Reply. Shall i get code for deleting rows if any of the column contains blank cells Thanks Reply. Ravi Patel Reply. How to reverse vlookup in VBA on bottom 20 records from records? Nice work Reply. Very well articulated, useful tool and helpful.
Thanks Puneet, nice work Reply. How to run macro for collecting data from read only file? Excellent would need your support Reply. Great list! Thank you! Thanks for your words. You are welcome. Therefore, in this particular section, I only explain line-by-line the first section of the macro. In other words, the With… End With block. The With statement allows you to execute a series of statements on a particular object ActiveSheet. PageSetup in this case without having to repeat the object qualification every single time.
In other words, all of the statements within the With… End With block rows 2 to 8 below make reference to the object that appears on the opening statement ActiveSheet. The object that appears on the opening statement is the page setup description of the active sheet. The object is obtained by using the Worksheet. PageSetup property. In this particular situation, the Worksheet object is the active sheet ActiveSheet. The PageSetup. CenterHeader property allows you to set what appears in the center section of the page header.
You specify the text that you want to appear in this section as a string. You can generally apply the syntax and explanation I provide here for the CenterHeader property to the following roughly equivalent properties:.
You can use the PageSetup. Orientation property for purposes of setting the xlPageOrientation value that specifies the page orientation. The Orientation property can take the following 2 values :.
You generally specify the relevant range using A1-style references and as a string. Under the A1-style cell referencing style, you use letters for column headings and numbers for rows. This range is from cells B5 to F PrintTitleRows property allows you to specify that a particular row or set of rows should be repeated at the top of each page. Generally, you specify the rows to be repeated as a string using A1-style references. In this particular example, I don't explicitly type A1-style references in the code.
Rows 5. You can generally use the same principles and syntax that I explain here in connection with the roughly equivalent PageSetup. PrintTitleColumns property. By using the PageSetup. Zoom property, you can determine how Excel scales the relevant Excel worksheet for printing or, in this case, conversion to PDF. As a general rule, you specify the zoom as a Variant value between 10 and percent. Whenever PageSetup. Zoom is set to False, the properties that determine how Excel scales the Excel worksheet are PageSetup.
At the same time, if the PageSetup. FitToPagesTall property for purposes of setting the number of pages tall to which the relevant Excel worksheet is scaled to when printing or, in the case of the example in this tutorial, converted to PDF. This is done in…. The syntax and purpose of the PageSetup.
In other words, you use the FitToPagesWide property to specify the number of pages wide the relevant Excel worksheet is scaled to when printing or converting to PDF as in this example.
As anticipated above, this End With statement simply ends the With… End With block that we've just analyzed. As I show in this example, you can make several changes or adjustments that influence the resulting PDF file by using the PageSetup object. You can use this parameter to set the filename of the resulting PDF file. Visual Basic for Applications allows you to do either of the following :. The use of the Filename that I make in these first 3 examples may work for you in certain circumstances.
After all, the material structure of those macros is enough to help you save Excel worksheets, workbooks, ranges or charts as PDF files. However, in several situations, you'll want to use the Filename parameter in a slightly different way for purposes of creating more sophisticated macros that save Excel files as PDF.
Let's take a look at some different ways you can use the Filename parameter:. If you're creating a macro to save Excel files as PDF, and those PDF files must always be saved in the same folder, you can simply hard-code the relevant file path and name using the Filename parameter of ExportAsFixedFormat.
Notice, however, how I've specified the full path of the file using the Filename argument. The consequence of this change is that the resulting PDF file is no longer saved in the default folder. Instead, the PDF is saved in the folder that you specify with the Filename argument. In the example above, the file is saved to a OneDrive folder called Power Spreadsheets.
You don't necessarily need to hard-code all of the details in the Filename parameter. You can, for example, concatenate different items for purposes of building a more flexible filename structure. In other words, if you use this Filename structure, the new PDF file is saved in the same folder as the source Excel workbook.
The reason for this is that the Workbook. Path property returns a string with the complete path to the relevant workbook. In the case of the example above, the relevant workbook is the active Excel workbook returned by the Application. ActiveWorkbook property. Name property using the Application. ActiveSheet property to return the name of the active worksheet as the actual filename within the Filename parameter.
The file path continues to be provided by the Workbook. Path property. In this particular case, the macro uses i the Application. ActiveSheet and Worksheet. Range properties to return cell F5, and ii the Range. Value property to return the value in that cell.
You can achieve similar results using slightly different structures. This includes, for example, naming the converted PDF file by concatenating several strings of text. In some cases such as when concatenating several strings of text to specify the PDF filename , it may be more appropriate to declare a variable at the beginning of the procedure to store the applicable string.
Whenever you use a worksheet cell as the source for the PDF filename as in the example above , make sure that the cell contains no forbidden characters. The VBA code samples that I provide in this section are just some of the examples of the different ways you can work with and generate the Filename argument of the ExportAsFixedFormat method. As you can see, Visual Basic for Applications provides significant flexibility in this regard.
Even though the topic isn't precisely the same as that of this blog post, some of the principles and examples I provide there may be helpful to you when working with the Filename parameter of the ExportAsFixedFormat VBA method. You can use the Application.
This allows the user of your VBA application to specify the full path and filename. I cover the Application. For purposes of this macro example, it's enough to know that the GetSaveAsFilename method:. The 2 blocks of code I highlight in the image above are substantially the same as those that appear in other examples of VBA code within this Excel tutorial.
The only difference is in the Filename parameter , which is the topic we're focusing on in this section. I cover the topic of VBA variables in more detail here and here. A variable is simply a storage location that you pair with a name. You use the variable to represent a certain value. In this example, I use the PdfFilename variable for purposes of storing the filename that the user enters into the Save As dialog box displayed by the Application. GetSaveAsFilename method in block 3 below. Even though Variant isn't the most efficient data type, it's probably the most desirable for this particular case.
The basic syntax of Application. ActiveDocument follows that of the Application. Documents property above:. Automation is probably the most commonly used and the preferred tool for purposes for purposes of controlling one Windows application from another. When using Automation for purposes of manipulating one application from another one, you refer to the relevant applications as follows:.
In this context, a particularly important advantage of Automation is that it allows you to programmatically access the Component Object Model COM objects offered by the Server Application. Automation lets the client application harness the built-in capabilities of the server application. In the case we're looking at, you use Excel to harness the built-in capabilities of Word.
The purpose of doing this, in this case, is to convert the contents of a PDF file to Excel. For purposes of using Automation, the main thing you need to is create a connection between the relevant applications.
In this particular case, those applications are Word and Excel. In the sections below, I provide samples of VBA code that use both early and late binding. In the section containing the code sample that uses early binding, I also show how you can create a reference to Word's object library.
The basic steps of this process open file, copy and paste are roughly the same as the steps followed by the previous macro examples 1 using Adobe Reader and 2 with Able2Extract. Therefore, you may notice that some lines of code are substantially the same as those used by the previous samples.
I explain each of the lines of code below. When appropriate, I refer to the equivalent lines of code in previous sections. However, since this particular macro uses early binding, you must start by setting up a reference to the relevant object libraries.
Let's start by taking a look at this:. In order to create these references, go to the Visual Basic Editor and follow these 4 easy steps :. Once you've set the object reference, you can access the Word object model and use the objects, methods and properties it contains.
The purpose of the myWorksheet object variable is to represent the Excel worksheet where the data within the PDF file is pasted. The main purpose of this line of code is to create a variable representing the Word application in order to expose the Word object model. The purpose of this line of code is similar to that of the previous line 2. More precisely, this line 3 creates the variable that provides access to the native Windows shell. I explain why myWshShell is required when explaining line 13 below.
Its purpose is to declare a variable that holds the path to the PDF file you want to convert to Excel. It uses the Set statement to assign a particular worksheet Word Early Binding of the active workbook to the myWorksheet object variable.
Both of these lines of code use the Set statement for purposes of assigning a new New instance of the relevant object to the applicable object variable. This line of code uses Word's Application. Version property wordApp. This property returns the version number of the relevant Word version. That version number returned by the Version property is assigned to the wordVersion variable. The expression on the right side of the assignment operator is created by concatenating the following 3 items:.
In order to understand why this statement is necessary, let's take a look at what happens when you manually open a PDF file using Word :. Additionally, you have the option of specifying that the message shouldn't be displayed again. To do this, you just need to check the relevant box that appears on the left side of the dialog box. Clicking the OK button manually is quite easy. At first glance, this is something that would also be easy to handle using VBA.
The key that corresponds to the value written to the registry doesn't initially exist. In order to proceed with the PDF to Word conversion, you must solve this. The statement uses the RegWrite method of the WshShell object. You can use the RegWrite method for 3 main purposes :. The practical effect of this line of code 13 is that the warning in the dialog box that I explain above regarding the PDF to Word conversion is turned off. Therefore, Visual Basic for Applications is able to proceed with….
Lines 14 to 16 are a single statement. The statement calls the Documents. Open method. This method allows you to:. Notice that the statement we're analyzing only makes reference to 2 of the parameters of Documents.
Open :. As a consequence of the above, the practical effect of lines 14 to 16 is that Word opens the PDF file you want to convert to Excel. The only difference between lines 15 and 17 is the value assigned to the anyValue parameter of the RegWrite method.
In practice, line 17 reverses what line 15 does. In other words, it turns the warning regarding the PDF to Word conversion back on. Other than the above, the general aspects within the explanation I provide in line 15 above are applicable. Uses Word's Range.
Copy method for purposes of copying the content of the file you want to convert. Within this line 18, this Range object is that returned by the Document. Content property. When you use Document.
The Document object to which the Content property makes reference to is the active word document returned by Word's Application.
ActiveDocument property. The practical effect of With… End With is that the series of statements within the block are executed on the same object. In the case of the sample macro we're looking at:. The whole With… End With block composed by lines 19 to 22 of this sample macro has substantially the same effect as lines 17 to 20 of the previous macro examples.
This line of code is the same as line 18 of prior code examples. It uses the Range. Select method to select cell B4 of myWorksheet. This line sets the location where the data from the PDF file you're converting is pasted.
This statement is needed because it's required by the Worksheet. PasteSpecial method used by…. More precisely, it uses the Worksheet. PasteSpecial method for purposes of pasting the contents of within the Clipboard on the selected range see line 20 above using a particular format.
Uses Word's Application. Quit method to close Word without saving the file changes. For purposes of this tutorial, you only need to be aware of the following 2 items :. Both lines of code use the Set statement to set the wordApp and myWshShell object variables to Nothing. The reason these statements are included is to release the memory. The only difference is that, instead of using early binding, this sample 4 uses late binding.
The macro, therefore, follows exactly the same 3-step process to convert the PDF file to Excel :. There are only a few differences between the 2 macro examples that rely on Word to convert a PDF file. I highlight them in the following image:. I explain these differences in the following sections.
Other than these clarifications, the comments I provide above when explaining macro example 3 with early binding are roughly applicable to this macro. Both of these lines of code use the Dim statement to declare an object variable. Furthermore, in both cases, the object variable is declared as an Object. This declaration as Object forces late binding. These lines of code are the equivalent of lines 2 and 3 of the previous macro example 3 that uses early binding.
In that case, however:. The consequence of this difference is that both macros paste the data from the PDF file in different Excel worksheets.
This difference, however, isn't strictly related to the topic of early binding vs. It's simply a consequence of the way in which I create and organize the sample workbooks that accompany this tutorial. In the previous macro example 3 with early binding , the object references are created in the same lines 8 and 9.
However, in those cases, the object reference is created by using the New keyword instead of the CreateObject Function. This line of code achieves exactly the same purpose as line 23 of the previous macro example 3. The consequence of this line is, therefore, that Word closes without saving the file changes. The only difference between the syntax used in the macro that uses early binding and here is the way in which the value of the SaveChanges parameter is specified. In the previous example, this was specified as wdDoNotSaveChanges.
Here, it's assigned the value of 0. Now that we've covered how you can use Word to extract data from PDF to Excel using VBA, let's take a look at the results I obtain when executing the sample macros 3 and The VBA code examples 3 and 4 are materially the same. The only difference between them is the fact that 3 uses early binding, whereas 4 uses late binding. As a consequence of the above, the results I obtain when executing both macros are substantially the same.
0コメント