Extracting Data

[Phil Stanton]

I am trying to use Excel 2000 to create a map of our boat storage areas.
There are a number of Locations such as Boat Compound and Mast Shed. There
are currently 8 such locations.
I have an MSAccess database that stores each area, combined (spaceno and
owner's name) and x & y co-ordinates.
Each worksheet has an xy scatter graph with a plan of the location
concerned, the xy plot and thanks to Bob Bovey's xy labeller, the number and
name of each space.

E.g. Location SpaceAndName X Y
Mast Shed 2 - Smith 47 23
Mast Shed 5 - Jones 20 23
Mast Shed 17 - Robinson 5 48
Boat Compound 42 - Donby 47 20
Boat Compound 41 - Sonny 53 20

2 Problems.
1 The plan (background to the chart) is embedded. I would like it to be
linked so that changes in the plan are reflected in the chart.
2 I have had to create separate queries to extract the data from Access
for each of the 8 location. It appears that each query has to be refreshed
separately. I would like to have a single query linked to Access with all
the locations and break them down in Excel. Is this possible?

Thanks

Phil

 

[Ed Ferrero]

Hi Phil,

1 The plan (background to the chart) is embedded. I would like it
to be linked so that changes in the plan are reflected in the chart.

To change the chart background, use a small macro.

Sub ChangeChartBg()
'
' ChangeChartBg
' Macro recorded 25-06-2007 by Ed Ferrero

Dim cht As Chart
Dim chtArea As ChartArea
Dim myPath As String
Dim myFile As String

' change the following line to refer to your chart
' eg ActiveSheet.ChartObjects("Chart 13").Chart
Set cht = ActiveSheet.ChartObjects("Chart 13").Chart
Set chtArea = cht.ChartArea

' Change the following lines to refer to the picture you wish to see
' in this example cell F1 contains the file path
' and cell F2 contains the file name that you wish to use as background
myPath = ActiveSheet.Range("$F$1").Value
myFile = ActiveSheet.Range("$F$2").Value

chtArea.Fill.UserPicture PictureFile:=myPath & myFile
chtArea.Fill.Visible = True
End Sub

2 I have had to create separate queries to extract the data from Access
for each of the 8 location. It appears that each query has to be refreshed
separately. I would like to have a single query linked to Access with all
the locations and break them down in Excel. Is this possible?

Probably the easiest way to do this is to use an Access query that has all
locations, and use AutoFilter in Excel to select the location you want.

Ed Ferrero
www.edferrero.com

 

[Phil Stanton]

Hi Ed

Exactly what I want. If I change it into a function it works perfectly by
typing "ChangeChartBg" in the immediate window.

Could you point me in the right direction to getting the macro to run
automatically for each chart ( there is a different one on each of 7
worksheets) when I open this file (Storage.XLS)

The other bit is also working fine. Have now a single query from which I
extract the data.

Thanks again

Phil

 

[Ed Ferrero]

Hi Phil,

To run the macro whenever the workbook is opened...

First, channge the original code as follows...

Sub ChangeChartBg(ByRef sht As Worksheet)
'
' ChangeChartBg
' 26-06-2007 by Ed Ferrero

Dim cht As Chart
Dim chtArea As ChartArea
Dim myPath As String
Dim myFile As String

' change the following line to refer to your chart
' here I assume you only have one chart on each sheet
' if there is more than one chart, either loop through
' each chart and change the background, or name the chart you wish
' to change and use Set cht = sht.ChartObjects("myChartName").Chart
' to change it
Set cht = sht.ChartObjects(1).Chart
Set chtArea = cht.ChartArea

' Change the following lines to refer to the picture you wish to see
' in this example cell F1 contains the file path
' and cell F2 contains the file name that you wish to use as background
myPath = sht.Range("$F$1").Value
myFile = sht.Range("$F$2").Value

chtArea.Fill.UserPicture PictureFile:=myPath & myFile
chtArea.Fill.Visible = True
End Sub

Then, add the following code to the code pane for the Workbook (double-click
Thisworkbook in the VB Editor) and copy the code.

Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
Call ChangeChartBg(sht)
Next sht
End Sub

That's it. The Workbook_Open code runs whenever the workbook is opened. It
loops through each worksheet and calls the ChangeChartBg procedure. It
passes the worksheet object to the ChangeChartBg procedure, so that the
first chart on each sheet is updated.

Ed Ferrero

 

[Phil Stanton]

Thanks, Ed

Worked perfectly. Just had to add a couple of lines to check that MyFile and
Mypath were valid and I was there.

Really appreciate your help

Phil