save Importing Data

[Scooternm]

I have a workbook that contains about 10 sheets that import data from a SQL Server query. Right now it is set to automatically refresh when opened. The problem is I want to then email this spreadsheet off to a client, hence losing the SQL Server connection. Is there a way to import the data once and save it as static text? Thanks!

[Leith Ross]

Hello Scooternm,

This macro will turn the automatic refresh off for all queries in the workbook. The refresh on opening is not effected. Place this code in a Standard VBA module and then call it from the Workbook Open event.

Please Login or Register  to view this content.

Add this macro to your Workbook_Open() event as shown below...

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

[Scooternm]

If I turned off the refresh on open....is there a macro that I can run on my side that would automatically refresh all the worksheets, instead of having to go to each one and hitting refresh data? thanks again.

[Leith Ross]

Hello Scooternm,

You can create a second macro using the first. You only need to change the RefreshPeriod from 0 to some suitable number. The refresh period is measured in seconds.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Scooternm]

You helped a lot!!!

Scott