save workbook no queries or formulas

[rjmdc]

i have a workbook created of worksheets with tables created by queries
i want to save the workbook no queries no formulas just the worksheets with the tables
what do i do
i tried so many ties and keep failing

 

[rjmdc]

to reply
i need to share book
save copy as If you want that, then the VBA code would have to be in a different workbook, as "xlsx" files cannot contain VBA code.
If you want the VBA code in the same file, then it must be saved as an "xlsm" or "xlsb". dont want i need save as
So which option do you want?

If you want the VBA code in another file (or your Personal Macro Workbook), how would you like it to identify which file to apply the code to?
Will the file already be open, or do you want a prompt to select it?
i have open workbook with macros and many power queries to create the data on the worksheet.
i need this book copied and shared as data table worksheets only
my workbook remains as is
no queries or vba - data may not be tampered by the recipients
i have a save folder desinated for the copy
how would i do that

 

[Joe4]

Before we go down that path (and because I do not know if what I suggested will work with Power Query, since I do not use it), I want to task the following question.
If the main goal is you do not want anything tampered with, have you considered creating a PDF file out of it instead?

 

[rjmdc]

my boss asked for an excel copy

 

[Joe4]

my boss asked for an excel copy

OK, I can come up with some code, but cannot guarantee it will work with Power Query queries.
We will just have to try it and see if it does.

 

[jkpieterse]

This seems to remove all queries and connections:

Option Explicit

Sub ScrubConnections()
    Dim qt As QueryTable
    Dim q As WorkbookQuery
    Dim sht As Worksheet
    Dim lst As ListObject
    For Each sht In ThisWorkbook.Worksheets
        sht.Activate
        For Each lst In sht.ListObjects
            On Error Resume Next
            Set qt = lst.QueryTable
            On Error GoTo 0
            If Not qt Is Nothing Then
                qt.Delete
            End If
            If lst.SourceType = xlSrcModel Then
                lst.TableObject.WorkbookConnection.Delete
                lst.Unlink
            End If
        Next
        For Each qt In sht.QueryTables
            qt.Delete
        Next
    Next
    For Each q In ThisWorkbook.Queries
        q.Delete
    Next
End Sub

 

[rjmdc]

thanks
i know i have to add something like:

Sub RemoveConnections() 
    Dim cn As WorkbookConnection 
    Dim qu As WorkbookQuery 
    On Error Resume Next 
    For Each cn In ThisWorkbook.Connections     
        cn.Delete 
    Next 
    For Each quIn ThisWorkbook.Queries     
        qu.Delete 
    Next 
End Sub

 

[rjmdc]

how do i add your code jkpieterse first to copy workbook
and then do your code to copied workbook and save as xlsx

 

[rjmdc]

what am i doing wrong?

Sub ExportCopy()
 Dim wb                  As New Workbook
    Dim SaveFolder1         As String:          SaveFolder1 = "M:\all\Exports Copied Sheets\"
    Dim FileName            As String
    Dim FileDateTime        As String:          FileDateTime = Format(Now, "m-d-yy hh-mm")
    Dim FullFileName        As String
    Dim ReportDoc           As String:          ReportDoc = Worksheets("Variables").Range("ReportDocument")

    
        FileName = "Time clock " & ReportDoc
        FullFileName = Replace(FileName, ".xlsx", "") & " (" & FileDateTime & ")"

    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7")).Copy

    'Save and Close New WB
    wb.SaveAs SaveFolder1 & FullFileName & ".xlsx", xlOpenXMLWorkbook
    wb.Close SaveChanges:=False
    DoEvents

    'Back to Main WB
    ThisWorkbook.Activate
    
    MsgBox "Process Complete"

End Sub