excel data connection not updating (1 Viewer)

[hopsy24]

I have an excel spreadsheet that uses a data connection to a website to download text. I have the data connection properties to refresh upon opening the file. If i double click the file from windows, it updates. Although using the code below doesn't work. When i've added the code ObjXL.Save before closing it, it usually saves a file with no data and no cells. Where am I going wrong? I'm eventually going to switch visible to false.

Private Sub btnDownLoadSettle_Click()
Dim XLapp As New Excel.Application
Dim ObjXL As Excel.Workbook
Set ObjXL = XLapp.Workbooks.Open("C:\...\Settlements.xlsx")
ObjXL.Application.Visible = True
ObjXL.Windows(1).Visible = True
Sleep (5000)
ObjXL.Close
XLapp.Quit
End Sub

 

[mh123]

I think it's something like ObjXL.Connections("conn name here").refresh iirc - let me know if that does the trick

 

[hopsy24]

This worked, but not when when objxl.window(1) was False. With this code, you don't see any of the windows.

Dim XLapp As New Excel.Application
Dim ObjXL As Excel.Workbook
Set ObjXL = XLapp.Workbooks.Open("C:\...\Settlements.xlsx")
ObjXL.Application.Visible = False
ObjXL.Windows(1).Visible = True
ObjXL.Connections("stlags").Refresh
Sleep (6000)
ObjXL.SaveAs "C:\...\FinalSett"
ObjXL.Close
XLapp.Quit