Linking to workbook on a server.

ovadeso

Board Regular
Joined
Sep 23, 2004
Messages
150
Hi,
If someone could provide me with a solution for this it would be much appreciated. I have some excel templates that dump information into another local excel workbook (collection.xls) on the sheet named "datasheet". I use a button on the template with the following code to carry over information from specific cells in the template into specific fields in the "datasheet":
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim x As String
Dim rngSheetName As Range
Dim c As Range
Dim ws As Worksheet
Dim rng As Range
Dim wb As Workbook
Dim source As String

Set wb = Workbooks("Collection.xls")
Set ws = Workbooks("Collection.xls").Worksheets("Datasheet")
Set rngSheetName = ws.Range("G16:G40")
source = ("Template.xls")

My question is, what if the workbook "Collection.xls" was not local and was on a server, how would I modify this code to point to it? Is it possible to do this remotely with excel and would I be able to maintain two way communication between the files. Thanks
 
By "server" do you mean a SharePoint server or a any other server? I have made this work on a SharePoint server. Others I am not sure.

In the code your example the workbook "Collection.xls" is assumed to be open. What in the case of server workbook. Will it be open or do you want the code to open it?

Try this:
Code:
Dim strServerFileName as String
strServerFileName = "http://myserver.com/mylocation/etc/etc/Collection.xls"
 
'if file is not open
Workbooks.Open strServerFileName 
 
Set wb = Workbooks("Collection.xls")
Set ws = Workbooks("Collection.xls").Worksheets("Datasheet")
Set rngSheetName = ws.Range("G16:G40")
source = ("Template.xls") 
 
wb.Save
 
Upvote 0
Thanks for that answer. I'm running this on my local computer now, usually with the workbook open, not yet using a server, but I'm thinking about doing so, in order for users from different locations to be able to send information to the collection table as well. When I do, I think I might just use SharePoint server since you mentioned it.
I assume I will get most of the information in this line from the server itself, once its setup:
"http://myserver.com/mylocation/etc/etc/Collection.xls"
I'm about a month or so away from setting up the server, so I will get back to you when I do.
I think I would leave the workbook open on the server, like I do on my local machine.
Thanks.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top