In this tutorial we will guide you on how to export Excel data to word using VBA.
For this tutorial, we will be working with this sheet over here. What we’re going to do is create a macro that allows the user to specify a separator and then export using VBA from Excel to Word file. In order to Export Excel Data, we will use some objects with multiple combinations and data range to export into a new Word Document. If Word document is already existed then the selected data will be added at the end of the existing document instead of creating a new one.
Step 1– Assign File name Parameters and Variables
To copy data using VBA from excel to word, let’s open up the VBA editor window and add a new module here.
Once done, we will start off by assigning the parameters for “File name” which is to be saved, “Separator” which will be used to detach the data in columns and the range of cells to be exported.
Now we are going to assign all the variables that are going to be used in the code
Step 2 – Specify the Error handler
After that, we will specify that if any error occurs, the macro will end.
Step 3 – Insert a Conditional Statement
Next, we will insert a conditional statement that if the cells are selected then it will only export the selection in to a word document and if nothing is selected then the entire sheet will be copied. That is why we have used the Selection Only keyword here.
Step 4 – Using Append Data keyword to create a new Word File or Use existing one
After that, we will specify a condition that will copy the data at the end of the word document if it already exists, otherwise a new word file will be created. For that, we will be using the Append Data keyword here.
Step 5 – Specify the Word Document format
Now close the statement by putting the ending statements and let’s insert a new module.
In the new module, we will assign variables for File name and the separator and insert the code to save the file using the Microsoft word format here
Step 6 – Insert a return function when click on Cancel
If the user clicks on the Cancel button then the code will stop itself and return to the sheet otherwise it will prompt you to enter a separator which is used to separate the values in columns once the data is been copied.
Once done, let’s put the ending statements and go back to Excel Sheet.
Step 7 – Saving the File
Now let’s run the code. Great! It is asking us to choose a location for the file. Notice that below, it is saving the file in Word Format. Let’s browse and select a location and click on Save.
Step 8 – Enter the Separator
After that, it will give us a prompt or the separator value. For now, let’s insert a dash ( – ) and hit enter.
With that done, let’s go to the place where we saved the file and open up the word document.
With that done you will see that we have successfully exported excel data to word through visual basic.
Scripts can be found here:
https://howtech.tv/wp-content/images/004610/130715_9_Script_01.txt
https://howtech.tv/wp-content/images/004610/130715_9_Script_02.txt
Home