how to have code run automatically after auto refresh?

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi!

I have a workbook that imports data from several delimited files into different worksheets in the workbook. Each set of imported data gets refreshed automatically when the workbook is opened. Is it possible to have code or a macro run automatically after the data gets refreshed?

Specifically, I want to have a range on a seperate sheet that is a union of the first 2 columns of each of the imported sheet with duplicate records removed.

Where should this code be placed? Is there a similar workbook event such as open but which is for after the data is refreshed?

Thanks!
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,128
The querytable object has an AfterRefresh method that should do what you're looking for. The helpfile has good documentation on it. Make sure you read it all, there is another section called "Using Events with the QueryTable Object" that you'll need to incorporate.

Reply back if you can't get it to work.
 

Barq

New Member
Joined
Nov 16, 2005
Messages
18
Hi,
thanks for replying.

I imported the data using the import text file dialogue box. Would the resulting table be a quearytable Object?

Also, I am having problems joining the first 2 columns of each worksheet into one range on a seperate sheet. Any help in this would be really really appreciated.

Thanks.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,128
Yes, the resulting table is a query table object, it will just have a different connection string than something like a database query.

Now that I think about it some more, you could approach this a different way. Turn off the "refresh queries on open" setting that you have for all your text file imports.

Create a macro that runs upon opening the workbook, then have that refresh all queries (set them all to background refresh = false, that way your code will wait til refreshes are complete), then you can do your range union stuff. Change each query's refresh by right clicking anywhere in query data, choosing data range properties, and disabling the background query checkbox.

Private Sub Workbook_Open()

ActiveWorkbook.RefreshAll

'now add your union code

End Sub
 

Forum statistics

Threads
1,078,253
Messages
5,339,103
Members
399,278
Latest member
randomNumberGenerator2211

Some videos you may like

This Week's Hot Topics

Top