This one's a good challenge - slow down a macro!

richardff · Apr 4th, 2002, 08:09 AM

Oh where do I start with this!

I have a macro whose sole purpose is to open another workbook, select and copy a row of data and paste it into one central workbook. That's all it does - in all, the macro does this routine for about 150 separate workbooks (and puts the rows of data all into the one central workbook).

My problem is this: Each of those 150 workbooks has links to another application and when each workbook is opened it takes a second or two for the links to update.

Unfortunatley, my Macro is too quick and copies the row of data before it has had a chance to update (so it just copies a row of #N/A's and no data!!!)

Ideally, I need my macro to open the workbook and then pause for say 3 seconds (so the workbook can have a chance to update its links) and THEN copy the data and carry on with the rest of the routine?

Is there some sort of timer function or a pause and resume command for a macro?

Anyone have any advice or suggestions to get round this problem?

Mark O'Brien · Apr 4th, 2002, 08:13 AM

Have a look at VBA help for "Wait", the example is pretty much what you're looking for. (I don't have Excel to bang something out for you today, sorry)

150 times 3 seconds is 450 secs, that's a long time.

Dave Hawley · Apr 4th, 2002, 08:19 AM

Hi Richard

The OnTime Method should help, try

Code:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "CopyIt"
End Sub

Where "CopyIt" is your macro.

richardff · Apr 4th, 2002, 08:23 AM

Thanks Mark - I was searching in VBA help for "pause" "delay" "suspend" and everything else under the sun - except wait!!!

Cheers!

Apr 4th, 2002, 08:13 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Have a look at VBA help for "Wait", the example is pretty much what you're looking for. (I don't have Excel to bang something out for you today, sorry) 150 times 3 seconds is 450 secs, that's a long time. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Apr 4th, 2002, 08:19 AM	#3
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Richard The OnTime Method should help, try Code: Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:05"), "CopyIt" End Sub Where "CopyIt" is your macro.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 4th, 2002, 08:09 AM	#1
richardff New Member Join Date: Apr 2002 Posts: 8	Oh where do I start with this! I have a macro whose sole purpose is to open another workbook, select and copy a row of data and paste it into one central workbook. That's all it does - in all, the macro does this routine for about 150 separate workbooks (and puts the rows of data all into the one central workbook). My problem is this: Each of those 150 workbooks has links to another application and when each workbook is opened it takes a second or two for the links to update. Unfortunatley, my Macro is too quick and copies the row of data before it has had a chance to update (so it just copies a row of #N/A's and no data!!!) Ideally, I need my macro to open the workbook and then pause for say 3 seconds (so the workbook can have a chance to update its links) and THEN copy the data and carry on with the rest of the routine? Is there some sort of timer function or a pause and resume command for a macro? Anyone have any advice or suggestions to get round this problem?

Apr 4th, 2002, 08:23 AM	#4
richardff New Member Join Date: Apr 2002 Posts: 8	Thanks Mark - I was searching in VBA help for "pause" "delay" "suspend" and everything else under the sun - except wait!!! Cheers!