save workbook No queries no formulas no vba

[rjmdc]

copy entire book
i want to copy values only of book, no queries, no vba, no formulas
what do i need to change

Sub Auto_Export()

    Dim cES As New clsExcelSettings
    
    'Turn off Screen Updating etc
    cES.SettingsOff
    
    Dim saveDate            As Date
    Dim saveTime            As Variant
    Dim formatTime          As String
    Dim formatDate          As String
    Dim SaveFolder          As String
    Dim FileExt                  As String
    Dim ThisFileName        As String
    Dim DateFrom            As String:          DateFrom = Format(Worksheets("Control").Range("StartDate"), "m.d.yyyy")
    Dim DateTo              As String:           DateTo = Format(Worksheets("Control").Range("EndDate"), "m.d.yyyy")
    
    If MsgBox("Would you like to Export this file?", vbInformation + vbYesNo) = vbYes Then
         
       saveDate = Now
       FileExt = ".xlsm"
       formatDate = Format(saveDate, "MM-DD-YYYY hh.MM")
       SaveFolder = "M:\all\Billing Export Books\"
       ThisFileName = "Reconcile  " & DateFrom & "-" & DateTo & "_" & formatDate & FileExt
       ActiveWorkbook.SaveCopyAs SaveFolder & ThisFileName
       MsgBox "Process Complete"
     
     End If

    'Turn On Screen Updating etc
    cES.SettingsOn
    
End Sub

 

[Anthony47]

My suggestion is that you prepare a new workbook that is a plain copy of your original workbook.
This macro should do the job:

Sub PlainWB()
'https://www.mrexcel.com/board/threads/save-workbook-no-queries-no-formulas-no-vba.1215689/
Dim nwWb As Workbook, thWb As Workbook
Dim sPoint As String
'
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.SheetsInNewWorkbook = 1
Set thWb = ThisWorkbook
Set nwWb = Workbooks.Add
For I = 1 To thWb.Worksheets.Count
    thWb.Activate
    Worksheets(I).Select
    sPoint = Worksheets(I).UsedRange.Cells(1, 1).Address
    Worksheets(I).UsedRange.Copy
    Application.Goto nwWb.Sheets(I).Range(sPoint)
    DoEvents
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
    Range("A1").Select
    nwWb.Sheets(I).Name = thWb.Worksheets(I).Name
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = "ZcZc"
Next I
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox ("Plain Workbook is ready")
End Sub

At this point you may save the new workbook (in xlsx format) using your code (with couple of modifications):

    If MsgBox("Would you like to Export this file?", vbInformation + vbYesNo) = vbYes Then
       saveDate = Now
       FileExt = ".xlsx"            'XLSM???
       FormatDate = Format(saveDate, "MM-DD-YYYY hh.MM")
       SaveFolder = "M:\all\Billing Export Books\"
       ThisFileName = "Reconcile  " & DateFrom & "-" & DateTo & "_" & FormatDate & FileExt
       ActiveWorkbook.Save SaveFolder & ThisFileName
       MsgBox "Process Complete"
       nwWb.Close False             'Close the plain workbook
     End If

Your initial workbook is still open at the end of the process

 

[dmt32]

Hi see if this update to your code does what you want

UNTESTED & STRONGLY suggest that you make a backup BEFORE TESTING

Sub Auto_Export()
  
    Dim cES                 As New clsExcelSettings
  
    Dim wb                  As Workbook
    Dim ws                  As Worksheet
    Dim nme                 As Name
    Dim myLinks             As Variant
    Dim i                   As Long
  
    Dim ThisFileName        As String, FileExt As String, SaveFolder         As String
    Dim DateFrom            As String, DateTo As String, formatDate         As String
  
    If MsgBox("Would you Like To Export this file?", vbInformation + vbYesNo) = vbYes Then
      
        On Error GoTo myerror
        'Turn off Screen Updating etc
        cES.SettingsOff
      
        With ThisWorkbook.Worksheets("Control")
            DateFrom = Format(.Range("StartDate"), "m.d.yyyy")
            DateTo = Format(.Range("EndDate"), "m.d.yyyy")
        End With
      
        formatDate = Format(Now, "MM-DD-YYYY hh.MM")
        SaveFolder = "M:\all\Billing Export Books\"
      
        ThisFileName = "Reconcile  " & DateFrom & "-" & DateTo & "_" & formatDate
      
        ActiveWorkbook.SaveCopyAs SaveFolder & ThisFileName
      
        Set wb = Workbooks.Open(SaveFolder & ThisFileName, 0, False)
      
        For Each ws In wb.Worksheets
            With ws.UsedRange
                .Value = .Value
            End With
        Next ws
      
        'delete all names
        For Each nme In wb.Names
            nme.Delete
        Next
      
        'Create an Array of External Links
        myLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
      
        If IsArray(myLinks) Then
            'Loop each External Link in Workbook
            For i = LBound(myLinks) To UBound(myLinks)
                wb.BreakLink Name:=myLinks(i), Type:=xlLinkTypeExcelLinks
            Next i
        Else
            If Len(myLinks) > 0 Then .BreakLink Name:=myLinks(1), Type:=xlLinkTypeExcelLinks
        End If
      
        FileExt = ".xlsx"
      
        'saveas xlsx file
        wb.SaveAs SaveFolder & ThisFileName & FileExt, 51
      
        wb.Close False
      
        Set wb = Nothing
      
        'delete xlsm file
        Kill SaveFolder & ThisFileName
      
        MsgBox "Process Complete", 64, "Complete"
      
    End If
  
myerror:
    If Not wb Is Nothing Then wb.Close False
    'Turn On Screen Updating etc
    cES.SettingsOn
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
  
End Sub

Hope Helpful

Dave

 

[dmt32]

whoops, spotted an omission on this line after posting

If Len(myLinks) > 0 Then wb.BreakLink Name:=myLinks(1), Type:=xlLinkTypeExcelLinks

Dave

 

[rjmdc]

hi
with some adaptioin to original code it worked
you did set us on the right track