How Can I delete the database connection Using VB Script after refreshing and before saving the file

[zakkarchiheb]

Hello ,

I use this VB script to automate the refresh of an excel file based on a SQL query !

My code works well but the line closing the connection contains a problem !

Here is my code !

MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & _
Right("0" & Day(now), 2)

Set oExcel = CreateObject("Excel.Application")

oExcel.Application.Visible = True
oExcel.Application.ScreenUpdating = True
oExcel.Application.DisplayAlerts = False

Set oWorkbook = oExcel.Workbooks.Open("***********\Pivot Sheet.xlsx")
oWorkbook.RefreshAll

Do While oExcel.ActiveWorkbook.Connections.Count > 0
oExcel.ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
Loop

oExcel.Activeworkbook.SaveAs "C:\Users\ChihebZ\Desktop\Macro vbs\Pivot Sheet_" & _
MyDateFormat & ".xlsx"

oExcel.Quit
WScript.Quit

your Help please

 

[johnny51981]

So here is the focused VBA for clearing the Queries and Connections

Sub ClearQueries()

Dim pq As Object

Dim q As String

For Each pq In ThisWorkbook.Queries

q = pq

ActiveWorkbook.Queries(q).Delete

Next

End Sub

 

[zakkarchiheb]

Sub ClearQueries() Dim pq As Object Dim q As String For Each pq In ThisWorkbook.Queries q = pq ActiveWorkbook.Queries(q).Delete Next End Sub

It solved my issue! Thank you for your support!