Slow Macro...Takes at least 1 Hour to Run

[2sherberts]

Hello All,

I need some help with my macro. My macro currently takes about an hour
to run, most of the time it doesn't finish. I can't seem to understand
why it is so ridiculously slow.

 

[Gary Keramidas]

post the code.

 

[2sherberts]

Okay, I didn't get to finish.....(I'm really new to this, so please
help),

I'm trying to pull a lot of different graphs and data from 8 different
excel files and paste them into word. This process is crawling and I
know it should take seconds to do. Can anyone tell me why it is so?

Here is a small portion of what I have created:


myspreadsheet.worksheets("sheet1").chartobject(1).chart.chartarea.copy
Selection.GoTo what:=wdGoToBookmark, name:="bookmarkname"
Selection.Delete
Selection.PasteSpecial Link:=False,
DataType:=wdPasteEnahncedMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False

myspreadsheet.worksheets("Sheet").range("rangename").copy
Selection.GoTo What:=wdGoToBookmark, Name:="NameofBookMark"
Selection.Delete
Selection.PasteSpecial Link:=False,
DataType:=wdPasteEnhancedMetafilePicture, _
Placement:=wdInLine, DisplayAsIcon:=False

1) The first command is just for 1 graph and is repeated for 50 other
graphs. The second is for data tables. The excel files that the macro
is extracting the Graphs and Data from are large, could this be a
reason why the macro is running so slow?

2) Sometimes I run into an error: "Disk is Full" and other times it is
not. I've increased my virtual memory and I think I have sufficient
virtual memory, but I still get this prompt. I guess I can just add
more.

3) Also, some of the charts, when pasted, are abnormally large. When
running the macro again, it would be the exact size that I want.

Can someone please help me with my problem. I'm so lost.......

 

[2sherberts]

post the code.

Gary,


Sorry, I didn't mean to sound rude. I was in the process of posting my
next message when you responded and didn't see your response until I
finished.

 

[Per Jessen]

Hi

Are you running the code from Word or Excel ?

Are the workbooks open or closed ? If closed, how do you pull the data from
the workbook.

Regards,

Per

 

[2sherberts]

Hi Per,

I'm running the from Word and all of the workbooks are open.

 

[Nigel]

I just spent ages trying to speed up the copy-paste 'Picture' method which
was so slow (and that was between two Excel workbooks). I needed to
decouple the workbook data linked to a chart hence the use of the 'Picture'
method. Eight charts took about 30 seconds.

In the end I created new charts using array data, that is effectively stored
with the chart, making it portable. Reduced the time taken to less than 2
seconds. Maybe you could consider this option?

 

[Per Jessen]

Hi

Sorry, I am not so familiar with VBA in Word, try the Word group.

 

[SteveM]

Hi

Sorry, I am not so familiar with VBA in Word, try the Word group.

When I read many of the posts on this site, I realize that the OP is
looking to solve a mechanical problem in Excel, when a look at
alternative solutions may suggest a way of generating the desired
outputs much more cleanly.

But about the mechanical Excel questions:

If report generation is recurrent and the data sources are static, why
are you not pasting the Excel data as links so that external data
always maps correctly into the report without the need for the macro?

Regarding alternative solutions:

If the Word document is a regular report, can you generate an Excel
report instead? If the report is for internal consumption then the
more elaborate formatting that Word offers should be relatively
unimportant.

Here's another observation that takes some effort to adopt. Which is
transitioning to an Access data base for data management. Managing
eight workbooks can be tedious but managing 8 data tables is easy.
And Access dynamic queries allow you always keep data synchronized in
a transparent way. Again, it the report has an internal audience an
Access report should be sufficient. I see posters seeking advice for
Excel workarounds to problems that can be much more simply managed
with a data base solution. I sometimes wish they would consider this
alternative tool.

I know that the OP is just starting out in Excel, but sometimes a fix
further upstream is a better solution.

SteveM

 

[serge T]

I recently experienced such problem of slow macro , but within excel only.
You can cancel the screenupdating (put it on false), but in my case, it was
due to the option "page break preview". Once in normal view, it was running
in 55 seconds instead of 6 minutes...

 

[2sherberts]

Hi Steve,

Thanks for the response. My superior doesn't want me to paste link the
charts and prefer a macro for this function. So paste link is out of
the question for me.

Also, this report will be distributed out to the Board of Directors
and Execs. so everything has to be perfect and the format is rigid.

And the workbooks that I'm working with are models and I don't think I
have the option to manage the data in access. I'm not saying that it
is impossible, I'm saying I'm not allowed. But, I will study up on
this suggestions for other projects that I have more flexability with.
Thanks.


Serge,

Thanks, I'll try that as well.