|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have existing Excel workbook with numerous worksheets that are linked to
Charts.
I need to be able to update the worksheets data from Access 2003 to using
two different datasources. 1st datasource is based on ALL and must reflect
the summarized version of the data and 2nd is based on selected criteria. Is
it possible to use the same wkshts with different datasources without
affecting the Charts or do I need to create a separate chart and datasource 1
for ALL and 1 for Criteria based?
--
Karen
|
|
Hi Karen - If I am understanding what you are saying then yes, you should be
able to use the same wksheets with different datasources and have your charts
link to the imported data.
In Excell use the
Data / Import External Data / New Database Query
The Choose Data Source Dialog box appears
Choose MS Access Database
Click on OK or press Return
The Select Database Dialog box appears
Find and select you Access DB
You can then choose a Table or Query
Select your fields
The next screens are for you to Filter and Sort the data
When you finish the data is slotted into your wksheet and every time you
open it the data is updated. You can Import as many tables/queries as you
want - I have a wksheet with over 35.000 records broken down onto 7 tabs. And
other wksheets with the several queries from the same table used on a single
sheet, just having used Access Queries.
Hope this answers you. Yours -- Dika
"kfschaefer" wrote:
[Quoted Text] > I have existing Excel workbook with numerous worksheets that are linked to
> Charts.
>
> I need to be able to update the worksheets data from Access 2003 to using
> two different datasources. 1st datasource is based on ALL and must reflect
> the summarized version of the data and 2nd is based on selected criteria. Is
> it possible to use the same wkshts with different datasources without
> affecting the Charts or do I need to create a separate chart and datasource 1
> for ALL and 1 for Criteria based?
>
>
> --
> Karen
|
|
Thank you for your input, however, I do not wish to import from Excel, rather
Export from Access. My user wants it to be seamless, without any effort on
their part. Is there a way via VBA using the TransferSpreadsheet, export
to assign the named range or which wksheet to update thru the Access export?
--
Karen
"Kernow Girl" wrote:
[Quoted Text] > Hi Karen - If I am understanding what you are saying then yes, you should be
> able to use the same wksheets with different datasources and have your charts
> link to the imported data.
>
> In Excell use the
>
> Data / Import External Data / New Database Query
> The Choose Data Source Dialog box appears
> Choose MS Access Database
> Click on OK or press Return
> The Select Database Dialog box appears
> Find and select you Access DB
> You can then choose a Table or Query
> Select your fields
> The next screens are for you to Filter and Sort the data
>
> When you finish the data is slotted into your wksheet and every time you
> open it the data is updated. You can Import as many tables/queries as you
> want - I have a wksheet with over 35.000 records broken down onto 7 tabs. And
> other wksheets with the several queries from the same table used on a single
> sheet, just having used Access Queries.
>
> Hope this answers you. Yours -- Dika
> "kfschaefer" wrote:
>
> > I have existing Excel workbook with numerous worksheets that are linked to
> > Charts.
> >
> > I need to be able to update the worksheets data from Access 2003 to using
> > two different datasources. 1st datasource is based on ALL and must reflect
> > the summarized version of the data and 2nd is based on selected criteria. Is
> > it possible to use the same wkshts with different datasources without
> > affecting the Charts or do I need to create a separate chart and datasource 1
> > for ALL and 1 for Criteria based?
> >
> >
> > --
> > Karen
|
|
Karen,
You can export data to Excel from Access without a problem. The method that
will give you the most control is to control Excel from Access through
Automation. Using Visual Basic you can open the Excel spreadsheet and then go
the specific worksheets and copy or update the data based on queries you run
as you go.
This will require some coding and will require that the Excel spreadsheet's
format stays the same or your code could potentially overwrite the wrong
data. You can include some checks in your code to avoid this, but it depends
on how complex you want your export function to be. In any case, since you
are exporting from Access, your Excel file will be 'static', in the sense
that it cannot be updated but from the Access database.
From your message, though, I gathered that you'd like to be able to do some
type of selection on the second chart so you can show detailed data or a
subset of the first chart. There are many ways to do this, depending on 1)
how much data you are exporting to Excel, 2) how often it needs to be updated
and 3) the response time you need. Your network connections may also play a
role if you have shared drives or users located in other places.
If you'd like to be able to see the latest data entered in the database,
you'll need to link to it from Excel, as external data source. If, on the
other hand, you can live with semi-static data (say, a report that is updated
only once a week), you can probably export from Access once a week and share
that report.
It would really help if you can provide more details about your Excel file
and the data you are exporting. For example, if you think your data will
never have more than 65k records/rows, you can export the raw data to Excel
and do all the manipulation and summarization in Excel though Pivot Tables,
etc. If you have lots of rows, then you may want to keep the two part
separated so the summary data doesn't use many resources in Excel to display
the totals. Again, there are many variables and it will help if you can
provide as many details as possible.
Good Luck,
Fermon
"kfschaefer" wrote:
[Quoted Text] > Thank you for your input, however, I do not wish to import from Excel, rather
> Export from Access. My user wants it to be seamless, without any effort on
> their part. Is there a way via VBA using the TransferSpreadsheet, export
> to assign the named range or which wksheet to update thru the Access export?
> --
> Karen
>
>
> "Kernow Girl" wrote:
>
> > Hi Karen - If I am understanding what you are saying then yes, you should be
> > able to use the same wksheets with different datasources and have your charts
> > link to the imported data.
> >
> > In Excell use the
> >
> > Data / Import External Data / New Database Query
> > The Choose Data Source Dialog box appears
> > Choose MS Access Database
> > Click on OK or press Return
> > The Select Database Dialog box appears
> > Find and select you Access DB
> > You can then choose a Table or Query
> > Select your fields
> > The next screens are for you to Filter and Sort the data
> >
> > When you finish the data is slotted into your wksheet and every time you
> > open it the data is updated. You can Import as many tables/queries as you
> > want - I have a wksheet with over 35.000 records broken down onto 7 tabs. And
> > other wksheets with the several queries from the same table used on a single
> > sheet, just having used Access Queries.
> >
> > Hope this answers you. Yours -- Dika
> > "kfschaefer" wrote:
> >
> > > I have existing Excel workbook with numerous worksheets that are linked to
> > > Charts.
> > >
> > > I need to be able to update the worksheets data from Access 2003 to using
> > > two different datasources. 1st datasource is based on ALL and must reflect
> > > the summarized version of the data and 2nd is based on selected criteria. Is
> > > it possible to use the same wkshts with different datasources without
> > > affecting the Charts or do I need to create a separate chart and datasource 1
> > > for ALL and 1 for Criteria based?
> > >
> > >
> > > --
> > > Karen
|
|
Fermon,
thanks for your reply, to answer your questions.
The Data that is being updated is only a few columns and dozen rows of data
per each chart.
What I want to do is to use the same data source for the criteria data
results and the "ALL" criteria Data results - without having to link the
datasource to an outside source - My customer wants the data to be
standalone. I want to be able to use the same charts for both criteria data
results and the "ALL" criteria results.
Are you suggesting that I do somekindof FileCopy of the data from the
results of the Access query to the Excel (nameed) wksheet.
This process will be run on demand most likely weekly or monthly.
no the Charts are not based on the results of Chart1 to Chart2 or Chart2 to
Chart3.
It is based on the data and whether the user select view ALL or limits the
query to a specific Criteria. I currently have 8 charts and corresponding
data wkshts. 4 for the criteria data and 4 for the "ALL" criteria. I want
to eliminate the necessity for the 8 charts and keep the for no matter what
the criteria select was.
I hope this is more clearer.
Thanks,
Karen
--
Karen
"Fermon" wrote:
[Quoted Text] > Karen,
>
> You can export data to Excel from Access without a problem. The method that
> will give you the most control is to control Excel from Access through
> Automation. Using Visual Basic you can open the Excel spreadsheet and then go
> the specific worksheets and copy or update the data based on queries you run
> as you go.
>
> This will require some coding and will require that the Excel spreadsheet's
> format stays the same or your code could potentially overwrite the wrong
> data. You can include some checks in your code to avoid this, but it depends
> on how complex you want your export function to be. In any case, since you
> are exporting from Access, your Excel file will be 'static', in the sense
> that it cannot be updated but from the Access database.
>
> From your message, though, I gathered that you'd like to be able to do some
> type of selection on the second chart so you can show detailed data or a
> subset of the first chart. There are many ways to do this, depending on 1)
> how much data you are exporting to Excel, 2) how often it needs to be updated
> and 3) the response time you need. Your network connections may also play a
> role if you have shared drives or users located in other places.
>
> If you'd like to be able to see the latest data entered in the database,
> you'll need to link to it from Excel, as external data source. If, on the
> other hand, you can live with semi-static data (say, a report that is updated
> only once a week), you can probably export from Access once a week and share
> that report.
>
> It would really help if you can provide more details about your Excel file
> and the data you are exporting. For example, if you think your data will
> never have more than 65k records/rows, you can export the raw data to Excel
> and do all the manipulation and summarization in Excel though Pivot Tables,
> etc. If you have lots of rows, then you may want to keep the two part
> separated so the summary data doesn't use many resources in Excel to display
> the totals. Again, there are many variables and it will help if you can
> provide as many details as possible.
>
> Good Luck,
>
> Fermon
>
> "kfschaefer" wrote:
>
> > Thank you for your input, however, I do not wish to import from Excel, rather
> > Export from Access. My user wants it to be seamless, without any effort on
> > their part. Is there a way via VBA using the TransferSpreadsheet, export
> > to assign the named range or which wksheet to update thru the Access export?
> > --
> > Karen
> >
> >
> > "Kernow Girl" wrote:
> >
> > > Hi Karen - If I am understanding what you are saying then yes, you should be
> > > able to use the same wksheets with different datasources and have your charts
> > > link to the imported data.
> > >
> > > In Excell use the
> > >
> > > Data / Import External Data / New Database Query
> > > The Choose Data Source Dialog box appears
> > > Choose MS Access Database
> > > Click on OK or press Return
> > > The Select Database Dialog box appears
> > > Find and select you Access DB
> > > You can then choose a Table or Query
> > > Select your fields
> > > The next screens are for you to Filter and Sort the data
> > >
> > > When you finish the data is slotted into your wksheet and every time you
> > > open it the data is updated. You can Import as many tables/queries as you
> > > want - I have a wksheet with over 35.000 records broken down onto 7 tabs. And
> > > other wksheets with the several queries from the same table used on a single
> > > sheet, just having used Access Queries.
> > >
> > > Hope this answers you. Yours -- Dika
> > > "kfschaefer" wrote:
> > >
> > > > I have existing Excel workbook with numerous worksheets that are linked to
> > > > Charts.
> > > >
> > > > I need to be able to update the worksheets data from Access 2003 to using
> > > > two different datasources. 1st datasource is based on ALL and must reflect
> > > > the summarized version of the data and 2nd is based on selected criteria. Is
> > > > it possible to use the same wkshts with different datasources without
> > > > affecting the Charts or do I need to create a separate chart and datasource 1
> > > > for ALL and 1 for Criteria based?
> > > >
> > > >
> > > > --
> > > > Karen
|
|
|