Excel VBA导出到Excel-删除连接 [英] Excel VBA Export to Excel - Removing Connections

问题描述

我有一本工作簿，其中包含一些要通过VBA Script导出为ex​​cel的工作表。我要导出的工作表中有一个数据库连接以提取该报告期间的值。当我导出到excel时，我注意到与数据库的连接仍然存在。我可以使用一段代码来导出工作表中的值但删除数据库连接吗？以下是我目前每天用于导出报告的脚本。谢谢！

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

推荐答案

也许符合以下情况：

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

在您的情况下，您似乎需要将与ActiveWorkbook一起使用。请注意，删除索引 i 最终会出错，因为删除操作更改了正在迭代的集合： i 将最终大于集合的大小。

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.

或者：

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

这篇关于Excel VBA导出到Excel-删除连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！