/ / Slet regnearks forespørgsler helt via Excel VBA - excel, vba, excel-vba

Slet regnearkspørgsmål helt via Excel VBA - Excel, VBA, Excel-VBA

Jeg har kode, der opretter en forespørgsels tabel. Efter at jeg har lavet det, trækker jeg ud de data, jeg vil have, og så er jeg færdig med den forespørgsel. Jeg vil ikke have en masse forespørgselsborde, der hænger sammen med at optage plads, så jeg vil straks slette den. Jeg bruger

QueryTables.Delete

ActiveWorkbook.Connections.Item(i).Delete

Problemet er, at forespørgselstabellen er stadig der. Så hvis jeg forsøger at lave en anden forespørgsel med samme navn, fortæller det mig, at der allerede eksisterer, og det kan ikke lade sig gøre.

Jeg vil have en oversigt over den tabel, der eksisterer, for at være helt væk.

Her er min kode:

Sub Macro8()

Dim currency1 As String
Dim currency2 As String

currency1 = ActiveSheet.Range("currency1")
currency2 = ActiveSheet.Range("currency2")

Range("clear").Select
Selection.ClearContents

ActiveWorkbook.Queries.Add Name:="FXFWD", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""http://www.investing.com/currencies/" & currency2 & "-" & currency1 & "-forward-rates""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{"""", type text}, {""Name"", type text}, {""Bid"", type number}, {""Ask"", type number}, {""High"", type number}, {""Low"", type number}, {""Chg."", type number}, {""Time""," & _
" type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Columns"""
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Book1"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=FXFWD" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [FXFWD]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "FXFWD"
.Refresh BackgroundQuery:=False
End With

Sheets("Book1").Rows("1:33").Copy
Sheet1.Rows("18").PasteSpecial xlPasteValues
Sheets("Book1").Select

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim con As Object
Dim ws As Worksheet
Dim qryT As QueryTable

For Each con In ThisWorkbook.Connections
con.Delete
Next con
For Each ws In ThisWorkbook.Worksheets
For Each qryT In ws.QueryTables
qryT.Delete
Next qryT
Next ws

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheet1.Select
End Sub

svar:

1 for svar № 1
For Each qr In ThisWorkbook.Queries
qr.Delete
Next qr

Beslægtede spørgsmål


Kommentarer (0)

Tilføj en kommentar