zakkarchiheb
New Member
- Joined
- Feb 16, 2023
- Messages
- 2
- Office Version
- 2021
- Platform
- Windows
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
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