Open Excel, refresh query, and format Excel spreadsheet

[Guest]

In my Access 2003 application, I have a query that generates a large data set
and stores it in a temporary table. I also have an Excel spreadsheet that is
linked to this table. I am using Excel to view the data in a variety of
ways, and to format the font and background colors of the pivot table cells.
I am also using this format because I need to post this information to a
portal so others can download it.

My problem is this. Because of the formatting in the spreadsheet (column
headers, conditional formatting, ...) I cannot use the Transferspreadsheet
method, and have to use Excel to link to the data table. I've figured out
how to open the Excel file, but cannot figure out how to force it to refresh
the link. The other problem is that when I refresh the data manually in this
spreadsheet, it changes the column widths in the spreadsheet. I figure I can
record a macro to reformat these columns, but was wondering whether there is
a way to prevent this.

 

[Guest]

Dale,

If I understood you correctly you use a query to get data out of an access
database. Afterwards on this data you create a pivottable.

What you could do is directlty get the data out Access in an Excel pivot
table. You have to select "External datasource" and create a query. Via this
way also your formats are preserved.

HTH