Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application wait question
I'm having some issues with pausing a macro to allow data to load. I have a
table created from an imported web query, and my macro refreshes the query and then makes some adjustments to the new data: Sub NewData() 'Refresh part Range("e44").Select Selection.QueryTable.Refresh 'line X 'Adjust part Range("ColA").Select Selection.Copy Range("ColB").Select Selection.PasteSpecial Paste:=xlPasteValues Range("ColB").Select Selection.Replace What:="~*", Replacement:="" End sub For line X, I have tried: Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10) and Application.Wait Now + TimeValue("00:00:10") and have also made use of: Public Function Delay() y = Timer + 10 Do While Timer < y DoEvents Loop End Function However, when I run the macro, only the Refresh part works even though it takes 10 seconds to run, but when I run the macro again, the Adjust part then works. Also, when I step through the data, the pause activates, and everything seems to work. Any ideas would be appreciated. thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application wait question
Just a thought... still not sure if this will work... but have you tried
separating the macros? Please see example below. Mark Ivey Sub NewData() 'Refresh part Range("e44").Select Selection.QueryTable.Refresh ' Call the second macro Second_Macro End Sub Sub Second_Macro() 'Adjust part Range("ColA").Select Selection.Copy Range("ColB").Select Selection.PasteSpecial Paste:=xlPasteValues Range("ColB").Select Selection.Replace What:="~*", Replacement:="" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application wait question
Mark - thanks for responding. However, I had already tried that, and
unfortunately it didn't work either. "Mark Ivey" wrote: Just a thought... still not sure if this will work... but have you tried separating the macros? Please see example below. Mark Ivey Sub NewData() 'Refresh part Range("e44").Select Selection.QueryTable.Refresh ' Call the second macro Second_Macro End Sub Sub Second_Macro() 'Adjust part Range("ColA").Select Selection.Copy Range("ColB").Select Selection.PasteSpecial Paste:=xlPasteValues Range("ColB").Select Selection.Replace What:="~*", Replacement:="" End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application wait question
"Graeme" wrote in message
... I'm having some issues with pausing a macro to allow data to load. I have a table created from an imported web query, and my macro refreshes the query and then makes some adjustments to the new data: Sub NewData() 'Refresh part Range("e44").Select Selection.QueryTable.Refresh Does this work? Selection.QueryTable.Refresh (False) -- roger |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application wait question
This worked perfectly! Thank you.
"roger" wrote: "Graeme" wrote in message ... I'm having some issues with pausing a macro to allow data to load. I have a table created from an imported web query, and my macro refreshes the query and then makes some adjustments to the new data: Sub NewData() 'Refresh part Range("e44").Select Selection.QueryTable.Refresh Does this work? Selection.QueryTable.Refresh (False) -- roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.wait... won't! | Excel Programming | |||
Please help with Application.Wait ( ) | Excel Programming | |||
pause vba action without using Application.Wait? | Excel Programming | |||
Different syntax for Application.Wait | Excel Programming | |||
application.wait help | Excel Programming |