PDA

View Full Version : Refresh data in closed excel workbook



mim
07-25-2013, 07:05 AM
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
07-28-2013, 10:44 PM
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.


Set Wkb = Workbooks.Open(Filename:="C:\SubFolder\Filename.xlsm")


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


Wkb.RefreshAll

mariap
07-30-2013, 05:16 AM
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()

Set Wkb = Workbooks.Open(Filename:="F:\Clients\PRCB\medium\connection.xlsm")


Wkb.RefreshAll

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.


Set Wkb = Workbooks.Open(Filename:="C:\SubFolder\Filename.xlsm")


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


Wkb.RefreshAll

Kenneth Hobs
07-30-2013, 07:22 AM
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