This article illustrates how you can convert word documents to excel spreadsheets but keep the same formatting. Suppose you need to format or sort your data that is in a word document. Obviously, excel is the better choice to do that. Fortunately, you don’t have to type the data again in the new excel file. You can just convert the word document to an excel spreadsheet while keeping the formatting. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that easily.
Download Practice Workbook
You can download the practice workbook from the download button below.
2 Ways to Convert Word to Excel with the Same Formatting
Imagine you have the following word document. Now you want to convert it to an excel spreadsheet. Then follow the methods below.
1. Convert Word to Excel with Copy-Paste
You can simply copy the data from the word document and paste it onto the excel sheet. Follow the steps below to do that.
- First, go to the word file. Then, press CTRL+A to select the entire document. You can also select a particular range only if required.
- After that, press CTRL+C to copy the data as shown below.
- Next, go to the excel spreadsheet. Then, select the upper-left cell of the range where you want to get the data.
- Now, press CTRL+V to paste the data. Alternatively, you can right-click on that cell. Then, select Keep Source Formatting (K) from the Paste Options.
- After that, you will get the following result.
2. Convert Word to Excel with VBA
You can do the same with Excel VBA. Follow the steps below to do that.
- First, add a new worksheet. Then, save the workbook as a macro-enabled workbook.
- Next, press ALT+F11 to open the VBA window.
- Then, select Insert >> Module to create a new module as shown in the picture below.
- After that, copy the following code using the copy button.
Sub WordToExcelWithFormatting() Dim Document, Word As Object Dim File As Variant Dim PG, Range Application.ScreenUpdating = False File = Application.GetOpenFilename _ ("Word file(*.doc;*.docx) ,*.doc;*.docx", , "ExcelDemy.Com - Please Select") If File = False Then Exit Sub Set Word = CreateObject("Word.Application") Set Document = Word.Documents.Open(Filename:=File, ReadOnly:=True) Document.Activate PG = Document.Paragraphs.Count Set Range = Document.Range(Start:=Document.Paragraphs(1).Range.Start, _ End:=Document.Paragraphs(PG).Range.End) Range.Select On Error Resume Next Word.Selection.Copy ActiveSheet.Range("B2").Select ActiveSheet.Paste Document.Close Word.Quit (wdDoNotSaveChanges) Application.ScreenUpdating = True End Sub
- Then, paste the copied code onto the module window as shown below.
- Now, press F5 to run the code. You can also do that from the Run
- After that, you will be asked to select the word file that you want to convert.
- Now, browse to the location of your desired word document. Then, select the file and click Open.
- Finally, you will get a similar result as in the earlier method.
🔎 How Does the Code Work?
We will write the code inside this subject procedure.
Dim Document, Word As Object
Dim File As Variant
Dim PG, Range
Declaring necessary variables.
Application.ScreenUpdating = False
This makes VBA work in the background. As a result, it works faster.
File = Application.GetOpenFilename _
(“Word file(*.doc;*.docx) ,*.doc;*.docx”, , “ExcelDemy.Com – Please Select”)
This allows the user to input the word document s/he wants to convert.
If File = False Then Exit Sub
The subject procedure ends if no input is given.
Set Word = CreateObject(“Word.Application”)
This sets the Word variable as a word document.
Set Document = Word.Documents.Open(Filename:=File, ReadOnly:=True)
This assigns the Document variable to the object or file referred to by the user.
The referred document becomes activated.
PG = Document.Paragraphs.Count
This code line assigns the PG variable to the number of paragraphs in the word document
Set Range = Document.Range(Start:=Document.Paragraphs(1).Range.Start, _ End:=Document.Paragraphs(PG).Range.End)
This code is for detecting the entire range of the word document
Selects the range of data in the word document.
On Error Resume Next
Ignores if any error occurs and executes the next code line.
Copies the selection in the word file.
Selects the cell where the data will be imported. You can change this as required.
Pasting data copied from the word document onto the excel worksheet.
Closes the word file without saving any changes.
Application.ScreenUpdating = True
Resets screen updating to its default value.
Exits the subject procedure.
Things to Remember
- You can also save the word file as a PDF. Then, use your PDF editor to convert it to an Excel Spreadsheet.
- Don’t forget to save the workbook as a .xlsm Otherwise, you will lose the code.
Now, you know how to convert a word document to an excel spreadsheet and to keep the formatting as well. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.