call VB code on refresh.

[GS80]

Hi,

I have an Excel file with several office data connections. I'm having
minimum difficulty with the data that comes in and is used in various charts.
What i am having trouble with is when i have to manipulate the data as it is
to be presented in a tabular format with formatting etc. I'm doing this
automatically from an application, however am setting up the basics in an
Excel file which will be used as a template.

The paticular table i'm trying to produce is almost there aside from a bar
chart which sits alongside the table and each bar is specific to the row it
sits next to. What i need to do is once the data is refreshed i want to
resize my chart object so that it is the same height as the number of rows
in its data range. I figure i can do this is VBA behind the scenes but i need
to know is what event I can add the code to and where this goes in the VBA
project behind the scenes.

Is it also possible that everytime the file is opened that i don't get a
warning regarding diabled macros.

Thanks in advance,

G

 

[Dick Kusleika]

I have an Excel file with several office data connections. I'm having
minimum difficulty with the data that comes in and is used in various charts.
What i am having trouble with is when i have to manipulate the data as it is
to be presented in a tabular format with formatting etc. I'm doing this
automatically from an application, however am setting up the basics in an
Excel file which will be used as a template.

The paticular table i'm trying to produce is almost there aside from a bar
chart which sits alongside the table and each bar is specific to the row it
sits next to. What i need to do is once the data is refreshed i want to
resize my chart object so that it is the same height as the number of rows
in its data range. I figure i can do this is VBA behind the scenes but i need
to know is what event I can add the code to and where this goes in the VBA
project behind the scenes.

Is it also possible that everytime the file is opened that i don't get a
warning regarding diabled macros.

What are office data connections? External data tables? If so, the
Calculate event will probably work. You'll find that in the Sheet's class
module. Pull down Worksheet on the left dropdown and Calculate on the right
dropdown.

Also, you can access the AfterRefresh event of the QueryTable object if you
set up a custom class. Let me know if you want to go that route.