Md. Shamim Reza
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.
📌 Steps
- 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.
Read More: How to Copy from Word to Excel into Multiple Cells (3 Ways)
2. Convert Word to Excel with VBA
You can do the same with Excel VBA. Follow the steps below to do that.
📌 Steps
- 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?
Sub WordToExcelWithFormatting()
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.
Document.Activate
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
Range.Select
Selects the range of data in the word document.
On Error Resume Next
Ignores if any error occurs and executes the next code line.
Word.Selection.Copy
Copies the selection in the word file.
ActiveSheet.Range(“B2”).Select
Selects the cell where the data will be imported. You can change this as required.
ActiveSheet.Paste
Pasting data copied from the word document onto the excel worksheet.
Document.Close
Word.Quit (wdDoNotSaveChanges)
Closes the word file without saving any changes.
Application.ScreenUpdating = True
Resets screen updating to its default value.
End Sub
Exits the subject procedure.
Read More: How to Convert Word Table to Excel Spreadsheet (6 Methods)
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.
Conclusion
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.
Does not work properly. All lineshifts in the Word document table converts to separate lines in the Excel – making it difficult and manual reformatting again required.
Hello Espen,
First of all, thanks for commenting. it’s unfortunate that we don’t explain the line-shifting issues in this article. If I am not wrong, I think you are talking about the case like the image below.
For resolving the issue you can follow the procedure described in the method of Converting Word to Excel without splitting cells.You will get the result like the image below.
Hopefully, you will understand the steps mentioned in the link and solve your issue.
Have a nice day!
Regards
Fahim Shahriyar Dipto
Excel & VBA Content Developer