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.

View 5 Replies


ADVERTISEMENT

Load Dialog Box In Last Directory Path Opened?

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

Automatically Open File In Specific File Path When Another Sheet Is Opened

Mar 20, 2014

I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls

So opening x.xls will automatically open y.xls

I've tried this in the workbook code area but it doesn't do anything.

[Code] .....

View 1 Replies View Related

Loop Through Directory And Return Path File And A Value

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

Macro To Save As File To Current Daily Folder, Path Changes

Aug 6, 2008

I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as c:

eports8052008, so tomorrow 's folder would be c:eports8062008.

All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), mmddyyyy), get error message.

View 9 Replies View Related

Prompt User For Directory And Filename; Then Save File In Directory With File Name

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

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 View Related

Dialog Box For Selecting Directory/path

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

Place Username In Directory Path

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

Create Directory Path From Cells

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

Conditional Directory Path From Cells

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

Using Excel Cell As Pointer In Directory Path?

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

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 View Related

Automatically Remove Folder From Directory Path

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

Save Hyperlink Path For File With Absolute Path And Non-Relative?

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

Trim Full File Name & Path To File Path Only

Sep 27, 2006

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?

View 4 Replies View Related

Macro To Separate File Name And Path From Complete Path

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

Using Current Directory In Save Name?

May 15, 2013

I am trying to use VB to save a workbook into the same directory but with a file name that references the folder it is stored in...if that makes sense!

Here is where I have got to so far but fails on save,

Sub Rename()
Application.DisplayAlerts = False
mdy = Month(Now()) & "." & Day(Now()) & "." & Year(Now())
fold = ThisWorkbook.Path
FName = fold & "-" & mdy & "-(New).xls"
SaveName = fold & "" & FName
ActiveWorkbook.SaveAs (SaveName)
Application.DisplayAlerts = True
End Sub

View 3 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

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

Get A Macro To Look In A Subfolder Relative To Its Current Directory

May 28, 2014

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

[Code]....

View 1 Replies View Related

Get Current User Path In VBA

Jan 31, 2014

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..

OR

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

View 3 Replies View Related

Macro That Saves Current Open Workbook To Directory Specified?

Jan 10, 2012

I would like a macro that saves the current open workbook to a directory specified in a cell on the open work sheet.

View 4 Replies View Related

User Input For Current Path?

Oct 31, 2008

I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.

I did a number of searches and I can't find exactly what I'm looking for.

I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.

View 4 Replies View Related

Wildcard In Path- Relative To Current Sheet

Dec 5, 2008

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.

View 2 Replies View Related

Excel VBA Current Folder Without Complete Path

May 15, 2009

What's a proper way - in Excel VBA - to get the current folder *without* the preceding folders/path?

For example from folder "C:oracleora81sqlplusdemo" I'd like to retrieve "demo".

Currently I have:

Code:
DirNames = Split(ThisWorkbook.Path, "")
CurrentFolder = = DirNames(UBound(DirNames))

It works, but I suspect something exists specifically for this one.

View 7 Replies View Related

Excel 2010 :: Using Current Month / Date And Time In A Folder Path In VBA Code

Feb 4, 2014

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?

View 4 Replies View Related

Import CSV File Using Cell As File Name & Active Workbook Path

Nov 3, 2009

I am trying to import some csv files so I can combine them, but am having probs with the filename and location.

Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With

With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................

View 5 Replies View Related

Save Excel File As Another File Using Current Date As Part Of File Name

Oct 10, 2011

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.

View 1 Replies View Related

Copy File Names From A Directory Into An File

Mar 11, 2007

Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?

View 9 Replies View Related

Save File As Text Using Cells For Path & File Name

Dec 20, 2009

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......................

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved