Refresh data in closed excel workbook

[mim]

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.

[Jacob Hilderbrand]

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]

[mariap]

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.

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]

[Kenneth Hobs]

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.

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