How to convert a word document to excel without losing formatting

[JTH94BT]

I have a large word document that I need to convert to excel so that I can add formula and checks, however the document is very large and has very specific formatting as the document will be converted to PDF and uploaded to a website. I've tried simply copy and pasting the word pages in to excel but this seems messy and is quite time consuming considering the word doc is over 250 pages long. Can anyone help?

 

[Dan_W]

Convert a Word document into an Excel spreadsheet? I imagine it is very messy, because a page and spreadsheet are wildly different constructs - I can't even visualise what that should even look like.

What are you actually trying to do? When you say "so that I can add formula and checks" what does that mean? Like, an equation?

 

[JTH94BT]

Convert a Word document into an Excel spreadsheet? I imagine it is very messy, because a page and spreadsheet are wildly different constructs - I can't even visualise what that should even look like.

What are you actually trying to do? When you say "so that I can add formula and checks" what does that mean? Like, an equation?
View attachment 115994

Essentially, the word document is a pricing document with pages full of text and / or pricing tables throughout. This word document is updated during the year with new pricing, it is then converted to PDF before being uploaded to a website. The reason for wanting to convert to excel is to give myself the ability to add some formula to the pricing updates that are made throughout the year and automate these updates where possible.

Other than physically copy and pasting the information from word to excel, is there another way? I think at this stage my only solution is to copy, paste and format manually.

 

[Dan_W]

I'm still not understanding.

If the issue is:

1. the formatting of final document (and how "messy" it is); and,
2. the word document comprises "pages full of text"; and,
3. all you are doing is populating that text with figures calculated in Excel - namely, to give you the "ability to add some formula to the pricing updates that are made throughout the year and automate these updates where possible"

why are you not just taking the calculated figures from Excel and putting them into the Word document?

I must be missing something obvious. Long story shot - I'd really advise against copying and pasting a word document into Excel. I've never heard of such a thing. Tables - maybe, but not actual text.

 

[JTH94BT]

I'm still not understanding.

If the issue is:

1. the formatting of final document (and how "messy" it is); and,
2. the word document comprises "pages full of text"; and,
3. all you are doing is populating that text with figures calculated in Excel - namely, to give you the "ability to add some formula to the pricing updates that are made throughout the year and automate these updates where possible"

why are you not just taking the calculated figures from Excel and putting them into the Word document?

I must be missing something obvious. Long story shot - I'd really advise against copying and pasting a word document into Excel. I've never heard of such a thing. Tables - maybe, but not actual text.

I understand it's difficult to visualise what I'm trying to achieve here without showing you the actual documents. Currently I have to go through each page of the word document and calculate the new figures manually and then input the new figure in to the word document before converting the word document in to PDF which is then uploaded to our website. What I want to do is hold the entire document in excel, with each tab representing a page of the word document where I can add calculation steps to automate the price updates and then convert the excel doc in to PDF which is then uploaded to our website. I have experimented with simply copy and pasting each page of the word document in to excel, but in doing so, the formatting goes funny, however I think there is no easy solution to copying all of the data from word in to excel whilst maintaining the formatting. By holding this information in excel, it allows me to automate pricing updates throughout the document whilst reducing the risk of human error. Thanks for your response, however I think I have a tedious job of copy, pasting and formatting manually ahead of me.

 

[arthurbr]

Perhaps have a look at Extracting data from Word (.docx) files into Power Query, although it seems complicated to me

 

[Dan_W]

I understand it's difficult to visualise what I'm trying to achieve here without showing you the actual documents. Currently I have to go through each page of the word document and calculate the new figures manually and then input the new figure in to the word document before converting the word document in to PDF which is then uploaded to our website. What I want to do is hold the entire document in excel, with each tab representing a page of the word document where I can add calculation steps to automate the price updates and then convert the excel doc in to PDF which is then uploaded to our website. I have experimented with simply copy and pasting each page of the word document in to excel, but in doing so, the formatting goes funny, however I think there is no easy solution to copying all of the data from word in to excel whilst maintaining the formatting. By holding this information in excel, it allows me to automate pricing updates throughout the document whilst reducing the risk of human error. Thanks for your response, however I think I have a tedious job of copy, pasting and formatting manually ahead of me.

You don't need to convert the word document (or pages thereof) into a spreadsheet in order to get access to the content of that word document. You can control Word from Excel.

For example, the following script - running from Excel - will create an instance of Word, open up designated word document, extract the text from that word document, and deposit at most the first 1000 characters of that text in to the Activecell.

Sub ExcelControlsWord()

    Dim WordApplication As Object
    Set WordApplication = CreateObject("Word.Application")
    
    Dim Filename As String
    Filename = "C:\PATHTOMYFILE\MyFileName.docx"
    
    Dim WordDocument As Object
    Set WordDocument = WordApplication.Documents.Open(Filename)
    
    Dim TargetRange As Object
    Set TargetRange = WordDocument.Range
    
    Dim FullText As String
    FullText = TargetRange.Text
    
    ActiveCell.Value = Left(FullText, 1000)
    
    WordDocument.Close
    
    WordApplication.Quit
    
    Set WordApplication = Nothing

End Sub

It's a pretty basic example, but the point is that you can control Word, from Excel, to parse the text of the document without having to convert it into some kind of spreadsheet; complete your calculations etc; put the values back into the nicely formatted word document, and then convert that to a PDF. I hope that makes sense.

 

[JTH94BT]

You don't need to convert the word document (or pages thereof) into a spreadsheet in order to get access to the content of that word document. You can control Word from Excel.

For example, the following script - running from Excel - will create an instance of Word, open up designated word document, extract the text from that word document, and deposit at most the first 1000 characters of that text in to the Activecell.

Sub ExcelControlsWord()

    Dim WordApplication As Object
    Set WordApplication = CreateObject("Word.Application")
   
    Dim Filename As String
    Filename = "C:\PATHTOMYFILE\MyFileName.docx"
   
    Dim WordDocument As Object
    Set WordDocument = WordApplication.Documents.Open(Filename)
   
    Dim TargetRange As Object
    Set TargetRange = WordDocument.Range
   
    Dim FullText As String
    FullText = TargetRange.Text
   
    ActiveCell.Value = Left(FullText, 1000)
   
    WordDocument.Close
   
    WordApplication.Quit
   
    Set WordApplication = Nothing

End Sub

It's a pretty basic example, but the point is that you can control Word, from Excel, to parse the text of the document without having to convert it into some kind of spreadsheet; complete your calculations etc; put the values back into the nicely formatted word document, and then convert that to a PDF. I hope that makes sense.

Thank you Dan I will give this a shot!

 

[arthurbr]

A small acknowledgement of my suggestion, even if it's useless, would have been nice...

 

[JTH94BT]

A small acknowledgement of my suggestion, even if it's useless, would have been nice...

My apologies Arthur thank you for suggesting using Power Query!