user2704742 user2704742 - 2 months ago 9
MySQL Question

Make a Export button that copy's query table to a new workbook in desktp and removes data connections

Hello I have for sheets in a workbook with each sheet having its own query table from MySQL. These sheets also have some user form button on them like refresh, Go to first row etc. They also have few calculated columns.

I want to create a Export button that makes a copy (paste only values) of the entire workbook without the data connections and form buttons.

Answer

The code below will run through all existing sheets in your workbook, it will copy the entire sheets cells (values only, without Form Controls or links) , and will paste them in the same structure to another workbook that is saved on the Desktop.

Sub ExportCleanDataSheets()

Dim Sht             As Worksheet
Dim DestSht         As Worksheet
Dim DesktopPath     As String
Dim NewWbName       As String
Dim Wb              As Workbook
Dim i               As Long

Set Wb = Workbooks.Add

' set path to Dektop
DesktopPath = "C:\Users\" & Environ("USERNAME") & "\Desktop\"

' modify the name (I used "Clean_SQL_Data" and current date and time) to your needs
' i like to use the full date and time format, this way I don;t overwrite old files
NewWbName = "Clean_SQL_Data " & Format(Now, "yyyy_mm_dd _hh_mm_ss")
i = 1

' scroll through all sheets and copy the values only to the new workbook
For Each Sht In ThisWorkbook.Sheets

    If i <= Wb.Sheets.Count Then
        Set DestSht = Wb.Sheets(i)
    Else
        Set DestSht = Wb.Sheets.Add
    End If

    Sht.Cells.Copy
    With DestSht
        .Cells.PasteSpecial (xlPasteValues)
        .Cells.PasteSpecial (xlPasteFormats)
        .Name = Sht.Name
    End With

    i = i + 1
Next Sht

Application.DisplayAlerts = False

Wb.SaveAs Filename:=DesktopPath & NewWbName, FileFormat:=xlNormal
Wb.Close

Application.DisplayAlerts = True

End Sub