Solved

How can i transpose translations from Word to Excel?

Posted on 2011-02-22
32
311 Views
Last Modified: 2012-05-11
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
Comment
Question by:lz7cjc
  • 19
  • 13
32 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34957227
A sample would have helped for a faster solution...

Sid
0
 

Author Comment

by:lz7cjc
ID: 34957244
ok will upload something shortly!
0
 

Author Comment

by:lz7cjc
ID: 34957358
0
 

Author Comment

by:lz7cjc
ID: 34957365
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34957472
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
 

Author Comment

by:lz7cjc
ID: 34957495
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34957505
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
 

Author Comment

by:lz7cjc
ID: 34957541
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
 

Author Comment

by:lz7cjc
ID: 34957653
(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
 

Author Comment

by:lz7cjc
ID: 34966341
help!!! I am stuck; can't get any further on this
thanks
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966369
Sorry I was kinda busy so couldn't work on it. On to it right now.

Sid
0
 

Author Comment

by:lz7cjc
ID: 34966411
no problem - all help gratefully received!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966608
>>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
 

Author Comment

by:lz7cjc
ID: 34966756
do you need the source doc? I have amended to include text with bullets and carriage returns?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966762
Yup the actual doc would be great.

Sid
0
 

Author Comment

by:lz7cjc
ID: 34966771
OK- have attached both just in case sample-1--with-results.xlsx sample-doc.docx
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966912
Just to let you know that data successfully copied over .

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34966957
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
 

Author Comment

by:lz7cjc
ID: 34967464
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
 

Author Comment

by:lz7cjc
ID: 34968668
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34969745
Try this

Sid
sample-1.xlsm
0
 

Author Comment

by:lz7cjc
ID: 34970947
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
 

Author Comment

by:lz7cjc
ID: 34971118
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34971147
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
 

Author Comment

by:lz7cjc
ID: 34971160
by the way i have to choose one of four modules to run
i am choosing the last one - module2.sample
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34971177
4?

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

Sid
0
 

Author Comment

by:lz7cjc
ID: 34971256
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34971267
Ha ha ha. No you cannot but thanks for the thought. ;-)

Sid
0
 

Author Comment

by:lz7cjc
ID: 34976473
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
 

Author Comment

by:lz7cjc
ID: 34976693
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
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 34978797
Try this now :)

Sid
sample-1.xlsm
0
 

Author Closing Comment

by:lz7cjc
ID: 34985487
Amazing - you have been incredibly helpful - thank you so much

everything works as desired :)
Nick
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now