Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting Data
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting Data
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting Data
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting Data
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Extracting Data
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 "Ed Ferrero" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data | Excel Discussion (Misc queries) | |||
Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
Extracting data | Excel Discussion (Misc queries) | |||
extracting data | Excel Worksheet Functions | |||
Extracting Data | Excel Discussion (Misc queries) |