Learn Excel VBA Programming & Macros (Free Tutorial & Download PDF)
※ Download: Vba excel tutorial pdf
Therefore, PdfFilename is False whenever the user cancels the Save As dialog box. GetSaveAsFilename method to provide the Filename parameter when using the ExportAsFixedFormat method: Excel MVP Debra Dalgleish, at , suggests a similar and slightly more advanced example of VBA code to export the active sheet as a PDF file including use of the GetSaveAsFilename method. If the condition isn't met, performs no action.
This is, however, not the only type of loop you can use for these purposes. You specify the relevant file type by choosing the appropriate value from the XLFixedFormatType enumeration. Documents property above: expression.
Learn Excel VBA Programming & Macros (Free Tutorial & Download PDF) - For purposes of using Automation, the main thing you need to is create a connection between the relevant applications.
This commission comes at no additional cost to you. All of these topics are quite important. After all, PDF files are one of the most widely used formats for electronic documents. If you're anything like me, you probably encounter PDF files at work virtually every single day. In some of those cases, you may need to extract data from PDF to Excel. I cover the topic of manually converting PDF files to Excel in the first post I link to above. Those 3 methods particularly methods 2 and 3 are helpful in many situations you may encounter in your day to day work. You might, however, be wondering whether there's a way to automate such processes. If that's the case, I've got good news for you: It's possible to use VBA for purposes of extracting data from PDF files to Excel. To be more precise, you can use VBA to automate the 3 methods of converting PDF to Excel that I mention above. I show you how to do this in the VBA tutorial below. Therefore, let's start by taking a quick look at the topic of… Controlling Other Application From Excel With VBA There are several ways in which you can control other applications from Excel. More precisely, I use SendKeys when working with Adobe Reader or Able2Extract to convert a file from PDF to Excel. Automation is relevant for purposes of using Word when converting a PDF file to Excel. I explain how to use SendKeys for purposes of communicating between applications first. However, for purposes of using SendKeys, you first need to use and understand the Shell Function. The reason for this is that you can only use SendKeys with an application that is currently open and running. SendKeys isn't capable of opening an application by itself. However, the Shell Function is able to run an application. Once the application is running, you can use SendKeys. If the program doesn't run successfully, an error occurs. You use it to specify the following: 1: The path and name of the program to be executed. In other words, you generally use pathname to specify i the program to be executed and, if required, ii the file name and arguments required for purposes of executing the program. If you're working on a Mac, you can use for purposes of using the application signature instead of its name to specify the pathname argument. You use windowstyle for purposes of determining the style of the window in which the program you specify using pathname is executed. You can generally use the windowstyle values that appear in the table below. In all cases, I describe i what happens to the window of the newly executed program and ii which window has the focus is active : Value Constant Description 0 vbHide Program window is hidden. Focus is on hidden window. Focus is on new window. Focus is on new window icon. Focus is on new window. Focus is on current active window. Focus is on current active window. If you omit windowstyle, the default value is vbMinimizedFocus. In other words, the program starts minimized with focus. If you're working on a Mac, the following differences apply particularly on the more recent iOS versions : Difference 1: You can only use windowstyle to specify whether or not the relevant application has focus when executed. Difference 2: The vbHide windowstyle constant 0 I show above isn't generally applicable. Therefore, in practice, when working with a Mac you can usually only choose between the following 2 options: Option 1: Display the newly executed application in the foreground with focus. For these purposes, you can use any of the 3 windowstyle values with focus vbNormalFocus, vbMinimizedFocus or vbMaximizedFocus. Option 2: Open the application in the background without focus. For these purposes, you can generally use the windowstyle values without focus vbNormalNoFocus, vbMinimizedNoFocus or vbHide. The Shell Function has a characteristic that can be tricky to handle: It runs the relevant program asynchronously. In other words, Visual Basic for Applications continues executing the statements that follow the Shell Function without pausing regardless of whether Shell has achieved its purpose opening another application. In other words, those statements may need the application launched by Shell to be open before they can do anything. You don't have to go too far to find situations where this is an issue. In fact, that I provide below serve as examples. This, however, doesn't mean that you can't do anything to reduce the possibility of errors. As Richard Mansfield goes on to explain in the above-cited Mastering VBA for Microsoft Office 2016: A crude but often-effective fix is to just allow extra time for the Shell function to execute before taking any dependent action. There are a few ways in which you can allow for this extra time. You can use the Sleep API to suspend execution of a thread for a certain amount of time. The Wait method allows you to pause a macro for a specified amount of time. I don't cover these topics in detail within this blog post. However, I may write about the topic of pausing or delaying VBA in more detail sometime in the future. If you want to receive an email whenever I publish new content in Power Spreadsheets, please make sure to register for our Newsletter by entering your email address below now: Before we move on to SendKeys, let's take a short look at the… TaskKill Command The main purpose of the TaskKill command is to end a task or process. As explained by , you can use the Shell Function for purposes of killing the application once you're done working with it. Therefore, in the context of this VBA tutorial, we're interested in how we can use TaskKill for purposes of closing the application that the Shell Function runs. As a consequence of this, I don't explain everything there's to know about the TaskKill command. The syntax of the TaskKill command can get relatively complicated. This makes reference to the Process ID of the task or process you want to terminate. If you use this alternative within the context of this tutorial, the relevant Process ID is the task ID returned by the Shell Function as I explain above. In this alternative, you specify the process to be terminated by using its image process name. This is the way in which I specify the task to be terminated in the code examples below. You can refer to them for purposes of seeing how I implement the TaskKill command to close both Adobe Reader and Able2Extract after using them to convert the data from a PDF file to Excel. SendKeys Statement And Application. SendKeys Method Visual Basic for Applications has both a SendKeys statement and a SendKeys method. They both behave in a very similar manner and, therefore, I explain both of them in this section. Let's start by taking a general look at the main similarities and differences between the statement and the method: SendKeys Statement Vs. SendKeys Method As a general matter, you can use both the SendKeys statement and the Application. SendKeys method to send keystrokes to a particular window or application. This, in practice, has the same result as actually typing those statements on the keyboard. As a general rule, you can't use SendKeys to send keystrokes to an application that isn't designed to be executed on a Windows or Mac platform. This is the case, for example, of DOS applications that run in a virtual machine. SendKeys is also not able to work with applications that aren't currently running. As I mention above, you must use the Shell Function for purposes of opening the application. Once the application is running, you can use SendKeys. SendKeys method for purposes of sending keystrokes to the active application. Even though the description is almost identical, notice the difference at the end of the description of both constructs. Even though the active window and the active application may and usually make reference to the same, this isn't always the case. As explained by Excel expert Joe Was in , the active window and active application may be different. So, what's the bottom line? More precisely, in order to determine which one is appropriate to use, you must be aware whether you're i controlling one application from another, or ii working within one application. SendKeys method mirrors the above: expression. SendKeys Keys, Wait When working with the Application. This first argument string or Keys is required. You use it to specify the keystrokes that should be sent as a string. You use it to determine whether Excel waits for the relevant keystrokes to be processed before control comes back to the macro. More precisely: If you set wait to True, the keystrokes you specify with argument 1 string or Keys are processed. Only after this processing has been completed, control returns to the VBA procedure. If you omit the wait argument or set to its default value of False , the keystrokes are sent and control immediately returns to the macro. In this case, the VBA code doesn't wait for the keystrokes to be processed and the macro execution simply continues. Argument 2 is relatively straightforward. As I mention above, it's a Boolean and you can only set it to True or False or omit it. However, you have a lot of options when specifying argument 1 string or Keys. The main exceptions you should be aware of are those I explain in rule 4 below. The table below includes several of the most relevant. You can also refer to the lists at the Microsoft Dev Center for the SendKeys statement and for the SendKeys method. The main reason why they're subject to this rule is that they have a special meaning within other applications. This may be significant if there's dynamic data exchange DDE. Note that this rule 5 applies only to combinations of a single key with Shift, Ctrl or Alt. In other words, only the keystroke that immediately follows the code above for Shift, Ctrl or Alt is considered to be pressed at the same time. The other keystrokes in the string that you specify are considered to be pressed separately. For these purposes, enclose those other keys in parentheses. When To Use SendKeys SendKeys is a very basic way of controlling other applications from Excel. For example, in Mastering VBA for Microsoft Office 2016, author Richard Mansfield describes SendKeys as… The oldest and most primitive method of communicating between applications. You may not be surprised by such descriptions. In fact, as Mansfield himself explains, SendKeys may cause certain issues. In particular, you generally face limitations when trying to step through the code using. You might then be wondering… Should you use SendKeys at all? If so, when should you use SendKeys? SendKeys should probably not be your VBA construct of choice for purposes of controlling other applications from Excel. However, in certain situations, it can be helpful. A useful general rule is provided in Mastering VBA for Microsoft Office 2016: You may find SendKeys useful if neither Automation nor DDE works with the target application. In the code examples that I explain below, SendKeys is used for purposes of i accessing the menus of other applications and the tools within those menus, or ii taking within those applications. You can get this PDF file. The basic structure of this VBA application is commonly found throughout the Internet whenever you search for examples of how to extract data from PDF to Excel. For examples of similar versions, you can check out , , also , and. Let's take a look at each of the lines of code to understand the specifics behind this sample macro: Line 1: Dim myWorksheet As Worksheet Uses the Dim statement to declare the myWorksheet object variable as a Worksheet. The main purpose of this line of code is to create an object variable that represents the Excel worksheet where the contents of the PDF file are pasted. Lines 2 To 4: Dim adobeReaderPath As String Dim pathAndFileName As String Dim shellPathName As String All of these lines use the Dim statement for purposes of declaring several variables adobeReaderPath, pathAndFileName and shellPathName as Strings. In the case of this example 1, this application is Adobe Reader. The variable holding this string is adobeReaderPath. This string is held by the pathAndFileName variable. It i takes the expression to its right side item 3 below , and ii assigns it to the variable on its left side item 1 above. ActiveWorkbook property returns the active workbook. Property 2: The Workbook. This is the path to the application used to open the PDF file. Item 3: The string held by the pathAndFileName variable. This is the path to the PDF file you convert to Excel. This variable is declared in line 4 above. In other words, this is the line of code that opens the program you're using. In this code example 1, this program is Adobe Reader. As I explain above, you use the pathname to specify the program to be executed and any relevant arguments or command-line switches. In this code example, pathname is set to be the string held by the shellPathName variable. The value held by the shellPathName variable is as determined in line 8 above. Therefore, shellPathName holds a string containing the paths and names of i the executed program Adobe Reader in example 1 and ii the PDF file to be converted to Excel Statistical Tables. You use the windowstyle argument for purposes of specifying the style of the window in which the relevant program is executed. In this case, I use the vbNormalFocus constant. The consequence of this is that the program Adobe Reader in this case , opens in a window that has i focus and ii its original size and position. Lines 12 And 16: Application. I also mention how there are several ways in which you can handle these timing problems. Since this VBA tutorial focuses on how to convert files from PDF to Excel instead of pausing or delaying VBA , I use a relatively simple construct for these purposes: The Application. Both of these lines of code 12 and 16 use the Application. Wait method for purposes of pausing the execution of the sample macro until a particular time. Wait has a single required argument: Time. This is the time at which you want execution of the macro to resume. TimeValue takes its string argument time , and returns a Variant Date that contains the time. When used together as in this case , the Now and TimeValue Functions return a time that is a certain amount of time in the future. In other words, execution of the macro is paused for 3 seconds. The consequence of this is that execution of the macro pauses for 30 seconds. The times I'm using here are for illustrative purposes only. The computers you work with may be faster or slower than the ones I've used while testing the sample macro. Inaccuracies in the amount of time you use as argument for the Application. Wait method can result in errors. You might be wondering why should you ensure that scrolling is enabled if the only thing you want to do is select all the text. Let me explain: The Select All command of Adobe Reader which I use in the following line 14 doesn't always select all the text in the open file. More particularly, if Single Page View is enabled, Select All usually only selects the contents of a single page. Therefore, in order to ensure that the Select All command given in line 14 selects all the text within the PDF file vs. Within Adobe Reader, this is the keyboard shortcut to select all. In Adobe Reader, this is the keyboard shortcut to copy. Lines 17 And 20: With myWorksheet End With These are the opening and closing statement of a With… End With block. The consequence of using the With… End With statement is that the series of statements within the block lines 18 and 19 below are executed on the object specified in the opening line of the block. In the example we're looking at, this means that both lines 18 and 19 below work with the object represented by myWorksheet. Range and the Range. Select for purposes of selecting cell B4 of the worksheet represented by the myWorksheet variable. The range you select in this line of code or its equivalent determines where the contents of the PDF file are pasted in Excel. As explained at , you can generally use this method to paste data from other applications. Note that the Worksheet. PasteSpecial method which we're using in this example is different from the Range. PasteSpecial method that I explain. The syntax of the Worksheet. PasteSpecial method is as follows: expression. This is myWorksheet in this case. You as a string. Note that none of the parameters of the Worksheet. PasteSpecial method allows you to select a destination for pasting. Since there's no such parameter, Worksheet. PasteSpecial pastes the Clipboard contents in the current selection. As a consequence of this, you must select the destination range prior to calling the PasteSpecial method. The TaskKill command, in turn, kills Adobe Reader. The windowstyle parameter of the Shell Function is set to vbHide. As a consequence of this, you only see the relevant application Adobe Reader in this case closing. This VBA Convert PDF to Excel Tutorial is accompanied by Excel workbooks containing the data and macros I use. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter. In most situations, this isn't the result that you want. However, if you're relying on Adobe Reader for purposes of converting a PDF file to Excel, you're unlikely to obtain better results. The reason for this is that, as a general rule, whenever you copy data from Acrobat Reader and paste it into Excel which is what this sample macro does , the data ends up in a single column. This is precisely what happens in the image above. The following sample macro solves this problem by using a PDF Converter: Able2Extract. You can try Able2Extract for free. The following image highlights the differences between both pieces of code: The differences between both macros can be reduced to the 2 that I explain in the following sections. As a consequence of the above, the VBA code behind both of these sample macros makes reference to different applications. This difference is reflected in 3 different places within the VBA code of the sample macros. Only 1 of these changes 3 has a material effect on the way the macro proceeds. This change isn't really material and I've done it for clarity purposes only. In fact, I could've used the same variable name for both macros. Generally speaking, what's more relevant is that you ensure compliance with the. Again, the change isn't really material. I've used different names because the sample workbook that accompanies this tutorial displays the results obtained when executing each macro in separate worksheets. In any case, the object you assign to the myWorksheet variable will, in the end, depend on the particular context you're in and how you want to design your VBA Sub procedure. Therefore, you're likely to modify this part of the code anyway. As I anticipate previously, this is the only of the 3 differences we're looking at here that has a material effect. In other words, you have flexibility when naming your variables and selecting the destination where you paste the data. However, you must ensure that you're making reference to the correct application when determining and structuring the pathname parameter of the Shell Function and the process name killed by the TaskKill command if required. The code examples in this blog post use alternative 2 process name. More precisely, the keyboard shortcuts you need to use when working with Adobe Reader differ slightly from those you need to use when using Able2Extract. Therefore, the VBA code must reflect these differences when using SendKeys. This keyboard shortcut enables scrolling. This shortcut selects all. This is the keyboard shortcut for the Copy command. When working with Able2Extract, you don't need to enable scrolling shortcut 1 above. However, as I show in the following section, the results obtained when using Able2Extract are significantly better to those obtained when relying on Adobe Reader. A particularly significant improvement is the fact that the macro that relies on Able2Extract is able to replicate the structure of the table in the original PDF document. The macro that uses Adobe Reader for these purposes generally pastes all the values in a single column. Furthermore, the macro that relies in Able2Extract is usually able to extract all the values from the source PDF document. Granted, there are situations where you may still need to do some data cleansing after converting the data from PDF to Excel using VBA and Able2Extract. A final advantage of Able2Extract is that you can use its variety of keyboard shortcuts when working with SendKeys. This gives you more flexibility for purposes of determining which is the exact portion of data you want to extract. Notice, for example, how you can use keyboard shortcuts to execute virtually all of the commands within the Edit and Document menus of Able2Extract: Now that we've covered the topic of converting PDF to Excel using SendKeys alongside Adobe Reader or Able2Extract, let's move on to the next way of convert PDF files to Excel: with a recent version of Microsoft Word. For these purposes, let's start by taking a look at… The Microsoft Word Object Model: A Quick Introduction Before you start reading the sections on how to use Word for PDF conversion purposes, please note that you need to have at least Word 2013 installed in your computer. The previous versions of Microsoft Word don't have the PDF Reflow feature that converts PDF files to editable Word documents. If you're using an older version of Microsoft Word, you can upgrade now at. I cover object models in several posts within Power Spreadsheets. As I explain in any of those posts, is important to have a good understanding of the object model of the application s you're working with. The main reason for this is that, whenever you're working with VBA, you're basically manipulating the objects of that application. Word isn't an exception to this general rule. Just as Excel, Outlook or the Ribbon Commander, Word has its own object model. Therefore, in this section, I provide a short introduction to this particular model. What Is The Word Object Model As a general rule, you can picture a VBA object model as being a hierarchy. This hierarchy contains you can use. Object model hierarchies have an object at the top. That object contains other objects. Those objects may, in turn, contain other objects. This process goes on until. Therefore, any object within the object model hierarchy has the potential of containing other objects. In any case, you eventually reach a point where a particular object doesn't hold any other objects. This basic idea is pretty much the same regardless of which particular VBA object model you're looking at. In other words, even though the exact components of the Word object model differ from those of the Excel object model, their structure follows the general rule I explain above. The object at the top of a VBA object model is usually known as Application. The Application object represents the relevant application. Since Word's Application object contains other objects, you can think of it as the root from which you access all the other objects of Word's object model. You generally obtain access to Word's top-level objects by appropriately using the properties of the Application object. Let's take a closer look at each of these: The Application. Documents Property, The Documents Collection And The Document Object The main purpose of is to return a Documents collection. The basic syntax of Application. Documents is as follows: expression. Documents property is a collection representing all the Document objects currently open. Each of those Document objects represents an individual document. For example, the following image shows 5 open Word documents. In this case, the Documents collection represents all of these documents Document1 through Document5. Each of the individual documents Document1, Document2, Document3, Document4 and Document5 is represented by an individual Document object. ActiveDocument Property The main purpose of is to return the Document object that represents the current active Word document. The basic syntax of Application. ActiveDocument follows that of the Application. Documents property above: expression. Automation, Early And Late Binding: An Introduction Automation is probably the most commonly used and the preferred tool for purposes for purposes of controlling one Windows application from another. Within the context of this blog post, the Server Application is Word. For purposes of this tutorial, the Client Application is Excel. Using Automation has several advantages over relying on the Shell Function and SendKeys. 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. In the words of author Richard Mansfield in Mastering VBA for Microsoft Office 2016 : 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. Once you've created the reference, the objects, properties and methods within that library are available while you're coding the relevant VBA application. Instead of this, you create an object that references that object library upon execution. 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. Once you've found it, select it click on the check box on the left side. The following screenshot is how the dialog box I'm working with looks like. I'm using Office 2016. Therefore, the label of the Microsoft Word Object Library refers to Microsoft Word 16. If you're using a different version, the version number changes. Once you've set the object reference, you can access the Word object model and use the objects, methods and properties it contains. Line 1: Dim myWorksheet As Worksheet Uses the Dim statement for purposes of declaring the myWorksheet object variable as a Worksheet. The purpose of the myWorksheet object variable is to represent the Excel worksheet where the data within the PDF file is pasted. This line of code is substantially the same as line 1 of the previous macro examples. Line 2: Dim wordApp As Word. Application Declares the wordApp object variable as of the type Word. 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. Line 3: Dim myWshShell As wshShell Declares the myWshShell object variable as of the type. 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. Lines 4 To 6: Dim pathAndFileName As String Dim registryKey As String Dim wordVersion As String These lines use the Dim statement to declare several variables pathAndFileName, registryKey and shellPathName as Strings. Line 4 Dim pathAndFileName As String is exactly the same as line 3 of the previous macro examples. Its purpose is to declare a variable that holds the path to the PDF file you want to convert to Excel. This variable holds a relatively long string see line 12 below. This variable appears in lines 13 and 17 below. In those lines, the RegWrite method is used to ensure that Word opens the PDF file you want to convert without displaying a dialog box warning you of the conversion. The purpose of wordVersion is to hold the version number of the Word version that you'll use when executing the macro. This value is assigned in line 11 below. It uses the Set statement to assign a particular worksheet Word Early Binding of the active workbook to the myWorksheet object variable. Application to the wordApp variable. Application or wshShell by using the New keyword. Application or New myWshShell to ii the variable to its left side wordApp or myWshShell. Version This line of code uses 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. In order for Word to proceed with the PDF to Word conversion, you must press the OK button within the displayed dialog box. 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. However, here's the deal: As explained by who proposes the solution I implement in this sample macro , 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. And this line 13 is the one that helps you solve this issue. Let's see how: The statement uses of the WshShell object. The basic syntax of RegWrite is as follows: object. In this line 13, this object is myWshShell. The string assignment for registryKey was made in the previous line 12. As explained by MVP Graham Mayor, the string assigned to strName is the value written to the registry. To specify a value-name as in the case we're analyzing , specify strName without a backlash at the end. Within line 13, this corresponds to 1. This strType corresponds to Integer. It allows you to specify a number in the form of an integer. The practical effect of this line of code 13 is, as explained , 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: wordApp. The statement calls the Documents. The basic syntax of Documents. Open is as follows: expression. Open FileName, ConfirmConversions, ReadOnly, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert,WritePasswordDocument, WritePasswordTemplate, Format, Encoding, Visible, OpenConflictDocument, OpenAndRepair,DocumentDirection, NoEncodingDialog A thorough description of the 16 parameters 15 of which are optional of Documents. Open exceeds the scope of this VBA tutorial. You can refer to for these purposes. Filename is the only required parameter of the Documents. You use it to specify the name of the document you want to open and add to the Documents collection. The value assigned to pathAndFileName in line 4 above corresponds to the path and file name of the PDF document you're converting to Excel. ConfirmConversions is an optional parameter. It allows you to determine whether the Convert File dialog box is displayed or not. This parameter can be set to True display or False don't display. In this code example, it's set to False. 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. Copy Uses for purposes of copying the content of the file you want to convert. The basic syntax of the Range. Copy method is as follows: expression. Within this line 18, this Range object is that returned by. 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. Lines 19 And 22: With myWorksheet End With These 2 lines of code are the opening and closing statements of a With… End With block. The practical effect of With… End With is that the series of statements within the block are executed on the same object. 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. Select 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… Line 21:. 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. The syntax of the Quit method is as follows: expression. In this case, expression is wordApp. You can set the SaveChanges parameter to any of the following 3 values from : Name Value Description wdDoNotSaveChanges 0 Don't save changes. In this case, I set SaveChanges to wdDoNotSaveChanges. Therefore, Word doesn't save the changes the conversion of the PDF file. 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. 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. When using late binding, you don't need to set up a reference to the Word Object Library or the Windows Script Host Object model. Let's take a look at the code changes: Lines 2 And 3: Dim wordApp As Object Dim myWshShell As Object 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. 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. For purposes of this example, these objects are the Word Application Word. Application and the Windows Shell WshShell. In both cases, this is achieved by using. 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. 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. Since the value 0 corresponds to wdDoNotSaveChanges, they're both equivalent. 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 4: PDF To Excel Using Microsoft Word: Example Results 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. Therefore, in the images below, I only show the results I obtain when executing the early binding macro version. At first glance these results seem to be very good. In particular, i the values seem to be appropriately extracted and ii the structure of the original file including its tables seems to be well replicated. However, upon scrolling down the file, some issues become evident. Check out, for example, the following screenshot and notice how the 2 rows I highlight. The following screenshot shows how this data looks in the original PDF file. Microsoft explains the reasons why Word may not accurately convert a particular PDF file. In such cases, the page or section may be transferred to Excel as an image. Despite the above weakness, relying on Microsoft Word for purposes of creating a macro that converts PDF to Excel has some advantages vs. Furthermore, by avoiding the Shell Function and SendKeys, you prevent the timing issues that are sometimes associated to their use. By now, you probably have a very good idea of the pros and cons of each of the different approaches I cover in this VBA tutorial. Therefore, is a good time to check out… PDF To Excel Using VBA: Which Approach To Use After learning about the different applications you can rely on for purposes of converting a PDF file to Excel Adobe Reader, Able2Extract and Word , you may rightly be wondering whether one is better than the others. In my opinion, each of the 3 approaches I explain in this tutorial has different advantages and disadvantages. Despite the above, in my experience, the results you obtain when relying on Adobe Reader are likely to be of lower quality and require more data cleansing than those obtained with Able2Extract or Word. In order to choose between Word and Able2Extract, I generally suggest that you consider the particular characteristics of the situation you're in. This question may seem relatively obvious, but the computer where your VBA application will be running needs to have installed the application you'll be using Able2Extract or a recent version of Microsoft Word for the macro examples I propose above to work. If the PDF files you're converting are particularly lengthy and complex, Able2Extract may provide better conversion results. As I explain above, Word is prone to inaccuracies when the PDF file has items such as i tables with cell spacing, ii footnotes that span more than 1 page, iii endnote, iv PDF comments, or v pages or sections that are mostly charts or other graphs. If you're constantly converting PDF files to Excel, you may find that the investment in a PDF converter such as Able2Extract is worth it. As I explain above, one of the advantages of relying on Microsoft Word, is that you can access its object model. In certain scenarios, this may give you a good amount of flexibility and control over how you handle the PDF to Excel conversion. Conclusion After reading this VBA tutorial, you know at least 3 ways in which you can convert PDF to Excel using VBA. This VBA Convert PDF to Excel Tutorial is accompanied by Excel workbooks containing the data and macros I use above. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter. For purposes of this VBA tutorial, I've excluded approaches that require relying on an API application programming interface. If you want and have the possibility , you can use an API for purposes of converting PDF files to Excel. Using an API certainly has advantages over some of the methods that I describe above. As I show above, Word's PDF Reflow feature works well in several scenarios. However, there are some situations where a more specialized PDF converter such as Able2Extract provides better and more accurate results. I may write about the topic of PDF to Excel conversion using APIs in future posts. For VBA code examples, check out the suggestions by VBA expert Christos Samaras at , exceljockey at , the code submitted by mohanvijay at or at the Adobe Forums. Books And Resources Referenced In This Excel VBA Tutorial Click on any of the links or images below to go to the official website of the software resource now. Some of these links are affiliate links, which means that if you choose to make a purchase, I will earn a commission. Click on any of the images below to purchase the book at Amazon now. I love reading and sharing success stories from amazing members of the Power Spreadsheets community, like you. If this or any other Tutorial has helped you, please share your success story below. This only takes few seconds and, by doing it, you help the future development of Power Spreadsheets. Any improvements I make to this or the other free Tutorials in Power Spreadsheets based on your feedback will benefit you too. I'd like to highlight your amazing work if there's a suitable opportunity. Therefore, I may publish your success story so other members of the Power Spreadsheets community can learn and be inspired by your success. If there's any data about you or your success story you don't want me to publish, please expressly specify this below.
For these purposes, vba excel tutorial pdf can use any of the 3 windowstyle values with focus vbNormalFocus, vbMinimizedFocus or vbMaximizedFocus. In order to proceed with the PDF to Word conversion, you must solve this. Parameter 9: FixedFormatExtClassPtr As explained atthe FixedFormatExtClassPtr parameter of the ExportAsFixedFormat method is a pointer to the FixedFormatExt class. All you need to do is download the training document, open it and start learning VBA for free. 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. The checkboxes are linked to their respective row in columns Z, AA and AB which is used to test for the state of the checkbox. It i takes the expression to its right side item 3 belowand ii assigns it to the variable on its left side item 1 above. However, in other situations, you may want to save each worksheet within a workbook in a separate file.
