Vba: Saving To A Network Shared Folder

Using VBA, how do I allow users to save files to a specific network shared location? Not all users are mapped the same, so the path is not consistant. The network location has a share name (cdsBulletins)

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Macro to get Data from Another Workbook in Excel
Macro to get data from a workbook, closed or open, over a network or locally on your computer. This is a versatile ...
Prevent Excel Alerts and Messages Appearing While Running a Macro in Excel
How to stop an Excel alert window or message box from appearing while running a macro. This is particularly useful ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...

Helpful Excel Macros

Output the File Path to and Name of a Workbook in Excel - UDF
- Free Excel UDF (user defined function) that displays the full file path and name of an Excel workbook. This function is
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Prevent Saving a Workbook under a Different File Name
- This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only

Similar Topics

what would the code look like to send a sheet into a shared network drive? I'm trying to design a macro that would submit a downloaded page back up the path to something that can be accessed by only a few users.


Hi, I have a shared workbook that functions as a calendar for a group of people (shared file, accessed over a network), with the date held in a single row, to save a bit of time I have created some VBA code to automatically center the users view on the current date when opening the workbook/sheet,

so far so good,

however the majority of the users of the file have macro's disabled by default (with notification enabled), and having them have to click through the security setting to allow the macro to run negates any benefit to having the code in place (they may as well just scroll to where they want on their own).

Is there a simple way to always allow the macro to run without any user input, trusted publishers and digital signatures seems a non starter, but would adding the file location to the list of trusted places (on a network) work?

also whats the easiest way to roll this (or any other solution) out across the user group (~30 users)

Thanks in anticipation.


I recently bought a Buffalo network drive so I can access my files from both
my laptop and desktop. This works perfect for my Word documents and even my
Outlook PST. The problem is when I try to save an Excel (2003) file to the
network drive I get a message like

File xxx.Xls is possibly changed by an other user since you last saved it,
do you want to overwrite it or to save with another name.

(My Office version is not in English so this is my translation of the

This only happens if I open an existing file, it happens even after I try to
save it immediately after opening. I can overwrite it so the file is not
locked (what would surprise me because there nobody else on the network)

It happens when saving files on both desktop or laptop.

The only way I found so bypass this message is to make every workbook shared
but that way I loos functionality.

I there a way to be able to save Excel files to a network drive without make
the Work book shared?


PS. I'm using Win Xp Pro on Desktop and laptop

We have a problem with a particular excel workbook and the fact that two users can both edit and save changes even though it is not set as a shared workbook.

The file is on a network share they both have the same access to directory and the same effective permissions to the file. The file is in 97-2003 format and one user is using 2003 and the other 2007.

I have tried everything I can think to get it so that if one user has it open the other one can only open it as read only but nothing seems to work, I have even tried saving it as html then saving it back to xls file format. I have tried setting the WB as shared then taking it off again still no joy.

This is really starting to bug me now but I don't know what else to try.

I have a small set of users (currently 4) that use a macro that I maintain to format downloaded data. The macro changes often enough that it is inconvenient to go to each PC and update the macro in each users personal.xls file.

Instead, I have placed a new document on a shared network folder that they all have access to and have created shortcuts to the document in each users XLStart folder. My hope was that I could then just update the macro in the file on the network drive and all users would have the updated version the next time they loaded Excel. (The document is hidden in Excel)

However, I have found that if one of the users has Excel open, and another user tries to open Excel on their computer, they get a message saying that the shared file is in use and would they like to open the file as read only.

I then tried to save the file using the Read-Only Recommended flag (Save As -> Tools -> General Options), but that still throws up a message about the file being read-only whenever anyone opens Excel.

So, is there any way to force an Excel document to open as read-only without prompting?

Also, of lessor concern, is there a way to protect it from modification without prompting for a password when Excel opens?

Chris Bloom

Hi all!
I've created some templates in a folder on the network shared drive, which
I would like other users to create workbooks out of it and not touch the
original templates.
In order to do so, I thought of inserting these templates into the General
Template inside the New... dialog box.
I've put the directory under Tools -> Options -> General -> At startup,
open files in. Then, I restarted Excel and by clicking New..., the templates
are displayed inside the dialog box. However, clicking on any of these
templates somehow does not generate any actions at all.
I've absolutely no idea why Excel is doing nothing upon clicking the
template. Rightfully, at least something should happen, like an error
message box. Would really appreciate any pointers or suggestions.

I have a general question. Is it possible that Excel users on serveral computers in one local area network can see permanently updated stock quotes that are provided by one Bloomberg Professional terminal in the same network?
The idea behind is that several users can open an Excel file that is on their local machine and which (the file) can be different from what other users have but all should get data from the Bloomberg terminal at the same time. Updates should be done via linked cells in all the excel sheets. Those links arrive at one excel file (that can be updated by the admin) being saved on a shared location in the network.

It would be great if you can provide me with information on this issue and maybe suggest a better way to realize that idea in case I am wrong!

Thank you very much!

Hi All,

As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist.

Is there any way to by pass this.

Ex - "TestFile.xls"

File is stored in "E:\Network\Team1\Testfile.xls"

However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file.

Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...Network\Team1\Testfile.xls".

Many Thanks


I have a macro that does a lookup from a file that is in a central network drive. There are multiple users who use the macro. I have shared the file so that everyone can be in it at once so the macro can do the lookups. The issue is that the users have the drive where the lookup file is as different drive letters.

So for example, I have the drive as O:/, but someone else might have it as T:/. So in the code, it opens up the file at the location O:/, but that drive is the wrong one for a different user.

Is there a way that I can reference the path based on the drive name (it is called CENTRAL DRIVE) so rather than O:/FOLDER, it could be CENTRAL DRIVE/FOLDER so that everyone could access the file regardless of his/her drive letter and wouldn't need to have the code modified for each user.

The only thoughts I had that I know how to put in would be to have a prompt pop up and ask for the drive letter and then have the user type in the drive letter and have the code pop the user response into the drive letter portion of the path.

Or similarly, have the drive letter reference a specific cell on a sheet and have the user fill out that cell with their drive letter.

I have a workbook, stored as Read-Only on a network, that has external data links (for vlookup functions) to files on the same network but different folders.

My problem is that the network path in the formulas seem to randomly change. I started with a full path, \\server\folder\folder\file. Occasionally the path will change to match my mapped drive, Z:\folder\file, but the data links usually still work.

Today it change to C:\folder\file, which is, of course, completely wrong and the data links won't work at all.

It's a large file with 100+ links. When this happens it costs me a lot of time to fix. Anybody out there know how I can prevent this? I'm using 2007, as are the other people in the office who use the workbook.


I have a network file the "needs" to be shared, however, it takes at least 30-45 seconds to open and be ready to input on when accessed and almost the same to save, and then another 30 seconds just to get it closed.

I have taken the same file off of shared mode, and it will open in seconds and be ready to input right away. Because the workbook grows month-by-month it takes a few seconds to save it, but not hardly noticable.

Users of excel 2003 are having less trouble than those using excel 2007.

any thoughts on this?

Hi there,

I have a few worksheets that as part of their equations they reference
other files on the company network. I have found though that sometimes
the path coming up is from the drive mapping (i.e. G:\SALES\etc) and
other times it is coming up with the full network path (i.e.
\\SERVER\SHARE\SALES\etc). When it is coming up with the network path
the formula then fails.

Any ideas?

Hi guys,

I currently have the following code:

Sub save()

ChDrive "S"
ChDir "S:\Folder"

cell = Range("A1").Value
Filename = Application.GetSaveAsFilename(InitialFileName:=cell, fileFilter:="Execel Workbooks (*.XLS), *.XLS")
If Filename = False Then GoTo finish

ActiveWorkbook.SaveAs Filename:=Filename

End Sub

This works fine in that it takes the value in A1, uses it as a file name and saves it to the folder on the S drive.

However, I have a problem. The S drive is a network drive and not everyone in the company has it mapped to the letter S. Therefore I think I want to use some code like this at the start so that it doesn't matter what letter people have the drive mapped to:

ChDrive "\\network1\shared$\"
ChDir \\network1\shared$\Folder\

I can't get it to work though! Does Chdrive have to just be a letter? If so, how on earth can I get this to work?

I hope that's clear and many thanks for any help at all!!



I have created and shared a workbook on my network at work. While some people have no issue making changes and saving, other cannot save and get this error when attempting to, referencing an (as far as I know) non-existent file.

The one variable I have discovered is that when I open the Share Workbook window the names of the people who cannot save show twice, where people who are not having the issue only show up once. These people do not have more than one instance of the workbook opened and everyone is running on Office 2007.

Does anyone have any experience with an issue like this?

I have an excel 2007 file sitting in a shared network folder. I only want one user to be able to make changes at a time (any other users would get a read-only). For some reason it currently does not do this, and I have multiple users with the same doc open. I'm concerned that changes will get over-written when 2 people are saving their changes. Can anyone help me with the settings for this.


We have a network share on a Win2K3 Server where users are trying to save
Excel workbooks to. They do not have the delete permission.
When Excel saves I believe it creates a temp file, then saves the .xls and
then tries to delete the temp file. If the temp file is unable to be deleted
the save fails.
(E.G. If the users have delete permissions they can save OK).

Anyone have a good workaround or fix for this?



I have a macro to upload data in the shared workbook everyday. Now if there is one user on the network who is using the shared workbook, there can be data corruption.

1.How can i write a macro so that i 'kick' out all the users who have this shared workbook open? Once i do that, i will be able to have exclusive right on that file and then do my loop from my macro to change this shared workbook.

2. if the shared workbook is open by other users, is it possible to copy the file to another folder through VBA. I understand it needs to be close to copy or open the workbook. any work around solution?

I have some code in my excel spreadsheet which runs very slowly when I am connected to my office network by the VPN link (through wifi). I want to put an if statement into my code to see if I am connected by the office LAN before running the code, or to see if I am connected by vpn to the network and not run the code.

I'm connected to the same network in both instances and I have only been able to pick up information associated with the network rather than with the connection, so I've not been able to find something that changes dependant on the method of connection. So far the only workaround I've come up with is to test the network speed by writing files to the network location and timing how long it takes.

Does anybody know of something I can access from within excel vba which would tell me how I am connected to the network? I'm using office 2003. Thanks in advance.


I have created an addin containing a macro which i have put on a network drive (K:\MY ADDIN\addin.xlam)

When i go to add it in excel it asks me if i want to copy it to the addins folder (locally) to which i say no, but then it doesnt work, and then the next time i restart excel, it says it cant find the addin and displays the LOCAL path it is looking in to try and find it.

Any idea how i can have it look for this on the Network path as the addin needs to be shared and updated by several people




I have a workbook located on my local laptop. The workbook contains a procedure that opens some excel source files from a network folder and copies data to the local workbook.

The issue is that the I need to change the drive in order to open the source file and I have no clue how to change the drive from local drive to network.

The folder is not mapped to a drive-letter. I would avoid mapping the folder, since a other users use a same workbook with the same code on their laptop assessing the same files.

Could anyone help me on this.

ChDrive ???
ChDir sFileDirectory


I'm trying to get a workbook to save locally if a counter is below a threshold and save to the network share if it is over.

The problem I'm having is that it doesn't appear to switch the directory to the local share when required, instead just saving it to the network share.

Yet when I step through it appears to do it - could someone shed some light on this please.


Sub TestSaveLocal()
Dim wbk As Workbook
Dim HomePath As String
Dim TeamPath As String
Dim SaveClick As Integer
Dim NextNetworkSave As Date
Dim SaveTimer As Date

Set wbk = ThisWorkbook

' This is grabbed from the workbook they are using, each team has their own

TeamNumber = ThisWorkbook.Worksheets("Daily").Range("H49").Value
    If TeamNumber < 9 Then
        TeamNumber = "0" & TeamNumber
        If TeamNumber > 10 Then
        ' Or if NewbieTeam
            TeamNumber = "Newbies\NewbieTeam" & TeamNumber
        End If
    'Fine as it is
    End If

' set the home path - all users have one locally in the format C:\Users Local Data\ & Application.UserName

HomePath = "C:\Users Local Data\" & Application.UserName
TeamPath = "\\XXXX\XXXX$\Teams\Team" & TeamNumber

' Start the Save Timer
If SaveClick = 0 Then
    SaveClick = 1
    SaveClick = SaveClick + 1
End If

'Check if the NextNetworkSave has been set yet, if not set it.

If NextNetworkSave = Empty Then
    ' Create new save point
    SaveTimer = Time
    NextNetworkSave = SaveTimer + "00:15:00"
    ' timer already set
End If

If SaveTimer  0 Then
    If Now < NextNetworkSave Then
        'Time to Save to Network Share
        ChDir TeamPath
	'Once saved return to the Local Share
        ChDir HomePath
	'Clear Timers
        SaveTimer = ""
        NextNetworkSave = ""
        'Just save it locally, unless...
        If SaveClick > 5 Then
            'Save over network
            ChDir TeamPath
            ChDir HomePath
            SaveTimer = ""
            NextNetworkSave = ""
            ' Save locally
            ChDir HomePath
        End If
    End If
ChDir HomePath
End If
End Sub

I need multiple users to share the same workbook, but the shared workbook functionality in Excel seems surprisingly limiting. For example, I would like the automatic updates more often than every 5 minutes. The other option is to get updates after the workbook is saved, but if one of the users wants new updates they have to wait until the other user saves their file...you would think one user would have some sort of option to get the other users changes without saving/opening/closing the file.

Anyway, there must be some alternatives or better ways to have multiple users share a workbook on a network. Some thoughts I had were (1) a macro that saves after every workbook change (2) a looping macro that automatically updates every 10 seconds ....what are your thoughts? How can we make the file as real-time as possible, so as soon as one person makes an update, the other person can see it. There will be NO OVERLAPING CELLS!


XL2K-Two of my customers are unable to edit a shared workbook. This workbook
is stored in a network folder where many users have been granted rights to
view teh document. The only two people that can make changes are the two that
have rights to do so but are unable to for some reason. We have checked the
folder and network rights and they both are correct yet are unable to edit
the document. What are we missing here? Would the document author ahve
anything to do with it since we all have our author defaulted to our company
name? Any suggestions would be great. Thanks.

How can I let multiple users update a single worksheet over a network? e.g. how can I let different users simultaneously (on a network) add new rows to existing rows of data?

I know there's a function called Share Workbook under the Tools menu. What happens if two users attempt to add data to row 10?

How do I deal with a situation where a user has added data to a row e.g. row 10; and another user seeing row 10 as blank and attempts to add data to row 10?

VBA Answers are also welcome

I'm looking to have an excel file in a shared folder in a small network 5-10 users.

1) If multiple users access the file at the same time, it will only save the user who saved the file last, correct? Is there any way around this?

2) Is there any way of knowing that a user has the excel file opened? Is there a way to make it (in whatever way.. altering the folder, using excel, etc.) so that the user knows if another user has the file opened OR to block the user trying to access the already opened folder?

Thanks in Advance!