information about users selecting a folder at this link. However, how do I change the starting point to a network drive, as opposed to "My Computer"?
For example, we reference our network drives like this:
\OurSite.comSharedFoldersDivisionName
I am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
I currently have the following ...
View 8 Replies View RelatedIf you don't know what the drive letter will be, because on other people's computer it could be different than what you have.
I only have the folder address
\ROGERSDAILYREPORTSXLSDM
I am using the OnTime method to automatically send out a spreadsheet by email at a certain time. I am using Windows task scheduler to start up excel and OnTime to execute the email. I have got this to work on my local drive however I need to save the spreadsheet on a network drive. Task scheduler opens this up still, however OnTime doesn't seem to be working as I have tested it and the email doesn't get sent.
View 3 Replies View RelatedWhen inserting a hyperlink in an Excel cell, the default pathname for the new link is always the current pathname of the workbook. After navigating to a distant pathname and inserting a hyperlink, inserting the next hyperlink in the next cell starts all over again at the current path.
Is there a method of telling Excel to start looking in z:\ABC instead of the current path? This would eliminate navigating through the entire path for each link.
A macro that handles the hyperlink and puts up a navigation window or something like that would be ok.
I want a simple VBA code that when run will check if a particular spreadsheet that exists on a Network Drive is already open or not by another user. Imagine there are 3 users A, B & C
The network path for example is : S:Department1TeamAConsolidated Report"
The spreadheet name is : Consolidated Database.xls
If the spreadheet that is on S:Department1TeamAConsolidated ReportConsolidated Database.xls is already open by User A then User B should get a message that "File is already open"
I have the below code which I got from a website which I kind of tweaked but it doesnt work.
Dim WbookCheck As Workbook
On Error Resume Next
Set WbookCheck = Workbooks("Consolidated Database.xls ")[code]......
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
I am having trouble creating a hyperlink to a place in the current document for a file stored on a network drive. My workbook has a lot of sheets (50 or so), so I created a 'Navigation' sheet that contains hyperlinks to all the worksheets located therein, and pasted a link back to the Navigation sheet in cell A1 on every tab.
Setting up these Navigation sheets has never a problem until I started posting the file on a network drive. As soon as I did that, I noticed that the internal hyperlinks on all sheets in the workbook would stop working after I closed the file in Excel. Note that this network drive is not set as a drive letter on my computer, and is only accessible by entering the server ip address in Windows Explorer.
An example address the hyper is trying (and failing) to open is: "file:///\ IP ADDRESS sub-folders activefile.xlsm"
I've tried a few options, using the HYPERLINK formula, using the HYPERLINK feature in the Insert ribbon, etc, but all to no avail.
I currently have a password secured excel file on my departs Q drive, which everyone can access unfortunately only 1 person can edit at a time. I tried to turn it into a workbook so we could all edit it at the same time but now some of the other users can't access the file at our other buildings throughout the county. This is for excel 2010. And I did select the share workbook, and allow multiple users to edit box. everything else is unchanged.
View 3 Replies View RelatedI am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation TeamDC Tools" &
"Copy DC Conversion WB_2014 " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlNormal
I have also tried:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation Team
DC ToolsCopy DC Conversion WB.2014_" & _
Format(Now(), "YYYYMMDDhhmmss") & ".xlsx", FileFormat:=56
But still no avail. I believe it may have something to do with the file format but I am not sure. I am running Excel 2010.
I have created a browse button in excel and assigned a macro which will ask the user for folder. I figured it out for windows using Windows API Functions as mentioned at http://www.cpearson.com/excel/BrowseFolder.htm. However cannot get it to work for a Mac.
View 2 Replies View RelatedIs there a way to incorporate a prompt that allows the user to browse a folder. The code I am using is below but 'myDir' will change each month.
Sub CombineWorkbooks()
Dim myDir As String, fn As String
Dim WSName As String
Dim WB As Excel.Workbook
WSName = "Summary"
On Error Goto Nxt
'turn off screen refresh for speed
Application.DisplayAlerts = False
Application. ScreenUpdating = False
myDir = "C:Users\DocumentsUsersWendyNew Folder"
fn = Dir(myDir & "*.xls")
I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.
The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?
Excel 2002 SP3
When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).
If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.
I have created a button on an excel form in the 2003 version and i want to create a macro that opens up a network path in windows explorer such as 'open W:
ew folder
ew share'
I found some code that works well to index a folder and all sub-folders for file names.
The issue is that the folder used is coded in an excel spreadsheet (cell C7) in the below code and whether to look in sub-folders (cell C8 - True or False).
Code:
Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
[Code]....
I have tried to incorporate the browse for folder and select if sub-folders are used, but it isn't working.
I'm having some trouble showing a messagebox if the user presses OK but doesn't select a folder using msoFileDialogFolderPicker.
[Code] .....
The 2 MsgBox's I've added show the same string, yet the If Not InStr line returns true regardless if ":" is in the string or not.
I've made a macro that would create some folders on a shared drive.
there is an issue if the path i want to create is too long.
I get this error.
Uploaded with ImageShack.us
is there anything we can do to pass this or any solution for it?
I am trying to allow users to select a file, and then the file name/ the file path / and date are added.
Right now, all those functions work in the following code, but the file path shows up as a letter drive. As there could be upwards of 50 people using this file and the mapped drive could be different, i wanted the full path to be displayed.
Code:
Private Sub File_1_overwrite_Click()
Dim filename As Variant
Dim filename1 As Variant
filename = Application.GetOpenFilename(, , "Select Programme")
filename1 = filename
filename1 = Mid(filename, InStrRev(filename, "") + 1)
File_1_link = filename
file_1_name = filename1
File_1_date = Format(Date, "MM/DD/YY")
I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.
"http ... /PMO/Shared%20Documents/ Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"
I am trying to get the user to choose which disc the file structure should be stored in. Is it posible to get a dialog box which contains all the disc drives? I tried using the GetOpenFileDialog, but it seems I must choose a file. I must only choose Disc Drive.
View 5 Replies View RelatedI have run into a problem in a project where I know what the folder root of the file I would like to import into excel is, but I do not know the file name.
I would like to open a dialogue open where I can select the file I wish to import from the known root supplied and retain the file name as a string.
All solution I have read assume the full file root is know or file name is known but root is unknown
I am working with Excel 2003.
I have read your loop instructions under FAQ entitled; Loop through a folder of workbooks. It works very well on a local drive but I need to have it loop through a folder on a network drive. the drive is mapped as h:\computer name2008shareddocs
View 2 Replies View RelatedI want to ask about the network folder. The current systems that i develop contains about 15 excel workbooks and every workbooks link with each other. If i place all the workbooks into the network folder, does all the formula, link and vba will be changed to network too?
Such as the formula: ='G:systems[workA.xlsm]A1.
Does it will change the path of the formula? or i have to change all the formula, hyperlink and vba manually? second, how should i place the workbooks in network folder in a right way? do i need to change any setting at excel file?
I am trying to figure out if there is a way to add up all the results of all spreadsheets within a single folder, either from a local drive or from a network drive?
View 1 Replies View RelatedScenario; I have 3rd party software that pulls reports in .csv files. I want to summarise this data into another spread sheet. Ideally I would like to save these files into a folder on the network and my summary workbook simply looks for this folder and is able to find data within all Excel files saved in there (new files will be added on a monthly basis).
Once it has found the folder there will be 2 pieces of data to evaluate; Cell $B$1; this will be the name of a course (e.g. health & safety, money laundering etc.) Column $D; this will contain the score achieved by multiple learners.
The summary spreadsheet then averages all of the scores for the different courses(in seperate cells)
The idea is that the .csv files can be pulled and saved into said folder and eliminates the need to manually input this data into the summary spreadsheet.
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
View 6 Replies View RelatedThe script below scans through a specified folder on a network and scans every xls file inside that folder for a value. When it finds the spreadsheet which contains that value (a policy number) it outputs some of the data from the matching file and also provides a link to the sheet in question.
It worked perfectly until I saved, exited and re-opened the file and now I am getting a 1004 run-time error every time I try and execute the script.
I cant for the life of me work out a cause.
More baffling, the wording of the error says that the file 'R:DirCancellations.xls' could not be found.
At no point is a file called 'Cancellations.xls' specified. Why has the script decided to search for a file called 'Cancellations'?
Sub Search()
ActiveSheet.unprotect
ActiveWorkbook.unprotect
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim DirLoc As String
Dim PolNum As String
ActiveSheet.Range("C13:G25").ClearContents................
