Create Directory Tree
Dec 9, 2009
I've been trying my best to create a directory structure using VBA and Excel, and have run out of patience on how best to do this. My goal is to create a directory tree containing 2 levels. The 1st level (Main Folders) will be named from data in Column A of an Excel Spreadsheet, the 2nd level (Subfolders) will be created from data in Columns B, C and D of the spreadsheet. I've been using the following code with success, but don't know how to create subdirectories:
Sub StartHere()
Dim rCell As Range, rRng As Range
Set rRng = Sheet1.Range("A1:A20")
For Each rCell In rRng.Cells
CreateFolders rCell.Value, "C: est"
Next rCell
End Sub
Sub CreateFolders(sSubFolder As String, ByVal sBaseFolder As String)
Dim sTemp As String
'Make sure the base folder is ready to have a sub folder
'tacked on to the end
If Right(sBaseFolder, 1) <> "" Then
sBaseFolder = sBaseFolder & ""
End If............
View 5 Replies
ADVERTISEMENT
Dec 5, 2006
I want to get a tree view created with the following details
NCR SUMMARY
KISHOR ASRANI
JAYDEEP SINGH
HITESH MADAN
LALIT MONGIA
ANISH BHAN
SUMIT WADHWA
VIBHOR DEWAN
SANJAY DHAR
KIRTIKAR OJHA
PANKAJ KHUSHANI
GUNJAN SACHDEV
SHUBENDU GHOSH
RON SUMMARY
SANGEETA BHATNAGAR
ATUL PAL
HARSH MALHOTRA
SALIL KHANNA
EDIT: FYI: thomach tried adding code and then quote tags to see if the indenting showed. Neither worked.
View 5 Replies
View Related
Jun 19, 2014
I was sent this file and I want to replicate the bill of material tree structure on the left. I'm not sure how it is done.
Image: BOM_example.png
View 1 Replies
View Related
Oct 20, 2007
Is it possible to create a VBA script that will list the filenames of files in a set directory? I have about 100 directories, each containing 1000 files and they need to go into an excel spreadsheet (each file on a new row)so they can be audited... ;(
View 3 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
Mar 20, 2014
I have Workbook named"Test_Template" in below specified path this is not the active workbook.
It is saved in strFName = ThisWorkbook.Path & "Backup" & "Test_Template.xlsx"
I need to create a copy of the same workbook named as "Copy_Test_Template.xlsx" in the same folder(Backup), same specified path on button click.
[Code] .....
But this creates a "Copy_Test_Template.xlsx outside the Backup folder, i want it in the back up folder.
View 2 Replies
View Related
Nov 11, 2003
how can I creat a directory and then save the same file into that directory. At the moment Im using -
ThisFile = Range("T3").Value
ActiveWorkbook.SaveAs FileName:=ThisFile
...which works great, but before I do this, I want to create a folder with the name the name/value in T4, then ofcourse save the file into that folder ....
View 9 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
Sep 8, 2006
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
View 5 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
May 6, 2009
How do I make the following conversion :
(Column values are separated by =)
I'd appreciate if someone can help create a Macro for this. This might have a long list and a longer tree as well.
Input
========================================
column1 column2
---------------------------------------
1 = top
1-2001 = middle
1-2001-2002 = bottom
1-3001 = alpha
1-3001-3002 = beta
1-3001-3002-3003= gamma
output
========================================
column1 column2
---------------------------------------
1 = top
1-2001 = top-middle
1-2001-2002 = top-middle-bottom
1-3001 = top-alpha
1-3001-3002 = top-alpha-beta
1-3001-3002-3003= top-alpha-beta-gamma
View 9 Replies
View Related
Jan 26, 2007
how to create a collapsable tree in Excel using VBA.
Note: There is restriction that I cannot use ActiveX
A small example will be very useful.
View 3 Replies
View Related
Nov 19, 2006
My Question: how would i be able to render a dynamic drop down tree in the following format (see attachment): look at the left Rich Graphics folder example:
http://javascript.cooldev.com/script...mos/superdemo/
View 3 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
Mar 6, 2014
I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?
View 1 Replies
View Related
Jan 8, 2007
I want to create sections in excel exactely like below. I need to show user how many types systems are available and in each type how many config systems available.
View 4 Replies
View Related
Aug 31, 2012
I am trying to build a cup tree that automaticly move players over to the next round if they are first or second in
their group of 4. What happens when first person have a result of lets say 33 and 2nd and 3d person have a result of 35 each and the 4th person 37? First person is easy but then there are 2 persons with the sam result and they play sudden death and we put in a 1 in the sd column for the winner and a 2 for the loser of the sudden death.
How do I make my formula figure that if they had the same result go look at the sd column?
Another thing is that of the 2 players advancing they have to be placed in the same order in next round, how can I achieve that?
If you look in column I row 19-22 there are players in the wrong order because the player with the better result goes in first. It should be this order: 9 11 15 16 instead. (if you are ahead in the previous round you will be ahead in the next round)
Another problem I noticed now is that when 2 players has the same lowest score the first player takes both spots as it is now and that doesn't work in real. You see what I mean if you look at player 1 in round 1 and 2 (R1 & R2), in this case it should have been players 1 2 6 and 7 moving on to round 2 and not 1 1 6 7 as it is at the moment.
(Rank is their rank from the qulification rounds where the first 4 is seeded and the rest is drawn into 1 group a time)
Klass B
*
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
2
Rank
Name
R1
sd
Pl.
[Code] .......
Spreadsheet Formulas
Cell
Formula
I7
=IF('Klass B Calculations'!F4=1;'Klass B Calculations'!C4;IF('Klass B Calculations'!F5=1;'Klass B Calculations'!C5;
IF('Klass B Calculations'!F6=1;'Klass B Calculations'!C6;IF('Klass B Calculations'!F7=1;'Klass B Calculations'!C7;""))))
[Code] ..........
View 4 Replies
View Related
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
Oct 7, 2006
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................
View 9 Replies
View Related
Oct 1, 2008
I encounter the following problem which has been bugging me for weeks.
I am doing up an engineering spreadsheet which calculate the electrical cable size from node A to node B.
Col A and B goes like this:
From|To
Transformer|MSB_A
MSB_A|SSB_A1
Transformer|MSB_B
MSB_B|SSB_B1
SSB_A1|DB_A1
SSB_B1|DB_B1
So Transformer is the parent of MSB_A and MSB_A is in turn the parent of SSB_A1 and so on.
What I would like to achieve is to put these data into the following configuration:
Transformer----->MSB_A
............................|-> SSB_A1
.....................................|----->DB_A1
Transformer----->MSB_B
...........................|-> SSB_B1
....................................|-----.>DB_B1
NB: Please ignore the "dots" above.
This will give the user an overview of the parents and node relationship.
I can't seem to find a solution how to achieve this.
View 9 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
Jul 28, 2009
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
View 5 Replies
View Related
Apr 22, 2009
The command Application.GetOpenFilename will open a explorer to be able to choose a file, what is the command to open a explorer but only to choose a directory?. Is there such a command?.
View 7 Replies
View Related
Feb 2, 2010
The Kill function works OK but the ThisWorkbook.SaveAs part does not work for some reason.
Please note that there are 2-spaces between the words: P3 CATS
View 12 Replies
View Related
Dec 20, 2011
For a macro i'm writing I need to search for a directory.
Users enter a projectnumber, this is being used for a search string.
But how can I search for folders/directories in Windows?
I've got the startfolder: 'C:Projects'
Then the project which the user is searching for can be in several subdirectories.
View 4 Replies
View Related
May 11, 2006
1. Is it possible to save additional worksheets (right now it is only copying one worksheet)?
2. How can I hard-code the directory path so the file goes to that one locatin (i.e. the "master directory")?
Also, is it possible to save userforms out of this workbook (i.e. some of the userforms contain information that is useful...it would be great to have them in the "output" file that gets saved to the "master directory"). Thanks!
Private Sub SAPfile_Click()
myfile = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
Worksheets("Output-SAP Plan").Copy
ActiveWorkbook.SaveAs myfile
ActiveWorkbook.Close
MsgBox "SAP file created."
End Sub
View 9 Replies
View Related
Jul 6, 2006
I've been looking for code to print Excel files from a directory with multiple worksheets. I found something close on this forum, but it prints 2 copies which is fine for pdf, but not for hard copy. I would like just 1 copy.
Sub PrintPDF_PLOTDIR()
Dim PATH As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application. ScreenUpdating = False
PATH = "C:DATA\_PLOTDIR"
FileName = Dir(PATH & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=PATH & "" & FileName)
Application.ActivePrinter = "Adobe PDF on Ne00:"
For Each WS In Wkb.Worksheets
ActiveWorkbook.printout Copies:=1, Collate:=True
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Nov 5, 2008
Imagine this:
* Main Category #1
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
* Main Category #2
Sub Title #1: "Value"
Sub Title #2: "Value"
Sub Title #3: "Value"
Sub Title #4: "Value"
Sub Title #5: "Value"
Sub Title #6: "Value"
Sub Title #7: "Value"
Sub Title #8: "Value"
Sub Title #9: "Value".................
Main categories and subtitles are in the same column. "Value"s are in the column to the right.
I want to make a list in the columns to the right. This list will only show something if the "value" is not zero. It will show in the first column right from the original list, the main category to which the subtitle belongs to and the subtitle. Something like this (imagine subtitle #2 from cat #1 and #2 and #3 from cat #2 are not zero):
View 4 Replies
View Related
Apr 29, 2014
I want to have a excel file with VBA code and it should operate like the following:
It should check the subdirectory's in a certain directory and put this on a excel sheet.
Every time when you start the sheet it should compare the sheet with the sub directory's and make al the changes in the list.
In the end the excel sheet should be a representation of the directory.
I can't get it to work when something is changed or a directory is added or deleted.
[Code] ....
View 1 Replies
View Related
Aug 6, 2005
An already existing file will be changed by my macro and at the end I want the user to save the file with a different name in a different directory with the option to change the filename or directory. Therefore I use the excel-standard-dialog "save as". But I want the dialog to start in another path as the path the original-file is stored in. But also I use the ChDrive and ChDir Parameters the dialog even starts in the original-file-path. I think this is standard for the dialog and I should maybe use a parameter in the show-arguement? The code is below.
Option Explicit
Sub SaveMyFile()
Dim xFileName, xAnswer
ChDrive "J"
ChDir "J:myfolder"
xFileName = "testme.xls"
xAnswer = Application.Dialogs(xlDialogSaveAs).Show(xFileName)
End Sub
View 5 Replies
View Related