Excel 5.0/95 - Excel 2000 Macro - Save

[Freckles]

One of the glitches in Access 2000 is that when you export to Excel, it exports in 5.0/95 format. To get it into Excel 2000 format (so that it can be linked to), you need to save it using the "disk" or "save as". You can not just close it.

When I wrote a macro to save it, it saves it in 5.0/95 and not 2000. I need a macro (that I can call from Access)so that it will save it and close the file.

Any ideas???

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[SkipVought]

Deb Koplen,

Does this need to be done from Access?

Could you query from Excel?

It could run on the Workbook_Open event. It does not use any code, just Data/Get External Data/New Database Query -- Access files -- your database -- your table(s) or query(s)...

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Freckles]

I am exporting 72+ queries from Access to Excel to update 20 Excel charts. And the charts have to be in Excel. I need to do whatever is possible to speed up the process. And this would be one step.

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[SkipVought]

I'd set up these 72+ queries ONCE in Excel, Use OFFFSET formulas in Insert/Name/Define to define Named Ranges to make your charts' source data dynamic.

Each time the Workbook is opened, the queries can be refreshed, or the query refresh can be triggered by the Activation of a ChartSheet or WorkSheet.

BTW, how does the data from 72+ queries get into ONLY 20 charts?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Freckles]

How do you get 72 into 20? With a lot of time spent in setup.

I have one workbook, and in that workbook is a sheet labeled "DataMaster". Data Master is linked outward to the 72+ queries generated from Access. And then the 20 worksheet pages are linked to the "DataMaster". Since they are updated maybe 2 and 3 times a month, and have multiple combinatins of products and views, I have had to cut down the repetitive work as much as possible, or that would all that I do. And the data is in a big Access 2000 database. Each month I move the main workbook to another "date-named" directory, and export the queries to that same directory. Therefore, no problem with the main workbook finding the linked spreadsheets.

Now, I just have to expedite getting the queries out of Access and converted to Excel 2000 quicker.

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[SkipVought]

Once you have queries set up in excel, there should be no need to expedite or do anything -- just run the queries.

???

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Freckles]

AH HA -- Using MS Query. Hmmmmm. Never thought of that.

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[SkipVought]

I do lots with MS Querys from Excel -- some on the fly using Paramaters and forms drop downs (NO CODE!) ;-)

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]