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

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

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

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