Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove query table when saving
I am updating an excel spreadsheet with data from sage using Query. I am
than doing some calculation before e-mailing on, the problem I have is that the file I am sending still has the refresh data command and can be linked to the source data which I do not want. Is there some code that could uncheck the "Save query definition" in data range properties or is there a simpler way. Any help would be greatly appreciated Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove query table when saving
Michael,
I was looking for exactly the same solution. This is what I ended up with; Sub PrepareReport() ' ' PrepareReport Macro ' Macro recorded 8/09/2005 by Tige Brown ' ' Dim Fname As String Dim CrntDir As String Dim SDate As String Dim Wsheet As Worksheet Dim qtb As QueryTable SDate = Format(Year(Now), "0000") & "-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00") Fname = ActiveWorkbook.Path & "\Price List Audit " & SDate ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False For Each Wsheet In ActiveWorkbook.Worksheets For Each qtb In Wsheet.QueryTables qtb.Delete Next Next ActiveWorkbook.Save Active End Sub And it works fine. Got the solution from the following forum; http://www.excelforum.com/archive/in.../t-332827.html Tige "Michael" wrote: I am updating an excel spreadsheet with data from sage using Query. I am than doing some calculation before e-mailing on, the problem I have is that the file I am sending still has the refresh data command and can be linked to the source data which I do not want. Is there some code that could uncheck the "Save query definition" in data range properties or is there a simpler way. Any help would be greatly appreciated Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove query table when saving
VBA code:
Myworksheet.QueryTables("my_query").EnableRefresh = False should do the trick I would suggest creating a copy of the workbook first, else *you* wont be able to refresh the table as well. DM Unseen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove query table when saving
Absolutely fantastic, thanks
Michael "Tige" wrote: Michael, I was looking for exactly the same solution. This is what I ended up with; Sub PrepareReport() ' ' PrepareReport Macro ' Macro recorded 8/09/2005 by Tige Brown ' ' Dim Fname As String Dim CrntDir As String Dim SDate As String Dim Wsheet As Worksheet Dim qtb As QueryTable SDate = Format(Year(Now), "0000") & "-" & Format(Month(Now), "00") & "-" & Format(Day(Now), "00") Fname = ActiveWorkbook.Path & "\Price List Audit " & SDate ActiveWorkbook.SaveAs Filename:=Fname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False For Each Wsheet In ActiveWorkbook.Worksheets For Each qtb In Wsheet.QueryTables qtb.Delete Next Next ActiveWorkbook.Save Active End Sub And it works fine. Got the solution from the following forum; http://www.excelforum.com/archive/in.../t-332827.html Tige "Michael" wrote: I am updating an excel spreadsheet with data from sage using Query. I am than doing some calculation before e-mailing on, the problem I have is that the file I am sending still has the refresh data command and can be linked to the source data which I do not want. Is there some code that could uncheck the "Save query definition" in data range properties or is there a simpler way. Any help would be greatly appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems saving pivot table in same workbook as export query | Charts and Charting in Excel | |||
Remove content when saving / loading spreadsheet | Excel Worksheet Functions | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
VB way to remove the query from a Query Table? | Excel Programming | |||
Pivot Table SQL Query saving | Excel Programming |