Set Dynamic Ranges for data imported from Access

[Jameo]

Hi all,

I have a two part question here, forgive my ignorance regarding the intricacies of VBA.

I have an access database set up, this database lists all of the power stations in the country, and associated flow rates, by day and hour.

The power stations are further split into regional areas. So far I have got to the point where a user can select either a region containing multiple stations, a single station, and further filter those results with a date range. I have a make table query that puts the queried data into a new table, and then a cross tab query that formats it into the layout that I require for excel. All these queries are ran from the same command button.

After the cross tab query has been ran, my data lies in the form of:

|Gas Day | Sort Hour | Total Flow Rates | Station 1 Rate | Station 2 Rate |

(this continues for however many stations the user has queried to a max of 30 seperate station columns)

Part 1 - I would now like to import the queried data into an existing excel spreadsheet.

Part 2 - For the imported data I would like to be able to set dynamic named ranges for each column.


My end goal is to be able to automatically generate a series of charts from the newly exported data.

The thing is, I am not totally sure of how to be able to accomplish this. Both the number of rows, and the number of columns will change dependant on the extent of the users query. Also, everytime a new query is made, the old information in the speadsheet must be cleared and the new data added in its place.

From my limited VBA experience I would guess that there would have to be someway to have excel distinguish how many rows of data and colums of data have been added on each query, then set dynamic ranges for each column, and then run a macro designed to plot charts specifically realtde to the amount of columns (i.e. if there are 5 power stations in the list, then plot 5, if there are 10, then plot 10)

I am just clutching at straws a little bit, and don't really know the best way to procede. Any input would be greatly appreciated.

Many thanks

 

[Jameo]

I realise that the above may not be the easiest post to follow. If you have any further information you wish me to supply please ask.

Ideally my VBA module would look at the data in a query. At the same time as export it would automatically define a dynamic range for each column.

After the data is exported into excel there would then be a futher module that would then create a chart for each defined column