Excel VBA Export to Excel - Removing Connections

问题:

I have a workbook with some sheets that I am exporting to excel via VBA Script.One of the sheets i am exporting has a DB connection to pull in values for that reporting period. When I export to excel, I notice that the connection to the DB still exists. Is there a snippet of code I can use to export the values in the sheet but remove the DB Connection? Below is the script I am currently using to export a report daily. Thank you!

Sub refreshsummary()

    ' refreshsummary Macro

    Dim strdate As String

    'Refresh Data Summary View
    Sheets("Data").Select
    Range("b1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Fill Down Formula for Summary Data
    Sheets("Data").Select
    Range("A2:A10000" & LastRow).Formula = "=B2&"" ""&C2&"" ""&E2&"" ""&F2&"" ""&G2&"" ""&D2"

    'Refresh Data Export View
    Sheets("Data Export").Select
    Range("a1").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

    Sheets("Control").Select
    strdate = Format(Range("c2").Value, "mmm-dd-yyyy")

    ActiveWorkbook.Save

    'excel read only
    Application.DisplayAlerts = False
    Sheets(Array("Template", "Data Export", "Sales Breakdown")).Copy

    Dim ExternalLinks As Variant
    Dim x As Long

    'Create an Array of all External Links stored in Workbook
    ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    'Loop Through each External Link in ActiveWorkbook and Break it
    For x = 1 To UBound(ExternalLinks)
    ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
    Next x

    'Removes Formulas
    Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        With sh.Cells
            .Copy
            .PasteSpecial xlPasteValuesAndNumberFormats
        End With
    Next sh

    ActiveWorkbook.SaveAs Filename:="MYFILE.xlsx", FileFormat:=51, CreateBackup:=False

'End If

End Sub

回答1:

Maybe something along these lines:

With Workbooks("target workbook name")
    For i = 1 To .Connections.Count
        .Connections(1).Delete
    Next
End With

In your case, it looks like you'd need to use With ActiveWorkbook. Note that deleting index i would eventually run into an error, since the deletion is altering the collection being iterated: i would eventually be larger than the collection size.

Alternatively:

With Workbooks("target workbook name")
    Do While .Connections.Count > 0
        .Connections(1).Delete
    Loop
End With