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. |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 01:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com