Question Update Preformatted Excel Spreadsheet with Access 2010 Query. (1 Viewer)

[J_Cat]

Hi All,

So here's the scoop. I have an Access crosstab query that I have exported to an Excel Spreadsheet. I have the spreadsheet formatted using conditional formatting and I'd rather not have to reset it every morning. It's a single spreadsheet (the columns/rows will not deviate greatly day to day) and should be very simple, but I'm not getting it for some reason.

So if I have "Test.accdb" and it contains "qryX" as my crosstab and "Sheet1.xls" is my formatted Excel spreadsheet, how do I code for the latest "QryX" to go in and replace the old "QryX" data in "Sheet1.xls" ?

 

[DavidAtWork]

If you try to export your query in xls format, I think you'll lose the formatting each time you export. As a work around I'd suggest you export as you have been only create a second workbook that is linked to your exported data and now you can set all the formatting you want in the second workbook and it won't be lost.
I don't know how you use the end product but if it has to be distributed then you'll need to save as a new filename and break the links.

David

 

[J_Cat]

Thanks David,

That sounds like an innovative solution - and here I was thinking I'd have to code everything. Nice. I'll give it a shot and report back.

JO