Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Hello All,
I am using Windows XP/Office 2003 I am running this macro from excel (downloaded from newsgroup) which reads the information from Word Tables in Excel. Sub ImportDoc() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTable As Word.Table Dim wdRow As Word.Row Dim wdCell As Word.Cell Dim nRow, nCol, nTab Dim bHeader As Boolean Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") wdApp.Visible = True 'Call or insert your table copying code here Range("A1:E999").Clear nTab = 1 nRow = 1 For Each wdTable In wdDoc.Tables nTab = nTab + 1 bHeader = True For Each wdRow In wdTable.Rows nCol = 1 nRow = nRow + 1 For Each wdCell In wdRow.Cells nCol = nCol + 1 With ActiveSheet.Cells(nRow, nCol) .Select .Font.Bold = bHeader If bHeader Then .HorizontalAlignment = xlCenter Else .HorizontalAlignment = xlHAlignGeneral End If End With wdCell.Range.Copy ActiveSheet.Paste Next bHeader = False Next nRow = nRow + 1 Next wdApp.ActiveDocument.Close wdApp.Quit Set wdDoc = Nothing Set wdApp = Nothing End Sub I am facing the folloiwng problems with the above macro. When the macro runs 1) Word Document (Test.doc) is shown after about 10-15 second on the screen with the cursor blinking - I wish this not to be displayed 2) Excel file is displayed reading from the Word Table and inserting info in the cells of Excel...this process is very very slow.. It takes about two to two and a half minutes to insert a 40 Rows x 11 Columns information. 3) I have to select all rows and column and do Autofit manually for rows and columns - Can this be done automatically. Can anyone help me to speed up this please? TIA Rashid Khan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Untested...
'--- Sub ImportDoc_R1() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTable As Word.Table Dim wdRow As Word.Row Dim wdCell As Word.Cell Dim nRow As Long Dim nCol As Long Dim bHeader As Boolean Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") 'wdApp.Visible = True Application.ScreenUpdating = False ActiveSheet.Range("A1:E999").Clear nRow = 1 For Each wdTable In wdDoc.Tables bHeader = True For Each wdRow In wdTable.Rows nCol = 1 nRow = nRow + 1 For Each wdCell In wdRow.Cells nCol = nCol + 1 With ActiveSheet.Cells(nRow, nCol) .Font.Bold = bHeader If bHeader Then .HorizontalAlignment = xlCenter Else .HorizontalAlignment = xlHAlignGeneral End If wdCell.Range.Copy .Paste End With Next bHeader = False Next nRow = nRow + 1 Next Set wdTable = Nothing Set wdRow = Nothing Set wdCell = Nothing wdDoc.Close SaveChanges:=wdDoNotSaveChanges Set wdDoc = Nothing wdApp.Quit Set wdApp = Nothing Application.ScreenUpdating = True End Sub -- Jim Cone Portland, Oregon USA Special Sort add-in review: http://www.contextures.com/excel-sort-addin.html .. .. .. "prkhan56" wrote in message Hello All, I am using Windows XP/Office 2003 I am running this macro from excel (downloaded from newsgroup) which reads the information from Word Tables in Excel. -snip- I am facing the folloiwng problems with the above macro. When the macro runs 1) Word Document (Test.doc) is shown after about 10-15 second on the screen with the cursor blinking - I wish this not to be displayed 2) Excel file is displayed reading from the Word Table and inserting info in the cells of Excel...this process is very very slow.. It takes about two to two and a half minutes to insert a 40 Rows x 11 Columns information. 3) I have to select all rows and column and do Autofit manually for rows and columns - Can this be done automatically. Can anyone help me to speed up this please? TIA Rashid Khan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Hello Jim,
After running the macro I get the following error. Run time error 438 Object doesn't support this property. Thanks for your time On Aug 4, 6:37*pm, "Jim Cone" wrote: Untested... '--- Sub ImportDoc_R1() *Dim wdApp As Word.Application *Dim wdDoc As Word.Document *Dim wdTable As Word.Table *Dim wdRow As Word.Row *Dim wdCell As Word.Cell *Dim nRow As Long *Dim nCol As Long *Dim bHeader As Boolean *Set wdApp = New Word.Application *Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") 'wdApp.Visible = True *Application.ScreenUpdating = False *ActiveSheet.Range("A1:E999").Clear *nRow = 1 *For Each wdTable In wdDoc.Tables * *bHeader = True * *For Each wdRow In wdTable.Rows * * *nCol = 1 * * *nRow = nRow + 1 * * *For Each wdCell In wdRow.Cells * * * *nCol = nCol + 1 * * * *With ActiveSheet.Cells(nRow, nCol) * * * * *.Font.Bold = bHeader * * * * * If bHeader Then * * * * * * .HorizontalAlignment = xlCenter * * * * * Else * * * * * * .HorizontalAlignment = xlHAlignGeneral * * * * * End If * * * * * wdCell.Range.Copy * * * * *.Paste * * * *End With * * *Next * * *bHeader = False * *Next * *nRow = nRow + 1 *Next *Set wdTable = Nothing *Set wdRow = Nothing *Set wdCell = Nothing *wdDoc.Close SaveChanges:=wdDoNotSaveChanges *Set wdDoc = Nothing *wdApp.Quit *Set wdApp = Nothing *Application.ScreenUpdating = True End Sub -- Jim Cone Portland, Oregon *USA Special Sort add-in review: *http://www.contextures.com/excel-sort-addin.html . . . "prkhan56" wrote in message Hello All, I am using Windows XP/Office 2003 I am running this macro from excel (downloaded from newsgroup) which reads the information from Word Tables in Excel. -snip- I am facing the folloiwng problems with the above macro. When the macro runs 1) Word Document (Test.doc) is shown after about 10-15 second on the screen with the cursor blinking - I wish this not to be displayed 2) Excel file is displayed reading from the Word Table and inserting info in the cells of Excel...this process is very very slow.. It takes about two to two and a half minutes to insert a 40 Rows x 11 Columns information. 3) I have to select all rows and column and do Autofit manually for rows and columns - Can this be done automatically. Can anyone help me to speed up this please? TIA Rashid Khan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Update:
Change... wdDoc.Close SaveChanges:=wdDoNotSaveChanges To... wdDoc.Close SaveChanges:=0 '-- Jim Cone |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
At what line in the code does the error occur?
'-- Jim Cone |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
On Aug 4, 7:06*pm, "Jim Cone" wrote:
At what line in the code does the error occur? '-- Jim Cone I changed the line as per your suggestion. I stepped into the code (F8) I receive after .Paste on the following line wdCell.Range.Copy .Paste Thanks once again |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Yup...
Change .Paste to .PasteSpecial '-- Jim Cone "prkhan56" wrote in message ... On Aug 4, 7:06 pm, "Jim Cone" wrote: At what line in the code does the error occur? '-- Jim Cone I changed the line as per your suggestion. I stepped into the code (F8) I receive after .Paste on the following line wdCell.Range.Copy .Paste Thanks once again |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
On Aug 4, 8:19*pm, "Jim Cone" wrote:
Yup... Change .Paste *to *.PasteSpecial '-- Jim Cone "prkhan56" wrote in message ... On Aug 4, 7:06 pm, "Jim Cone" wrote: At what line in the code does the error occur? '-- Jim Cone I changed the line as per your suggestion. I stepped into the code (F8) I receive after .Paste on the following line wdCell.Range.Copy * * * * *.Paste Thanks once again I changed the code as per your instructions. The macro runs for 4-5 mins now and puts the Word Tables as Embedded Object (nearly 500 Objects) Have I done something wrong. Thanks once again |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
"Change .Paste to .PasteSpecial"
"I changed the code as per your instructions. The macro runs for 4-5 mins now and puts the Word Tables as Embedded Object (nearly 500 Objects) Have I done something wrong." '-- Well that is very interesting. PasteSpecial has an option that should fix the situation... Change.. ..PasteSpecial To... ..PasteSpecial xlPasteValues '-- Jim Cone |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
On Aug 4, 9:00*pm, "Jim Cone" wrote:
*"Change .Paste to .PasteSpecial" * * * "I changed the code as per your instructions. * * * *The macro runs for 4-5 mins now and puts the Word Tables as Embedded * * * *Object (nearly 500 Objects) * * * *Have I done something wrong." '-- Well that is very interesting. * PasteSpecial has an option that should fix the situation... Change.. .PasteSpecial To... .PasteSpecial xlPasteValues '-- Jim Cone Hi Jim, Thanks for revised code. It works (just under 2 minutes) but now many date values have changed eg 01/06/2010 changed to 06/01/2010 (I am using dd/mm/yyyy) format. Pls note that some date are shown properly...only some have changed. Can you see about this problem please? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
This could also be a little faster...
'--- Sub ImportDoc_R2(Optional ByRef bHeader As Boolean = True) Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTable As Word.Table Dim wdCell As Word.Cell Dim wdRow As Word.Row Dim lngAlign As Long Dim nRow As Long Dim nCol As Long Application.ScreenUpdating = False Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") With ActiveSheet.Range("A1:E999") .Clear .NumberFormat = "dd/mm/yyyy" .Font.Bold = bHeader If bHeader Then .HorizontalAlignment = xlCenter Else .HorizontalAlignment = xlHAlignGeneral End If End With nRow = 1 For Each wdTable In wdDoc.Tables For Each wdRow In wdTable.Rows nCol = 1 nRow = nRow + 1 For Each wdCell In wdRow.Cells nCol = nCol + 1 With ActiveSheet.Cells(nRow, nCol) wdCell.Range.Copy .PasteSpecial xlPasteValues End With Next bHeader = False Next nRow = nRow + 1 Next Set wdTable = Nothing Set wdRow = Nothing Set wdCell = Nothing wdDoc.Close SaveChanges:=0 Set wdDoc = Nothing wdApp.Quit Set wdApp = Nothing Application.ScreenUpdating = True End Sub -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware .. .. .. "prkhan56" wrote in message Hi Jim, Thanks for revised code. It works (just under 2 minutes) but now many date values have changed eg 01/06/2010 changed to 06/01/2010 (I am using dd/mm/yyyy) format. Pls note that some date are shown properly...only some have changed. Can you see about this problem please? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
On Aug 4, 10:49*pm, "Jim Cone" wrote:
This could also be a little faster... '--- Sub ImportDoc_R2(Optional ByRef bHeader As Boolean = True) *Dim wdApp As Word.Application *Dim wdDoc As Word.Document *Dim wdTable As Word.Table *Dim wdCell As Word.Cell *Dim wdRow As Word.Row *Dim lngAlign As Long *Dim nRow As Long *Dim nCol As Long *Application.ScreenUpdating = False *Set wdApp = New Word.Application *Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") *With ActiveSheet.Range("A1:E999") * * *.Clear * * *.NumberFormat = "dd/mm/yyyy" * * *.Font.Bold = bHeader * * * If bHeader Then * * * * *.HorizontalAlignment = xlCenter * * * Else * * * * *.HorizontalAlignment = xlHAlignGeneral * * * End If *End With *nRow = 1 *For Each wdTable In wdDoc.Tables * *For Each wdRow In wdTable.Rows * * *nCol = 1 * * *nRow = nRow + 1 * * *For Each wdCell In wdRow.Cells * * * *nCol = nCol + 1 * * * *With ActiveSheet.Cells(nRow, nCol) * * * * * wdCell.Range.Copy * * * * *.PasteSpecial xlPasteValues * * * *End With * * *Next * * *bHeader = False * *Next * *nRow = nRow + 1 *Next *Set wdTable = Nothing *Set wdRow = Nothing *Set wdCell = Nothing *wdDoc.Close SaveChanges:=0 *Set wdDoc = Nothing *wdApp.Quit *Set wdApp = Nothing *Application.ScreenUpdating = True End Sub -- Jim Cone Portland, Oregon *USAhttp://www.mediafire.com/PrimitiveSoftware . . . "prkhan56" wrote in message Hi Jim, Thanks for revised code. It works (just under 2 minutes) but now many date values have changed eg 01/06/2010 changed to 06/01/2010 (I am using dd/mm/yyyy) format. Pls note that some date are shown properly...only some have changed. Can you see about this problem please? Hi Jim I cant run this macro. I can't even see it under the Macro Name I think it has to with the line: Sub ImportDoc_R2(Optional ByRef bHeader As Boolean = True) Kindly guide me Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
Try this one...
'--- Sub ImportDoc_R3() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTable As Word.Table Dim wdRow As Word.Row Dim wdCell As Word.Cell Dim nRow As Long Dim nCol As Long Dim bHeader As Boolean Application.ScreenUpdating = False Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") With ActiveSheet.Range("A1:E999") .Clear .NumberFormat = "dd/mm/yyyy" End With nRow = 1 For Each wdTable In wdDoc.Tables bHeader = True For Each wdRow In wdTable.Rows nCol = 1 nRow = nRow + 1 For Each wdCell In wdRow.Cells nCol = nCol + 1 With ActiveSheet.Cells(nRow, nCol) .Font.Bold = bHeader If bHeader Then .HorizontalAlignment = xlCenter wdCell.Range.Copy .PasteSpecial xlPasteValues End With Next bHeader = False Next nRow = nRow + 1 Next Set wdTable = Nothing Set wdRow = Nothing Set wdCell = Nothing wdDoc.Close SaveChanges:=0 Set wdDoc = Nothing wdApp.Quit Set wdApp = Nothing Application.ScreenUpdating = True End Sub '--- "prkhan56" wrote in message Hi Jim I cant run this macro. I can't even see it under the Macro Name I think it has to with the line: Sub ImportDoc_R2(Optional ByRef bHeader As Boolean = True) Kindly guide me Thanks |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Word Table Macro running very slow
On Aug 5, 12:52*am, "Jim Cone" wrote:
Try this one... '--- Sub ImportDoc_R3() *Dim wdApp As Word.Application *Dim wdDoc As Word.Document *Dim wdTable As Word.Table *Dim wdRow As Word.Row *Dim wdCell As Word.Cell *Dim nRow As Long *Dim nCol As Long *Dim bHeader As Boolean *Application.ScreenUpdating = False *Set wdApp = New Word.Application *Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc") *With ActiveSheet.Range("A1:E999") * * *.Clear * * *.NumberFormat = "dd/mm/yyyy" *End With *nRow = 1 *For Each wdTable In wdDoc.Tables * *bHeader = True * *For Each wdRow In wdTable.Rows * * *nCol = 1 * * *nRow = nRow + 1 * * *For Each wdCell In wdRow.Cells * * * *nCol = nCol + 1 * * * *With ActiveSheet.Cells(nRow, nCol) * * * * *.Font.Bold = bHeader * * * * * If bHeader Then .HorizontalAlignment = xlCenter * * * * * wdCell.Range.Copy * * * * *.PasteSpecial xlPasteValues * * * *End With * * *Next * * *bHeader = False * *Next * *nRow = nRow + 1 *Next *Set wdTable = Nothing *Set wdRow = Nothing *Set wdCell = Nothing *wdDoc.Close SaveChanges:=0 *Set wdDoc = Nothing *wdApp.Quit *Set wdApp = Nothing *Application.ScreenUpdating = True End Sub '--- "prkhan56" wrote in message Hi Jim I cant run this macro. I can't even see it under the Macro Name I think it has to with the line: Sub ImportDoc_R2(Optional ByRef bHeader As Boolean = True) Kindly guide me Thanks Hi Jim Thanks once again for your help. The macro runs now but the "Date" column is still having mixed date format..,the original code which I posted did not had this problem. Also a slight problem has occurred because of the following code: With ActiveSheet.Range("A1:E999") .Clear .NumberFormat = "dd/mm/yyyy" End With The above code also changes value (which includes serial numbers) in the range A1:E999... I desire that only column with the Heading "Date" should retain its original format as in the word document. All other figures should be unchanged. Rashid Khan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Running very slow | Excel Programming | |||
Macro Running Painfully Slow! | Excel Discussion (Misc queries) | |||
Really Slow Running of Macro | Excel Programming | |||
MY EXEL/WORD OFFICE 2000 IS RUNNING VERY SLOW AFTER DOWNLOADING U. | Excel Discussion (Misc queries) | |||
slow down a running macro | Excel Programming |