Excel VBA Export to Excel - Removing Connections

Question

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

score 1 · Accepted Answer · edited Nov 09 '18 at 15:50

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

edited Nov 09 '18 at 15:50

Mathieu Guindon

69,817
8
107
235

answered Nov 09 '18 at 15:00

Kubie

1,551
3
12
23

Simpler: `For i = 1 To .Connections.Count : .Connections(1).Delete : Next` – Mathieu Guindon Nov 09 '18 at 15:17
@MathieuGuindon touché sir – Kubie Nov 09 '18 at 15:18
Thank for the reply. I added the script I am currently using at the moment. I am exporting 3 sheets. 2 of which have a database connection pulling in the data that is needed. Is the snipped you provided still valid in that situation? Where would I slot this in the script as it exists? or should I need to define the specific Sheets to remove connections from? – Jason Yuen Nov 09 '18 at 15:27
@MathieuGuindon Hi there. I am getting a compile error. I added your line after my Remove Formula section of the script. 'Removes Connections Dim i As Long For i = 1 To .Connections.Count: .Connections(1).Delete: Next i – Jason Yuen Nov 09 '18 at 15:31
@JasonYuen the `.` implies a `With` block, like in the answer here. Also the `:` colons should really be line breaks; I used instructions separators because I can't do line breaks in a comment box. – Mathieu Guindon Nov 09 '18 at 15:33
@Kubie for "Target Workbook Name" is that the name of the workbook I am running this from? I'm not clear on this. – Jason Yuen Nov 09 '18 at 16:06
@JasonYuen its the name of the workbook you want to remove the connections from. If you want to remove connections from active workbook that's selected by your macro, you can change that line to `With ActiveWorkbook`. If you want to remove connections from workbook that your VBA code is sitting in (could be the same workbook as ActiveWorkbook), then you can try `With ThisWorkbook`. Otherwise, you would need to provide workbook name of book you'd like to remove connections from. – Kubie Nov 09 '18 at 16:09
@JasonYuen FYI you can mark the answer as "accepted" by clicking the hollow green checkmark, just underneath the up/down voting buttons near the top of this answer. – Mathieu Guindon Nov 09 '18 at 17:04

Excel VBA Export to Excel - Removing Connections

1 Answers1

Linked