Save copy of spreadsheet after refreshing data from SQL Server 1

[lhrpa]

Hello there!

I have a spreadsheet that on file open will refresh data from SQL Server into the spreadsheet. I want to be able to save and keep that data in a spreadsheet as of that point in time. If I do a "Save As", it of course saves the spreadsheet with the queries, and then the next time it opens, it refreshes the data, so I lose the point in time I had.

I've been trying to find a way to get rid of the queries before I do the "Save As", but I can't seem to do it without getting rid of the cell contents.

I'm trying to put this into a macro so that the user only has to open the file when they are ready for the new data.

Any suggestions would be greatly appreciated!!

Thank you!

Linda

 

[xlbo]

Use a macro to save:

Sub Saver()
Thisworkbook.saveas ("\\FullPath\" & left(thisworkbook.name,len(thisworkbook.name)-4) & format(date,ddmmyy)&".xls"
with activeworkbook
for each sht in .sheets
for each qt in sht.querytables
qt.refreshonfileopen = false
next
next
end with
activeworkbook.save
end sub

This will save a seperate copy of the workbook with the current date and then loop thru all sheets in the workbook, looking for querytables. When it finds one, it sets the refreshonfileopen property to false so data can only be refreshed manually

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[lhrpa]

I added that to my macro and it worked like a champ! Thanks so much!!

Linda