Change To Directory Path Located In Cell
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
ADVERTISEMENT
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
View Related
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
Jun 17, 2009
is there a VBA way to determine an unknown path in which a KNOWN WB is located !?
Assume a WB named: 1X1.xls Located at C:TEMP
[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:TEMP
The known open command for a known file AND Path is:
View 14 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 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 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 9, 2009
I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.
e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
View 13 Replies
View Related
Mar 16, 2007
I have a workbook that will save as if certain conditions are met. The workbook saveas filename is determined by user input into a textbox. I would like to define the path but allow the filename to be the value of the textbox. I used the ChDir function to do this but it is not working. Here's what I have:
Private Sub CrmFrm1_Click()
ChDir "S:AccountingProbationTest Files"
If Len(TextBox1) <> 12 Then
MsgBox "Incorrect Case File Number"
FrmSave.TextBox1.SetFocus
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:=FrmSave.TextBox1.Value
End If
Unload Me
End Sub
View 2 Replies
View Related
Oct 2, 2007
I have an Excel macro that pulls an external .txt file from a particular directory on a users computer. The directory is different for each user because of the way the computers are configured, see example below.
Bob's computer:
c:obwork
Jill's computer:
c:jillwork
There is a system variable called %username% on each computer that can be used for navigation in an OS enviroment. So if you were at the command prompt on user's computer and typed cd\%username%work you would be put in the desired directory. Is there a way to leverage this system level variable, %username%, in an Excel macro?
View 2 Replies
View Related
Nov 11, 2013
I have a 2 columns.
A: Current location Ex(C:UsersDesktoplabelsA02FAA.jpg)
B: Future location Ex(C:UsersDesktopoxes98300SA02FAA.jpg)
I want to move jpgs from location A to B
is there a way to execute this with a macro?
View 5 Replies
View Related
Jan 19, 2007
I have a macro that copies data from 1 sheet to another one.
However, in the destination sheet I have no way of know which is the 1st empty row that my data can be paste into.
View 9 Replies
View Related
Oct 17, 2012
I want to be able to link spinbuttons to the cell it is located in automatically, so I can move it around or copy and then paste into other cells. I do not want to use one button to increment a selected cell.
View 1 Replies
View Related
Mar 20, 2014
How do I select the empty cell (once located after using the below loop) so I can paste into it?
[Code] .......
View 10 Replies
View Related
Feb 7, 2008
I'm trying to calculate whether a time value located in a cell is between the times in two other cells. Here is what I tried:
=IF((AND($A62>=C$55,$A62
View 9 Replies
View Related
Oct 9, 2008
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with:
Sub importdata()
' importdata Macro
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:Documents and SettingsMy DocumentsTender Document(1).xls;DefaultDir=C:Documents a" _
[Code]....
View 9 Replies
View Related
Oct 9, 2008
I have a directory where our customer service people deposit forms each day. The form is always the same, with a number of columns that need to be copied into a master list. From there they are analyzed, sorted, etc, then exported, so the number of rows in that list varies from day to day as well. Is there some way to have VBA import each file sequentially from a directory if I do not have the exact file names?
I am using this import code from a macro to start with: .....
View 9 Replies
View Related
Apr 13, 2009
to fix a .xls file that wasn't created by me.
In order to do that I need to change the path of an External Data that is an access 2003 file (.mdb file).
The only way that I imagine it is possible is to select a cell that is an adress for the query result, than click on properties and change it selecting the new path of file (the path moves depending on the user of the .xls).
View 9 Replies
View Related
Dec 13, 2013
I have created a PPT which is linked with my excel data and charts. Now I have to copy it at different location (Both PPT and excel), But due to change in path; my PPT is not updating with latest data points and charts. I know, I can change link using edit link.
My PPT is having around 50+ link. some VBA code which can replace old path using the current path.
View 1 Replies
View Related
Mar 11, 2014
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
View 2 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