Relative Path With Different Directories - Especially Up One Level
Jan 4, 2011
Is there any way to reference a directory that is up one level from the ActiveWorkbook.Path?
I am trying to accomplish three goals:
1. Maintain a Master Database in a root directory and pull data from a subdirectory - EASY:
VB : FilePath = ActiveWorkbook.Path & "sub-DirectoryName"
2. Maintain a Minor Database located in a sub-Directory, Named by Month, and pull data from the Master Database located in the Root Directory (up one level). Which I cannot find the right syntax for - ".." does not work.
3. Maintain a Minor Database in a sub-Directory, Named by Month, and pull data from a different Minor Database in a Different sub-Directory (e.g. up one level to root directory, then down one level to "January"). Which I also cannot find the right syntax for.
The Databases are going to be moved around A LOT, so I have to use relative paths.
View 4 Replies
ADVERTISEMENT
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 28, 2014
I have a WinForm, which I'm calling via VBA's Shell command:
Code:
Sub Button1_Click()
Shell "C:TemplateFuncitons.exe " + ActiveWorkbook.FullName, 1
End Sub
I made this module an Excel Add-in and added it as a button in the quick access toolbar. Now, the idea is for the WinForm.exe and the Excel.xla to always be in the same directory (go hand in hand). I want to make a relative path for the .exe which will be the path to the folder where the .xla is + the string "TemplateFunctions.exe" in the end.
View 5 Replies
View Related
Dec 3, 2008
How can I run a DOS .bat (batch) file from within a Macro? This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.
View 2 Replies
View Related
Nov 23, 2006
I have a macro in excel that saves to an external text file, but the excel workbook (and the text file) will be copied to different folders so i need it to alway read the file from the location the worksheet was opened from. I hope that makes sense
Ive tried using ActiveWorkBook.path but that doesnt work, maybe im just using it the wrong way
View 5 Replies
View Related
Feb 16, 2008
I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.
But...
I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.
Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.
So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?
But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.
View 4 Replies
View Related
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
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
Feb 5, 2010
I need to use VBA to test if a certain directory exists and if not ten create it.
I do this with:
[CODE]Sub MakeDir()
Dim DirName As String
Dim FileName As String
Dim fnameshort As String
FileName = "C:TestA1ATest.xls"
fnameshort = Right(FileName, Len(FileName) - InStrRev(FileName, ""))
DirName = Left(FileName, Len(FileName) - (Len(FileName) - InStrRev(FileName, "")))
If Dir(DirName) = "" Then
MsgBox "Does not exist"
'Dir to be created using MkDIr
End if
End Sub/CODE]
My problem is that I need the script to loop back until it finds a folder that exists, and then make the respective subfolders.
Example:
C:TestA1A should be created, but currently only C:TestA exists.
VBA should then test for the full path, and when it finds out, that it does not exist, it should strip off one folder, test for that and so forth.
When it finds an existing folder, it should then make the respective subfolder, and then the subfolder to that...
View 9 Replies
View Related
Mar 2, 2009
In my Excel file, I have three columns: A, B and C. Column A holds the folder names. column B holds the files hash names. And in column C, i keep the files real names, i.e:
A1.value = Contract folder
B1.value = 23ffryu567894lkgj090
C1.value = picture1.jpeg
On my hard drive, I have all the folders (column A data) already created inside a folder called directories: E:directories... There is another directory on my drive in which I keep all the hash files: E:infiles
And all I would like to do now, is to write a piece of code to read all three columns row by row and based on the data in that row, moves the hashed file into their proper directories and rename them to their real name. In the example above, the program should read the data found in the first row: A1, B1 and C1 and move the hash file named 23ffryu567894lkgj090 to E:directoriesContract folder and rename the hash file to picture1.jpeg
View 14 Replies
View Related
Nov 29, 2012
I am trying to automate some of the work I used to do as I have moved to another position, specifically trying to automate file management. I am using this code to create new files for each client
Code:
Public Sub CheckDir(fold1 As String)
If Dir(fold1, vbDirectory) = "" Then
MkDir fold1
Else
End If
End Sub
which works great, but you still have to do one client at a time. Basically I'm using this code to create new client files, but each year we will need to add a directory and sub directories for every client in a certain directory. What I would like to do is code a macro that would add say a "2013" folder with all appropriate sub directories to every client folder within the folder that contains all of the client folders The folder hierarchy should look like this
Code:
'F:Clients
'F:ClientsClientName
'F:ClientsClientName2013
'F:ClientsClientName2013RandomOtherFolders
I assume it would use a For/Each, but I'm not sure how to go about it.
View 3 Replies
View Related
Nov 8, 2007
I have a folder structure like ex this c:my folders. Under this folder i have several folders and subfolders. I would like to use a macro to run through all this folders an sub folders and change the name of the first letters of all folders.
Ex
LOP-100-APPL
LOP-200-ORG
I would like to change LOP to PLP instead. I'm lazy and looking for a method for not doing this job manually. This should be done from the excel workbook.
View 3 Replies
View Related
Sep 19, 2008
I am trying to access and copy some data from the excel file which is located in different folder each time I run the new macro. The file got the same name but only the folder name is different each time.
e.g File is called "testfile", but I have a list of subfolders something like that
date_time (folder name)
15-02-2008_2122 (folder name)
15-03-2008_2189 (folder name)
Each of the above subfolders contain "testfile" with new data and they all are located in the same drive and same mainfolder. Note my Macro file is located in a different drive.
I want when I run the macro all I have to do is to enter Date and macro will automatically pick up the right folder and the right file ("testfile") inside that folder and do all the required task.
View 9 Replies
View Related
Aug 22, 2006
I would like to create a list of folder/directories in the current directory. The only code I've been able to find lists all files in the current directory (not folders).
View 3 Replies
View Related
Feb 13, 2010
search an entire drive from the root directory and down into all subfolders and copy all files matching various file types below to another drive into organized folders named for the file type. (for an example any .xls files in F: copy to G:xls .xls files) and also create an index in one excel file showing all the files in alphabetic order with its original path.
bmp
txt
xls
View 14 Replies
View Related
Oct 7, 2007
Create sub directories from Cell Values
I Need a VB macro to Create sub directories unless sub directory already exist. My file will be Save 5 Level deep. Each Sub Directory will be cell value in a Worksheet .
Example C:CategoryYearSeriesMonthBimonthlyFile Name.xls
I will be using this in over 100 different files so I want to be Generic as possible
View 6 Replies
View Related
Jan 18, 2008
I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the
If valFilePath = True Then
MsgBox (msg)
Else
'Search for directories within source directory
strDir = Dir(ebsSource & "", vbDirectory)
'If a file has been found
Do While strDir <> ""
If strDir <> "." And strDir <> ".." Then
strFile = Dir(ebsSource & "" & strDir & "" & "*.xls")
Do While strFile <> ""
'Copy .xls files and paste in destination
copySource = ebsSource & "" & strDir & "" & strFile
copyDestination = ebsDestination & "" & strDir & "" & strFile
FileCopy copySource, copyDestination
strFile = Dir
Loop
End If
'Crashes here after looping through the first iteration of Excel files
strDir = Dir
Loop
View 2 Replies
View Related
Mar 20, 2008
I found this nifty program on[url]but there is a problem with it and it's down to the types of files I'm trying to list on my PC.
The excel code below lets you select a starting directory and it will then produce a list of files in a sheet. However when it comes across an internet shortcut file it gets rather confused and won't display the name of the shortcut but what the shortcut stands for. This causes an issue when I try and get some details about the file (eg date, size etc.) as a 'permission denied' error can then occur if it links to a file that is currently in use. For some reason I can't attach an example of a shortcut / internet shortcut so please feel free to make one at your end. eg. I have a shortcut called 'ImageJ' which links to a web address [url]. The code below then reports this address instead of the file name 'ImageJ'. Can any one suggest how to amend this coding to handle this issue?
Option Explicit
'Requires a reference to:
' Microsoft Shell Controls and Automation (shell32.dll)
'Uses techniques found here:
[url]
Public objShell As IShellDispatch4
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
View 5 Replies
View Related
Feb 26, 2009
I have a CSV file and would like to create directories on my hard drive and the names of those directories will need to be pulled out from this csv file: [B1:B86].
For example I have the following data in my csv file:
B1: folder1
B2: folder2
.
.
.
And would like to create folders on my hard drive so that folder names will be: folder1, folder2, ...
View 9 Replies
View Related
Apr 5, 2007
I have a fluid document that I have to save for historical reasons in 2 separate directories. My save Macro works I was just wondering if anyone see's a better way of accomplishing saving to two separate directories then closing the document. I used the record function to gernerate most of the code.
Sub SAVEANDEXITTHESLATE()
Dim date1
Dim date2
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application.ScreenUpdating = False
date1 = Now()
date2 = Format(date1, "mmm d yyyy hh mm")
ActiveWorkbook.SAVE
Application.DisplayAlerts = False
ChDir "c:Documents and SettingsmeDesktop"
ActiveWorkbook.SaveAs Filename:= _...................
View 5 Replies
View Related
Jan 28, 2010
I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.
I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.
Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.
Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!.......
View 14 Replies
View Related
Aug 17, 2012
Is there an easy way to populate a combobox with a list of directories on the hard drive?
I want to create a way for a user to specify a file without having to type in the full address by hand.
I would like to start in the c:/ root and list the directories in one combobox and the excel files in another combobox. Then if they select say "desktop" it displayes the directories in "c:/desktop" and the excel files contained there in. Then if they click test it does directories in "c:/desktop/test" and so on.
View 2 Replies
View Related
Feb 22, 2014
I have a master workbook that has been set up to mirror the structure of a single worksheet in various other workbooks saved in different directory locations. I need some VBA code to retreive specific data from a specified worksheet in multiple workbooks which are saved in different directories and then copy the data to the master workbook, listing each data set one after another. I do not want to open any of the source workbooks to acheive this.
I attach two example workbooks to better explain:
The code has to look in various sub directories to find the relevant workbooks, (Source1) then find the specified worksheet, (Stock) and copy only rows that have data from column B to O. The data needs to be copied to the master workbook, (master) from all the source workbooks as a list with no space.
View 4 Replies
View Related
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
May 15, 2008
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
View 9 Replies
View Related
Apr 19, 2012
I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in
C: est
C: est3
It does not go deeper. i.e it wont look in folder that is for example
C: estanotherlevel
C: est3anotherlevelmylevel3
How to modify my function so that it goes to the deepest folder. is there a way to do it?
Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String
[code].....
View 2 Replies
View Related
Jul 24, 2012
How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .
I know the way to get the cell level name goes something like this:
MsgBox Sheet1.Range("A1").Name.Name
But i don't know the correct syntax for sheet level.
View 4 Replies
View Related
Feb 10, 2009
I have this memory game I want to set out on the computer. In table A, Row 2 (B2:E2) I (Moti) start off being the first player, where I place a series of different items. (In Example is given letters but can be kitchen article food and so…. Separated by coma) these Items are shown to player Jack, for a few seconds, so he can memorize them and Answer in (B3:E3) ....
View 9 Replies
View Related
Sep 22, 2009
I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.
View 2 Replies
View Related
