Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

How can i transpose translations from Word to Excel?

Hi
I have got a Word document which contains, amongst other things, tables which contain an english master copy and its translation in e.g. german. I then have an excel spreadsheet with those same english master copy phrases and I need to get the german translations into the Excel Spreadsheet. So something like:
Find first table in Word Document
Take first German phrase
Copy associated english translation
Go to Excel spreadsheet
Find english translation (there are multiple worksheets)
Paste German version into relevant cell
Go back to Word doc and start again.

How can I do this?
thanks
0
lz7cjc
Asked:
lz7cjc
  • 19
  • 13
1 Solution
 
SiddharthRoutCommented:
A sample would have helped for a faster solution...

Sid
0
 
lz7cjcAuthor Commented:
ok will upload something shortly!
0
 
lz7cjcAuthor Commented:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lz7cjcAuthor Commented:
I have edited down a few screens of the source and destination file - assume once it starts to loop it will be easy enough to go through a far extended file
the final destination file has around 40 worksheets and the source files vary from around 7mb to 20mb

thanks
0
 
SiddharthRoutCommented:
Ok here is the logic. It is already 7:33 AM here and I am bushed. Will work on it after I get up.

Logic:

I have partially achieved some of them and have indicated it below.

1) Add a Temp sheet [done]
2) Open Word doc from excel and then import the tables from word doc to Excel. [done]
3) Close Word Doc [done]
4) Loop through the sheets and use .Find to find the values and insert them in the respective sheets from the Temp sheet OR insert Vlookup formulas in every cell of your worksheets to pull up data from the temp sheet. Once done, convert them to values. [Pending]
5) Delete the Temp Sheet [Pending]

Here is the code that I have used.

Give it a try if you want to do this on your own. :)

Simply paste this code in a module and run it. Also do remember to change the path of the word document in the code below.

Sid

Code Used

'~~> Path of word document file
Const docPath As String = "C:\!ee\sample-file-1.Docx"

Sub ImportTable()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim oWordApp As Object, oWordDoc As Object
    Dim lastrow As Long, i As Long
    
    'Set ws1 = Sheets("Screen5")
    Set ws2 = Sheets.Add
    
    lastrow = 1
    
    '~~> Establish an Word application object
    On Error Resume Next
    Set oWordApp = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
        Set oWordApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oWordApp.Visible = False
    
    Set oWordDoc = oWordApp.Documents.Open(docPath)
    
    '~~> Copy Tables
    For i = 1 To oWordDoc.Tables.Count
        oWordDoc.Tables(i).Range.Copy
        ws2.Range("A" & lastrow).PasteSpecial xlPasteValues
        lastrow = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
    Next i
    
    '~~> Close Word
    oWordDoc.Close savechanges:=False
    
    '~~> Clean Up
    Set oWordDoc = Nothing
    oWordApp.Quit
    Set oWordApp = Nothing
End Sub

Open in new window

0
 
lz7cjcAuthor Commented:
fantastic - i am not sure how i would loop through each worksheet then find but will have a play
worst case hopefully you can finish off once you have gotten some rest!

at least I now know it is possible!

thanks
0
 
SiddharthRoutCommented:
at least I now know it is possible!

It is definitely possible :)

All we now need to do is use a loop like shown below to loop through the worksheets and then do the needful as I mentioned above.

Sub Sample()
    Dim oSheet As Worksheet
    
    For Each oSheet In ThisWorkbook.Sheets
        '~~> So that it ignores the temp sheet
        If InStr(1, oSheet.Name, "Screen", vbTextCompare) Then
            '~~> Do Stuff here
        End If
    Next
End Sub

Open in new window


Will get back to this if someone else has not already posted  solution ;)

Sid
0
 
lz7cjcAuthor Commented:
on the whole the import works well - however it doesn't seem to like bullets, carriage returns, very long text or '/' within the Word document - sends the formatting all over the place

0
 
lz7cjcAuthor Commented:
(and other special characters!)
basically we need to just look at the contents of the cell in the word doc and move the whole thing
0
 
lz7cjcAuthor Commented:
help!!! I am stuck; can't get any further on this
thanks
0
 
SiddharthRoutCommented:
Sorry I was kinda busy so couldn't work on it. On to it right now.

Sid
0
 
lz7cjcAuthor Commented:
no problem - all help gratefully received!
0
 
SiddharthRoutCommented:
>>on the whole the import works well - however it doesn't seem to like bullets, carriage returns, very long text or '/' within the Word document - sends the formatting all over the place

Yeah you are right.

Could you run the sub sample and post the workbook back with the results? Just want to check something. Do remember to change the path for the word doc.

Sid
sample-1.xlsm
0
 
lz7cjcAuthor Commented:
do you need the source doc? I have amended to include text with bullets and carriage returns?
0
 
SiddharthRoutCommented:
Yup the actual doc would be great.

Sid
0
 
lz7cjcAuthor Commented:
OK- have attached both just in case sample-1--with-results.xlsx sample-doc.docx
0
 
SiddharthRoutCommented:
Just to let you know that data successfully copied over .

Sid
0
 
SiddharthRoutCommented:
Now the last thing.

I am unable to comprehend on which text should go where...

I am uploading the file. Run and see if your table is getting updated correctly (it does here though) and then fill one sample sheet for me. Also color the cells red which needs to be filled up.

Sid
sample-1.xlsm
0
 
lz7cjcAuthor Commented:
wow - amazing!

the text in column D of the temp sheet needs to be copied to column J of the other sheets if their is an exact match of the english in the temp sheet column C to the column C of each worksheet.

Does that make sense?
0
 
lz7cjcAuthor Commented:
sorry - didn't see the colour coding bit!

here are two sample files
green is the text to match - red is where the text needs to go
nb there are multiple worksheets - in my actual files there are over 40
sample-1-v2.xlsm
sample-file-1.docx
0
 
SiddharthRoutCommented:
Try this

Sid
sample-1.xlsm
0
 
lz7cjcAuthor Commented:
get an error about data on clipboard is not same shape as destination cell... then something else about cannot paste into a merged cell

will get back to you with details shortly but wanted to let you know i was trying it!
0
 
lz7cjcAuthor Commented:
data on clipboard is not the same size as the selected area. Do you want to paste anyway?

I tried ok and cancel and both times got
runtime error 1004 cannot change part of a merged cell
then when i debug it goes to line
ActiveSheet.Paste

in
'~~> Copy Tables
    For i = 1 To oWordDoc.Tables.Count
        oWordDoc.Tables(i).Range.Copy
        ws2.Activate
        ws2.Cells(lastrow, 1).Select
        ActiveSheet.Paste
        lastrow = ws2.Range("B" & Rows.Count).End(xlUp).Row + 1
    Next i

if i say no then
0
 
SiddharthRoutCommented:
I need to see the actual word file to debug that error as it is working just fine with the sample file that you gave.

Sid
0
 
lz7cjcAuthor Commented:
by the way i have to choose one of four modules to run
i am choosing the last one - module2.sample
0
 
SiddharthRoutCommented:
4?

There are only 2 there. Yes, "sample" is the right one.

Sid
0
 
lz7cjcAuthor Commented:
ok - will match up the file with what you sent me and get back to you - will do it in the morning as is late here - thanks so much for all your help; really appreciate it...can i award more than 500 points for this?

Nick
0
 
SiddharthRoutCommented:
Ha ha ha. No you cannot but thanks for the thought. ;-)

Sid
0
 
lz7cjcAuthor Commented:
ok - looks like this is where it is failing - attached subset of where it looks like the import doesn't go quite right wt1.docx
0
 
lz7cjcAuthor Commented:
have got it working but only by removing a load of text from the full document which crashes the script due to merged cells - i have all the content that is crashing it but am hoping hte sample i have provided will work for all the conditions as it is basically the same thing - bullets and paragraphs

thanks - so close!
0
 
SiddharthRoutCommented:
Try this now :)

Sid
sample-1.xlsm
0
 
lz7cjcAuthor Commented:
Amazing - you have been incredibly helpful - thank you so much

everything works as desired :)
Nick
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 19
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now