Refresh data in closed excel workbook

Hi,
I have an excel file connected to a SharePoint list. What I need to do is automaticaly refresh the file every night no metter it is opened ot closed by any user.
I expect it will be closed at the time wneh it should be refreshed.

I hope it is possible.

Thank you.

You would need to open the files, refresh, then save. If they are opened by another user then this would not work.

When dealing with SharePoint I usually will map a network drive to the SharePoint folder. So for example if I map X:\ to the SharePoint folder then I could open the workbook like I normally would in a Windows folder.

[VBA]
Set Wkb = Workbooks.Open(Filename:="C:\SubFolder\Filename.xlsm")
[/VBA]

Depending on the data you want to refresh the following might work:

[VBA]
Wkb.RefreshAll
[/VBA]

Hi, an thank you. I had some problems with my password.

I put this code, but it doesn`t work :(

this is the whole code:

Sub testconnection()
[VBA]
Set Wkb = Workbooks.Open(Filename:="F:\Clients\PRCB\medium\connection.xlsm")
[/VBA]
[VBA]
Wkb.RefreshAll
[/VBA]
End Sub

I suggest that i am wrong somewhere.

Quote:

---

Originally Posted by DRJ

You would need to open the files, refresh, then save. If they are opened by another user then this would not work.

When dealing with SharePoint I usually will map a network drive to the SharePoint folder. So for example if I map X:\ to the SharePoint folder then I could open the workbook like I normally would in a Windows folder.

[VBA]
Set Wkb = Workbooks.Open(Filename:="C:\SubFolder\Filename.xlsm")
[/VBA]

Depending on the data you want to refresh the following might work:

[VBA]
Wkb.RefreshAll
[/VBA]

---

Please use Code tags rather than VBA now.

Obviously, you need to add the password option parameter values. Try the refresh parameter as true as well.

Code:

---

Sub ken()
  Dim wb As Workbook
  Set wb = Workbooks.Open("x:\t\book2.xlsx", True, , , "ken", "ken")
  wb.Close True
End Sub

---