Displaying The Current Opened File's Directory Path?
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.
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.
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
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.
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.
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
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
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?
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.
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
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
Sub filezillasettings() Dim sourceloc As String sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
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
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be; S:BryanFor KenGulf ConstructionST0609014-t.xls (the file name length may vary) What code can I use to consistently trim it back to; S:BryanFor KenGulf Construction as the "DestFldr" variable?
VB: Sub openfiles()Dim Path As String Dim ExcelFile As String ' Path = GetFolder("C:UsersKinteshDesktop") Path = "C:UsersKinteshDesktopVBA programmingMaps" ExcelFile = Dir(Path & "*.xls")
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.
This Macro (Located within "SWMS Auto-Fill.xlsm) scans the folder its in and transfers information into select cells within each .xlsm file it finds. However I need to move the SWMS Auto-Fill.xlsm file to the parent directory.
So I need it to scan: Workbook DirectorySection4
Code is below:
VB: Sub Autofill_SWMSs() Dim MyPath As String Dim MyFile As String Dim Wkb As Workbook Dim Cnt As Long
I am using this code to import excel files into one workbook..I have the path hard coded. Is there a way to get this automatically so if another person is running on there machine it will work..
can you define the path in like cell A1 and have it pull from there?
Sub GetSheets() Path = "C:UserswharnedDownloads" Filename = Dir(Path & "*.xls") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
i am trying to do, i have a spreadsheet with 100+ tabs all with the day of the year. Jan1,Jan2,Jan3...Feb23,Feb24,Feb25... etc etc. I want to pull all of the same values for each of these sheets on a single tab (using vlookups, hlookups and reformatting the information for a macro to input all of this data into a database)
My question is, how can I put a entry form, lets call it a1 and fill in the tab i want the contents of the page to update with? For example, if i input "Jan18" in a1, all formulas on the page look for their source information on A1.
Iâ€™m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on â€¦
2.Iâ€™ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath 'controle = Dir(pad$ + "FactuurNummer7.txt") 'If controle = "" Then GoTo EerstAanmaken 'Open pad$ + "Factuurnummer7.txt" For Input As #10 'Input #10, Nummer1 'Close #10......................