Reference Cell's Value In Directory Path Name?
Apr 23, 2013
I have a worksheet (sheet1 in book1.xlsx) that references another worksheet (sheet2 in book2.xlsx) in a different file. The latter file, however, is stored in a directory that changes. Call it MYPATH for the time being. I'm trying to use a formula (not macros, which aren't allowed in this office) to reference the directory path using the dynamic name.
For example, in the first worksheet, cell A15 contains the word MYPATH. I'd like cell A1 to have a formula like this: ='C:&$A$15&subdir[book2.xlsx]sheet2'!B50
so that if I change the value of cell A15 in book.xlsx to ANEWPATH, the path reference will switch to the book2.xlsx in ANEWPATH, not MYPATH.
View 3 Replies
ADVERTISEMENT
Oct 2, 2011
I have a few files all with the same name for a few different members of staff, so I save them in different paths to keep them separate.
eg
c:documentsdatajo blogsexcelfile.xls
c:documentsdatafred smithexcelfile.xls
c:documentsdatajane jonesexcelfile.xls
cells is the sheet contain the persons name
jo blogs
fred smith
jane jones
I want to be able to use this for others in the organisation and make it a simple as possible
Is there a way to use the cell contents in the path name (without opening the sheet first)
I know I can point to each cell in the other file and excel will use it but there must be a way of making this happen more automated by using the cell contents
View 5 Replies
View Related
Oct 16, 2008
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
End Sub
View 7 Replies
View Related
Jul 27, 2007
I've seen many posts advocating the use of BrowseFolder.zip located at
[url]
And some others saying to use the FileDialog object (if you use Excel 2002, or older(?))
I have to admit both are above my prior experience level, but I think I can do it if I understand what their function, requirements, and output are.
All I need is a dialog box that preferably lets me select the default directory, and then allows the user to select the target directory.
View 9 Replies
View Related
Mar 9, 2007
I've been trying to get the network username and found the solution on a previous thread. I have got to the point where I have copied the network username to my workbook. I now want to save this workbook into My Documents using that value for the username. So, so far i have:
'Get username from network
Dim User As String
CUser = Environ("username")
Cells(5000, 9) = Environ("username")
user = Application.ActiveCell
' Protect workbook and save in My Documents
ActiveSheet.Protect
ChDir "C:Documents and SettingsXXXXXXXMy Documents"
ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsXXXXXXMy DocumentsSalary Review Data 2007 - " & country & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
The user who saves this workbook, will be doing so on their own PC's so i need the file to save to their 'My Documents' folder. So i need to replace the red X's with the user's network username (which I have extracted and dim'd as User. How do i write this into the path of the directory?
View 2 Replies
View Related
Jun 10, 2008
This should be so easy, but I know I am missing something. I know that you can only create one directory on one statement line at a time with MkDir. in the "var_dir_path" resides a part number that changes dynamically.
Private Sub CreateFolderFromCellPath2_Click()...
View 6 Replies
View Related
Jun 12, 2008
Customer directory inside the "masterfile drive". Part number subdirectory of the Customer Directory. 5 subdirectorys of the Part Number Directory. 2 subsubdirectories of one of the 5 subdrectories. That is reference only.
Private Sub CreateFolderFromCellPath8_Click()
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String, s7 As String, s8 As String, s9 As String, s10 As String
var_path_masterfile_cust_name = Worksheets("RFQ").Range("AF48").Value
var_path_part_number = Worksheets("RFQ").Range("AF49").Value
var_path_Correspondence = Worksheets("RFQ").Range("AF50").Value
var_path_Customer_Service = Worksheets("RFQ").Range("AF51").Value
var_path_Engineering = Worksheets("RFQ").Range("AF52").Value
var_path_Purchasing = Worksheets("RFQ").Range("AF53").Value
var_path_Quality_Assurance = Worksheets("RFQ").Range("AF54").Value
var_path_Sales = Worksheets("RFQ").Range("AF55").Value....................
The variables are grabbing the path directly out of cells in Excel that are concantenated strings. This works great if the customer folder (s1) is not created. I would have thought that the way that I had the If/Then setup, that it would run smoothly, but if the customer folder is already created, the Debug highlights the very first MkDir s1
View 4 Replies
View Related
Jul 11, 2014
I have a file which is opened every so often. This file when opened it presents a userform in which you browse two files which then get loaded into the document and some charts/pivots are updated with the information from the loaded files.
When you click on the browse button it opens the browser dialog box in the libraries path. I am wondering if it's possible to have the dialog box open in the last directory path used.
For example:
A week ago I opened the file from the path C:UsersIntiDocumentsProjectsTea Project
What I would like to happen is that when I open the file today and I click the "Browse" button (which opens the dialog box to find files in your computer) for the dialog box to open automatically in the path C:UsersIntiDocumentsProjectsTea Project
Then if tomorrow the file is opened from the path C:UsersIntiPicturesProjectsTea2Project
Then a week from now when you browse for the file the dialog box opens automatically in the C:UsersIntiPicturesProjectsTea2Project path
This is what I have right now and it always opens the dialog box on C:UsersIntiDocuments
[Code] .....
View 1 Replies
View Related
Mar 15, 2014
I need a macro to loop through a dir and the sub directories to find xlsm files, when it finds one open, go to the sheet named data, look at c1 and if the value is between 12.1 and 13.4 then i need it to write the file path, filename to a1 in my excell sheet and then write the value from c1 in the original file to A2 of line in my excel sheet.
I have hobbled pieces of code together without any good results.
View 2 Replies
View Related
May 9, 2007
i am wanting to automaticaly remove a file from a directory when this directory is populated and move to another directory that is secure, how would i do this? just say directory 1(where it will be removed from) is s:/ddc/reports
moved too-- s:/ddc/test
test folder will be secure
View 5 Replies
View Related
Sep 25, 2008
What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.
View 5 Replies
View Related
Feb 8, 2014
I have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
View 6 Replies
View Related
Oct 19, 2009
Is there a way to generically reference a directory when creating a hyperlink? By this I mean, I have an XLS that documents the flow of a process. As an outcome of this process, text files are generated.
This process may be run multiple times. I would like to be able to create multiple directories: Run1, Run2, Run3, ...
I would like to put a copy of the XLS in each directory. I would like to put the output files from each run into their respective directory.
I would like to put hyperlinks in the XLS that point to the flat files. However, when I add a hyperlink it contains the full directory path. Something like C:MyDocumentsRun1output1.txt.
So, if I copy the XLS into directory Run2, the hyperlink is still going to point to a data file in directory Run1.
How can I generically default to look in the 'current' directory for a file? Or is there a different/better way around this? ............
View 7 Replies
View Related
Jun 11, 2008
I have a little boo-boo I need to fix. I have a customer that has about 100 workbooks. I needed to add some functions to them so I made up a new sheet of forms and buttons as well as a series of new macros to bring into these workbooks. I knew I couldn't import the macros, so I added the module containing those by hand to all workbooks. I then imported a master copy of the new sheet from a master file i use to hold my working macros. The problem is the buttons on the new sheeets in each workbook now references the master workbook for the macro name, even though each workbook has the same macro of the same name in itself.
My question is, can I make a macro that will edit all the buttons on a specific named sheet (maintenance is the sheet name) in a workbook and take out the external reference.
For example, instead of the assigned macro pointing to c:master.xls!macro1 i just want it to call macro1. And yes, I can go into them and do it by hand. Do I want to manually edit 100 workbooks for a dozen buttons each? No. I want to automate it if I can. If anyone can help I would greatly appreciate it. It would save me a bunch of time.
View 9 Replies
View Related
Mar 4, 2013
is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?
I notice that the hyperlink is ....pdf ry.pdf
if I change the position of the file excel there is a problem!
I would like to save es. d:invoichepdf ry.pdf
View 1 Replies
View Related
Feb 11, 2014
I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.
My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.
I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.
View 1 Replies
View Related
Oct 3, 2012
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
View 2 Replies
View Related
Sep 5, 2013
The script collects all the files in directory and sub-directories and list them in ascending format, I want them to get in transpose format. like for example: Root folder has many sub directories and in them a sub directory XYZ has 5 excel files, it will get the sub directory name in Col A and transpose all .xls files.
Col A | Col B________|Col C_________ |Col D___________|Col E________|
XYZ__|C:/root/test.xls|C:/root/Sales.xls |C:/root/Report.xls |C:/root/sam.xls|
[URL]
View 8 Replies
View Related
Jun 10, 2009
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
View 3 Replies
View Related
Dec 3, 2006
what code can be used to be place in a Command Button on a worksheet that will use the value in a cell, change it to the directory where it have to be saved and save it to the value of another cell. Example:
On a worksheet called "Customer", at cel A1 will be the customer's name and at cell A2 will be the asset number. Now let's say a directory does not yet exist on the root for this customer, a new directory must be created in the root directory by using the name in A1, and the workbook must then be copied and saved under the value of A2.
View 6 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Mar 6, 2014
Using Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.
Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.
Example
Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)
Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1
Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?
View 2 Replies
View Related
Feb 13, 2014
I have date values in a range of cells, and have named the range "ChangeRange".
How would I:
1. loop through the range to store the current value of each cell in a scripting directory?
2. use vba to compare the values in that dictionary to the current value of the cell when it changes (NOTE: the change is by formula, not by manual insertion of a new value?
3. write that old cell value in the cell immediately to the right of the cell when it changes and update the dictionary value with the "new" old value?
4. do this for more than one range of dates on the same page?
View 2 Replies
View Related
Feb 15, 2010
I want to use the following kill command to delete all files in a folder:
Kill "C:dsmanagerest*.*"
However, I want the directory to be specified not within the code, but within a cell on a worksheet. Ie. Cell A1 would contain the directory C:dsmanager est*.*"
View 6 Replies
View Related
Jan 21, 2012
how, via VBA, to save a worksheet ("ForExport") from my workbook into a new file (type CSV). To provide additional difficulty (not by my choice), the users will all have their own directories on the network to save their files into. These directories will NOT be mapped drives (e.g., "C:") but rather will be UNC paths (\parentusername)
So, obviously the filename and location are both rather dynamic, depending who is using the excel template.
The workbook has three sheets:Base Form (sheet 1)ValidationResources (sheet 2, provides data for menus/validation)ForExport (sheet 3, contains data written to it from the Base Form)
The workbook has fields to capture UserName, FileName (without an extension), and FilePath.UserName = captured by VBA code from the users login informationFileName = concatenated field based on some other fieldsFilePath = concatenated field of a set parent path (\parent) plus the UserName (so, \parentusername)What I want to do is have a macro that takes the ForExport sheet, saves it as a CSV file (with the file name from the field holding that info) into the directory I've specified in the FilePath field.
Is what I'm trying to do even possible? I have a sample workbook I can provide if you need something to work with. Not sure if I can attach it here.
View 2 Replies
View Related
Aug 25, 2008
How can you add the date from a sheet say A1 which is a long date and make a directory which the date incorporated into the new directory name?
View 2 Replies
View Related
Dec 24, 2008
How do I setup MyPath to a cell reference? In this case I need to set MyPath to the cell value of I1
Code that I have that doesn't work...
Sub SaveToFolder()
'Save File to Path
MyPath = I1
ActiveWorkbook.SaveAs Filename:=MyPath & ActiveWorkbook.Name
Windows("PE Console.xls").Activate
End Sub
View 9 Replies
View Related
Feb 23, 2010
I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.
The macros do this:
Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.
However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)
- Invalid procedure call or element on fn = dir() at the bottom
- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.
View 6 Replies
View Related
Sep 17, 2009
I'm trying to copy a file from one directory to another directory. However, the source directory, I'd like to have it so it's created from values within specific cells on a Sheet named "PULL".
When trying the following, I get an error permission denied.
View 14 Replies
View Related
Dec 15, 2009
I am creating a training records matrix which will be used by 30 or so locations. each one may want to save in a different location. I would like to save the chosen location as an entry in a cell so this can be accessed by other macros.
View 9 Replies
View Related
