Open file, delete connection and save as

[rins]

Hi,

Could anyone please help me figure out what is wrong with below VBScripting and how do I correct it?

It meant to
1) Open excel -- works
2) Refresh all data -- works
3) Remove external connection -- Dont work
4) Save as new file without any external connection -- Dont work due to 3rd step

Set oExcel = CreateObject("Excel.Application")

oExcel.Application.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False

Set oWorkbook = oExcel.Workbooks.Open("file path.xlsx")

oWorkbook.RefreshAll

oExcel.ActiveWorkbook.Connections("connection name").Delete

oExcel.Activeworkbook.SaveAs "NewFileName_"& _
MyDateFormat &".xlsx"

oExcel.Activeworkbook.Close
oExcel.Quit
WScript.Quit

 

[Zot]

Maybe something like this:

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

 

[rins]

Maybe something like this:

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

Hi, that worked just fine. Thank you very much for your help.

Out of curiosity and just for my knowledge, rather than deleting workbook connections- is it possible to remove external table link from the file?
so when I was recording macro below came up as to remove external table data from the file - would there be any vbs equivelant coding for below?

ActiveSheet.ListObjects( _
"My SQL data table").Unlink

 

[Zot]

Hi, that worked just fine. Thank you very much for your help.

Out of curiosity and just for my knowledge, rather than deleting workbook connections- is it possible to remove external table link from the file?
so when I was recording macro below came up as to remove external table data from the file - would there be any vbs equivelant coding for below?

ActiveSheet.ListObjects( _
"My SQL data table").Unlink

I'm no expert on vbs ... sorry