how to have code run automatically after auto refresh?

[Barq]

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]

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]

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]

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

 

[Barq]

Thanks, appreciate your help!