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)
Similar Excel Video Tutorials
Budgets, Scenarios & Scenarios Report
- Create a Budget with Formula Inputs in an assumption area and formulas. Then do what if analysis by saving and showing scenarios. Save Variables for a ...
Helpful Excel Macros
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
In Excel 2003, we've got departments that use files on shared network
folders, mapped as drives.
When we open a file that's on the local computer, make an update to the
data, and then try to save the updated file to the network location
overwriting the existing file, there's a long delay after which there's an
error and Excel is unable to save. I get an error that the disk is full. it
I also get a windows error that there's been a delayed write error and that
I should check the network.
I don't think it's a rights issue, since we're able to copy the files using
explorer without any problems and the same users were able to do this with
the previous computers running windows and office 2K.
I've tried to recreate the problem with MS Word 2003 and it's able to save
and overwrite files to the same location, on the same shared drive without
any problems, so I'm pretty sure this is confined to MS Excel.
Key West, FL
I have two independent questions:
(1) I have a macro that I'm making in VBA that is going to create 12 separate files (macro files for another program). I'd like for there to be a way that the user can open a box and choose the folder path where these individual files would be saved. Is there a way to do that that is not too terribly complex?
(2) Somewhat similar to #1, except say I want to automatically choose a location to save the files, and that location is on a network. Since each user might have that network drive mapped to a different drive, I cannot so simply use a path like H:\Folder names because H:\ might not be the right drive for every user. Is there a way to detect the drive letter of a specific network drive or is there some other way I can accomplish this?
I have an Excel spreadsheet on a network. I am using Macromedia Dreamweaver MX to connect to the folder where the document is located. My problem is I want different users to be able to access the form and save it at its network location. As it is now, there is only a save as option. The user has to go to the path to the document on the network. There are other documents in the folder that can be overwritten as well, which of course, I don't want to happen. How can I save the document without having the user go to the network place? Thanks.
I use my files on a network share. there is only 2 pc. I shared some of
folder on the other pc and mapped that pc as network drive. I opened excel
files, add some information on it but when I saved it a dialog box opened. (
this file is read only files you have to save it with another name) so I
couldn't save my files on the other computer. I controlled the boxesbut none
of them ticked. It seems everything is normal. Have you get any idea about
a shared workbook with links to a common network drive:
the scripted mapping for all users is M:\servername\share.......\folderA
some users have an additional drive mapped to the same location, but
N:\folderA or L:\folderA
if the workbook is opened in M:\servername\share.....\folderA
Prompts for an update, client clicks yes, error message "this workbook contains one or more links that cannot be updated", client chooses edits links, the edit location points to M:, and beside the worksheet links, it has "error source not found or worksheet not found."
When opening the workbook from the N: (or L, the following will happen:
Pprompts for update, clicks yes, and the spreadsheet opens normally. However, the location of the links are all pointing to N: (or L
the question is: is there a way to make the location static to the M:\servername\share....\folderA location (all users are mapped here)
no matter how they open the shared workbook?
hello all. I just had a question and am trying to figure a solution. A user wants to share an Excel file that has hyperlinks to other files with several users. some of these are from outside of the company so saving this to a network share is not an option.
When the file is moved from PC to PC, the location can change even if placed in My Documents folder. Is there any way that I can set it to either have a wildcard in place for the username or if there is a code I can add that will update the location?
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.
Is there anyway that 2 users can access a single excel file that is shared
over network in a shared folder and both be able to manipulate the file at
the same time. The reason being is we have one file that 2 offices needs to
have open and occasionaly change whilst open.
Thanks for any help with this.
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.
My organization provides network storage space for every user, as a default maps that location as "H:", and then assigns "My Documents" to that drive. I think users can change that but to date haven't found anyone who has. That said, to be robust, I need a routine that will:
1) Find the mapped drive of "My Documents"
2) Look for a specific folder there, and if it doesn't exist, create it
3) I then write files to and read files from that folder (I can figure this part out)
One of our staff is having problems with a xlsx file saved on a mapped network drive S:\
Every now and then when he saves the document appears to update the location of the linked files by itself.
typically the linked location should be s:\groups\...., however sometimes it reverts to the hostname \\nas\datashare\groups\... which also works. However, sometimes it changes the location to \\nas\groups\.... dropping the share that the files are actually located in which results in a big Error: Source not found when the links are edited when the file is reopened. This is even more problematic because the 5 or so files that are linked take about 30 minutes in total to re-link, which have to be done in increments as the files start linking as soon as each file is re-attached blocking further action.
The files are always edited from the same office, so the network location should not be changing, and obviously we're using Office 2007 with the latest service packs and updates applied.
I've created a database that is being accessed by multiple people over a network and some of the users are experiencing lag due to their physical location having a slower connection on the network.
I'm investigating ways to speed up the performance of the database and one article I found suggested:
"Share only the tables in the Access database You can put only the tables on a network server, and keep other database objects on users' computers."
Are they simply talking about linked tables? Or is there another way to achieve this? Experiences I've had in the past with linked tables over a network have not been much better.
Hi. I am having problems understanding how to apply correct security on a network level DSN or ODC for Excel users:
I have a group of users who need to use a connection (dsn or odc or you tell me) in a network folder location and get a table (or sql statement) back from our sql server. I have created a odc using a sql login info and put it in the network folder and gave the group "read & execute" permissions but they can't open the connection in excel due to permissions but if I add "read" rights then they can use the connection but also open the file in notepad and see the username as password and I can't have that either. How can I allow them to use the connection in Excel but not to open the file up to see the authentication and sql statment in the file?
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
Might be a dumb question but...
Is it possible for 2 users on a network to work on the same file at once?
I have used it before on a network and the 'locked for editing' message comes up if 2 try to access
I was looking for a way that the file can be shared without 1 having to keep closing the file before another can update it
I realise the conflicts but just thought there might be a way - I would try it myself but l don't have a network here.
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'm having a problem with a shared workbook which resides on a network drive. Most users have this drive mapped to M:, and others to K:.
I cannot alter the drive mappings because my company is using Novell. At startup a script is executed which maps network drives. I can change the drive mappings within windows explorer, but obviously at next reboot Novell overwrites these mappings!
The workbook has a vba procedure, which opens another workbook and carries out several procedures, and then closes when the user saves their changes.
This is where the problem lies. Because I have hardcoded the file path pointing at M: within the vba procedure, users mapped to K: obviously receive a run-time error.
Is there a way to dynamically retrieve the correct file path? The second workbook which is opened by the first resides in the same folder.
Hope this makes sense?
Because our compnay uses at least two different versions of MS Office, we
have found that network files, with macros, created by one user do not run
the macro correctly for other users. Even when both users have the macro in
their local XLSTART folder. This is because the XLSTART folders do not follow
the same path. Is there a way to change the XLSTART folder location? I see a
path in the registry but I do not want to change it if it will harm other
Was wondering, I use some VBA that does many things and in the end it exports the files to a network drive and as a last step, it creates a Log, the log is basically one row for each file exported which includes the path and file, something like this:
Row 1: Headers...
Row 2: \\network\path\to\drive\folder\Document_file_name.xls
Row 3: \\network\path\to\drive\folder\Document_file_name2.xls
Row 4: \\network\path\to\drive\folder\Document_file_name3.xls
So, basically looking for a way to start at row 2 to end, and delete the files. Looking for a way to also maybe save/and archive them on my local Desktop on XP ?
I have an excel which would be update by users in india & china ,till now the users will put the excel sheets in the ftp folder and one person will get the datas from the two excel and merge it.
since it happens in a daily basis we tried to avoid this repeatation work by keeping the excel sheet in the FTP and making it as a shared one but while saving it allows only to save as a another file even after giving full read/write access also.
since both are in two different network i have to use FTP.
Is there any other way to solve this problem.
I have workbook setup that uses a set of variables stored into stings. The way I have this script working right now is the variable becomes part of the file name when saving
. Ex. File Name: "2010-Assessment-Data- For-" & (Var1) & (var2) & ".xmls"
I was able to make this set-up work and save the file to the desktop but I am looking for a little more. I want to be able to save this file to a network path like: "S:/Clients\Holding File\**Files**"
Does anyone know first who to test if the S: network is even active?
If no network active then create a folder on the desktop Named: "Clients" Even if its already there "Prompt to overwrite"
Can this be done while at the same time allowing the users an out "Exit Sub"
Any ideas would great,
I am attempting to use the sumif function to pull some figures of an Excel sheet on our network at work. I have the following formula:-
=SUMIF('\\whysvr01\shared\Steelmaking&Casting\ReportSystems\ReportCaster\PC 24 hr Report\Folder 07\[08 Aug 07.xls]2'!$BJ$11:$BJ$55,"Combi",'\\whysvr01\shared\Steelmaking&Casting\ReportSystems\ReportCaster\PC 24 hr Report\Folder 07\[08 Aug 07.xls]2'!$BI$11:$BI$55)
which is returning #VALUE!
If I use the mapped drive address I get the correct value returned. I am using the network address sucssesfully elsewhere so as I can use this sheet from any terminal on the network. Any help would be greatfully apretiated as this is driving me nuts.
I'm using Office 2003. I would like to know how to save a file as Read Only, then re-save over it on a nightly basis.
Every night, I save all the work I do on the shared drive using a "Timer" on Excel.
Application.OnTime TimeValue("22:00:00"), "Save_Common_temp"
The Macro runs this code:
Workbooks.Open Filename:= "\\BCAFS01.BCARENA.COM\USERS$\TherrienA\Desktop\Other\Renewals & New Biz\0910 New Biz.xls" _
ActiveWorkbook.SaveAs Filename:="S:\Common\Temp\Andre\0910 New Biz.xls"
This basically saves my file over the Network file every night at the same time. The issue is that the shared file is often left open, not allowing me to save over it. If I save it as "Read Only" I can't save over it either.
Any ideas on how to be able to save my updated files on the Shared Network without interruption?
I currently have the following code:
cell = Range("A1").Value
Filename = Application.GetSaveAsFilename(InitialFileName:=cell, fileFilter:="Execel Workbooks (*.XLS), *.XLS")
If Filename = False Then GoTo finish
This works fine in that it takes the value in A1, uses it as a file name and saves the file to the folder called '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.
I therefore need a macro that will save the file to the network drive location, rather than the 'S' drive.
I've tried changing the top lines to this:
But it doesn't work and I'm now all out of ideas!
Does anyone know an easy way this could be solved? I've been trying for ages to get it sorted!!
I hope that's clear and many thanks for any help at all!!
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?