Excel:Disconnect Linked Data?

[Falcon99]

Hi, I hope someone can help.
I have set up a spreadsheet that has a linked data range from an Access DB table. I have automated this as much as possible for non-proficient users.
In Access a query runs to populate a table which is what the sheet is linked to.
The problem is when they go to distribute the sheet there is the constant nag about finding the DB source file. And yes you can cancel without problem but it is disconcerting if not confusing to users.

Is there any way to save or convert a spreadsheet so that the data is no longer "Linked", in essence being just a snapshot?

I've gone over the DB properties for the sheet and although I can delete the "query" the darn thing still looks for a DB when you open it up.
I suppose there is a way to use VBA to output from Access to Excel but it's frustrating when you've got something that is 99.999% functional and you think it would be easy to get that last bit. :/

Hope this makes sense.

TIA
Falcon

 

[koresnordic]

You do not need to use VBA. Create a macro. There is an action called "transferspreadsheet". It defaults to a transfer type of import, but you can change this to export. Select the version of Excel you wish to create. Enter the table/query name that you wish to export. Give it a file name (include the full directory path). Say if yo uwant the field names with it. Usually I leave the range as blank. Save this and when you wish to output to the file, run this macro. I hide it behind a button on a form.
Note: This will create a plain spreadsheet with no formatting or formulas. It will overwrite any existing spreadsheet with the same name, so any changes made to a previous copy in the same directory will be lost.
Hope this helps.

Graham

 

[Falcon99]

Thanks, Graham,

Overwriting the spreadsheet is something I'm trying to avoid. I have special column tiles, subtotals, etc. It's basically their report. They like getting it in Excel so they can play with the data. That's what was nice about data linking.

Falcon