Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copying Tables from Word and Pasting them to Cells in Excel

  Asked By: Egidius    Date: Nov 12    Category: MS Office    Views: 1171
  

I'm trying to copy Tables from Word to Excel

I found a way with this:

Set ExcelSheet = CreateObject("Excel.sheet")

[and in a 'For .. Next' block...]

ActiveDocument.Tables(TableCount).Select
Selection.Copy
ExcelSheet.Application.Cells(RowNumber,ColumnNumber).Select
ExcelSheet.Application.ActiveSheet.Paste

This is working but too slow. Because when it does copy the tables,user can
see the whole selecting,copying an pasting processes on the screen and it takes
time to show it to user.

Then i tried to add this to the code:

ExcelSheet.Application.Visible = False

[and]

ThisDocument.Application.Visible = False

Then when Program starts,user can't see any window but it's still too slow.

Is there any other solution?

In Word, Table object has Range object and Range object has Cell object. In
the Watch window, when i add "ThisDocument.Tables(TableCount).Range.Cells" i
figured out that is very similar(not totatly!) to the Range Object of Excel. But
i can't do something like that:

Dim RngObj as Range
Dim TblObj as Table
Set RngObj=TblObj.Range

This causes a 'Type Mismatch Error' and still i can't find any other solution
except the previous "old grandpa method" => Copy&Paste...

Any Ideas?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Bobbie Gonzalez     Answered On: Nov 12

Try

Application.ScreenUpdating = False

It might speed it up.

It might also be that you can get away without using the select statements and
that this might speed it up:


ActiveDocument.Tables(TableCount).Copy
ExcelSheet.Application.Cells(RowNumber,ColumnNumber).Paste

If you're not doing the selects, the screens might not be redrawn.

 
Answer #2    Answered By: Jerrah Brown     Answered On: Nov 12

One alternative, which might speed things up, would be to use an array as an
intermediary. That is have nested for/next loops to copy  the table  to the
array and then another set  of loops to copy from the array to the spreadsheet.

The reason I think this could be quicker is that the macro is only dealing
with one application  at a time rather than jumping from one to the other.

Another idea would be to use copy and paste special with the whole table. If
that achieves what you want manually you could do the same in code.

 
Answer #3    Answered By: Brandeis Fischer     Answered On: Nov 12

I will try this solution too. But i'll take time because i can't change the
algorithm structure so easily.

Anyway,i will report the result.

 




Tagged: