How to Export Excel Data to Word via VBA

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

Assign File name Parameters and Variables

Step 2 – Specify the Error handler

After that, we will specify that if any error occurs, the macro will end.

Insert an error handler

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.

Enter a conditional statement

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.

Insert Append Data keyword for saving file

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

Indicate the .doc format for saving file

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.

Enter a function to specify return function

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.

Click to save the export file

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.

Insert a separator to add between column values

Scripts can be found here:

http://howtech.tv/wp-content/images/004610/130715_9_Script_01.txt

http://howtech.tv/wp-content/images/004610/130715_9_Script_02.txt