Multi-Level Folders In VBA?
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
ADVERTISEMENT
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
Oct 24, 2007
I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...
=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)
The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......
View 3 Replies
View Related
Nov 19, 2009
Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.
View 4 Replies
View Related
Feb 9, 2010
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
View 14 Replies
View Related
Mar 13, 2014
So our company gets Material Statements which basically state what chemicals and how much of said chemicals go into producing X item.
I need to figure out the percentage of MASS for each chemical.
So data might look like this:
Total Weight of product: 500 grams or mg, etc.
Sub-Item - Contact - 450 g
Material - Copper - 425 g
Substance - Nickel - .05 g
Substance - Copper - 422 g
Substance - Lead - .02 g
Material - PVC Insul - 5 g
Substance - Polysomethings - .5 g
Substance - Red Dye - .4 g
Substance - Plastic - 4.1 g
So basically the sub-item line is 450/500
first material line is 425/450
substance is .05/425
the substance lines use vlookup to find the material line to divide by. formula i'm using is below:
=IF(B2="Sub-Item",I2/$M$2,IF(B2="Material",I2/VLOOKUP("Sub-Item",$B$2:I2,8,FALSE),IF(B2="Substance",I2/VLOOKUP("Material",$B$2:I2,8,FALSE),"n/A")))
B2 is either sub-item/material/substance
Col I is the mass of that line
M2 is the total weight of the item
Formula works until you get to the Polysomething lines in the above example data. instead of taking the material weight from PVC Insul, it takes it from Copper.
View 1 Replies
View Related
Feb 25, 2010
I need to set up an excel sheet with dependent data validation that is multiple levels deep.
ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.
Is this possible?
Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.
View 9 Replies
View Related
Dec 23, 2008
I am looking for some assistance to make pivot tables with multiple data values against a control item. A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.
View 2 Replies
View Related
Jul 24, 2009
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
View 9 Replies
View Related
Feb 16, 2012
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies
View Related
Jun 12, 2014
I need to back up files, which may be excel or MSword, by copying them from different directories, say C: to the backup directory, say X:. any vba to backup the files? I expect the vba can copy all file listed in column A.
E.g. C: est1File1.xls or C: est2File2.doc, and then pasted to the directory in column B. e.g. X:BackupFolder1 or X:BackupFolder2.
View 1 Replies
View Related
Dec 20, 2011
I have folders with different assigned folder icons based on the content in the folders.
I would like to arrange these folders in the window based on the assigned folder icons and then by alphabetical order.
How it is to be done. Can we make use of vba having the folders path list in one column of excel sheet?
View 9 Replies
View Related
Jul 8, 2013
macro and I think this has me on my way; however, I do not understand the code and I am having difficulty modifying it. It returns the path in cell $A1 of my workbook, but I get reference errors in $B1:$L1.
I would like for the code to start entering the values in row 2 of my current worksheet. Row 1 I want to make a header row. The first column I like having the file path. Can the path be changed to a hyperlink? I would then like for it to return the values in $AP1:$BC1 to $B1:$O1 of my current workbook.
View 4 Replies
View Related
May 21, 2014
I am attempting to create a macro that will create a new folder on the network drive Arie:quotes that will create the new folder and save the the file in the new folder. I need the name of the file and folder to be set to the master (worksheet) cell "B3". and then if possible have it be conditional that if cell "B40" on the same master (worksheet) has a value to have the value added at the end of the filename via hyphen. This workbook has numerous worksheets in it approx 15 worksheets. I am not sure if this is relevant.
As an brief example.
Cell "B3" on the master(worksheet) contains a job name I-40 bridge
So initially I would like the Folder name and file name to be I-40 Bridge
However once the Cell "B40" has been filled in with the value 54213 then I would like the file to renamed to I-40 Bridge-54213 and saved within the same folder.
I am currently working with Excel 2007 Pro edition on windows 8. I
I have found several other macro's that work with an auto save but i am unsure of how to change the directory to the network drive and show the correct file paths. This is the example of the macro I am currently working with.
Public Sub SaveAsA1()
ThisFile = Range("B3").ValueMkDir "C:NewFolder"ChDir "C:NewFolder"ActiveWorkbook.SaveAs Filename:=ThisFileEnd Sub
View 4 Replies
View Related
Aug 16, 2009
I have managed to get this far - I have put together a macro (from different threads on this site) that opens closed workbooks, copies data in one of the sheets (same sheet in each of 28 books), and pastes the data it into a master book sheet, each paste starting below the last. So that bit is working. The first bit of help I need is a line of code that will make the macro loop through a number of sub folders in a main folder. My code at the moment works as long as I specify a path that ends with the name of one subfolder, and it only loops through this subfolder. I would like the path to end at the folder that holds all the subfolders ('Workbooks' in the path below), and then add some code that tells it to apply the macro to all subfolders in this folder, so it loops through them all.
The second issue is that after the macro goes to the closed book(s), copies the data in there and pastes it into the master sheet (into columns E:FG), I then need it to go back to the workbook it just copied from, go the same sheet, to three specific cells on that sheet (FH1:FH3), copy the content, go back to the master sheet, and now repeatedly paste the content (values only and transposed) of these three cells into three cells (in columns B:D, with row number being dependent on what rows the first lot of data was copied into) next to every row it just previously pasted in for me. When it loops to the next workbook, it needs to do the same, and the three cells will have different content than the ones in the previous workbook paste.
I dont know how to define the range it needs to paste into the second time. I tried using the definition I used for the first paste (MCDrow), to tell it that it is the same rows, just different columns, but this is not working.
Here is what I have so far, which does the first part of what I need, except for needing a way to have it loop through all subfolder in the 'Workbooks' folder (at the moment it lists Barwon South West as a subfolder in that path, but I actually have multiple subfolders, not all called Barwon South (all different names) that it needs to loop through and do both the first and the second paste for. I have taken out the code I was trying to use to do the second paste, as this was not working and the code is pretty messy as it is (I sort of bumble along, being so new, and I know the code is not very clean or efficient!).
Can someone help me put in the few lines I need to loop through all my subfolders (if you give me an example I can probably extrapolate), but to get you started, three of the subfolders are Barwon South West, Eastern Region and Gippsland. And can someone help me put in the code that will do the second paste for each workbook?
Sub Click2()
Application. ScreenUpdating = True
Dim MCDrow As Long
'Dim SubFolders As String
MCDrow = ThisWorkbook.Sheets("Client Data").Range("A65536").End(xlUp).Row
Fpath = "Q:Clinical ServicesCS Statewide DatabaseWorkbooksBarwon South West" ' change to your directory
'SubFolders = True
Fname = Dir(Fpath & "*.xls")
Do While Fname <> ""
ThisWorkbook.Sheets("Client Data").Unprotect
Workbooks.Open Fpath & Fname
Worksheets("Client Data").Activate
Worksheets("Client Data").Unprotect
View 6 Replies
View Related
Mar 31, 2014
I want to open C:Projects
and for each Project in Projects, create a new folder with the actual month (e.g. "03" or "12")
Im running the Makro from a file where I'd like to put the month in a cell -> so the code refers to cell("A1") wich has value 01 and creates a folder "01" in every projectfolder in C:Projects
View 3 Replies
View Related
Jun 30, 2014
I have a listbox with 8 columns. Multiselect is enabled, and it must stay this way. As part of my program, after the user presses a command button, I need to use the row indexes of the selected rows in order to copy the selected information into an array which is then placed in a different listbox, and then delete the items from the original list. Pseudocode of what I want to do:
[Code] .....
But my understanding is that .ListIndex does not work this way with multiselect listboxes. I've tried searching for a solution for a while, but I cannot find one.
View 5 Replies
View Related
Jul 16, 2013
Let's say I have one column of;
1
2
3
4
5
6
7
8
9
What is the most efficient way to change this into '3-columns & multi-rows' like this?:
1 2 3
4 5 6
7 8 9
The actual list is a lot longer and numbers are not in order.
View 7 Replies
View Related
Aug 21, 2013
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
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
Nov 11, 2006
I created 2 groups of lines (or outlines) with sub-outlines (so I have 2 levels) in the same worksheet.
I would like, via a macro, to display the 1st at level 1 and the 2nd at level 2.
Of course, I tried the "showlevels" command but it puts both groups at the indicated level.
Is there any command to work only on 1 of the 2 groups ?
View 4 Replies
View Related
Aug 30, 2007
With reference to thr great charts that Andy Pope has made available on his website; specifically the thermometer chart and the dollar spit chart. Is there any way that one can use these methods on something shaped like a bucket or do the shapes have to be "straight line shapes"?
What I was trying to do was to adapt the thermometer method to work on a bucket but I realised that the chart is built on a column chart. Thus a bucket would not work cos the top is wider than the bottom.
View 7 Replies
View Related
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
View Related
Jan 16, 2014
I have a workbook with three sheets.
Sheet 1 is a large data array (approx 8 columns x max 400 rows), than contains the inventory and cost information of a list of products in a running cafe.
Sheet 2 is is a recipe analysis tool that I want to be able to use data validations to cost out recipes, drawing information from sheet 1 in three dependent drop down boxes 1- category, 2- supplier, 3- item name, which then returns that items cost per standard of measure.
Sheet 3 is where I have started to try to generate a unique list for category and supplier to assist sheet 2 in referencing from sheet 1.
There is data overlap across the inventory items, with many items having the same category and or supplier.
Examples that I have found online use the index and match function, in conjunction with naming to return the array required for the drop down, however most of these examples I have found contain simple unique lists.
I cant seem to nut dynamic naming, which is integral as the list is expanding and contracting all the time.
So far I have been successful by manually naming a range (not a satisfactory solution as per above) and then using the indirect function to reference that named array from sheet 1.
View 4 Replies
View Related
Jan 1, 2010
I am making a chart for displaying the amount in inches that we have in the water tanks.
The idea is to have a setup like this...
View 14 Replies
View Related
Jan 23, 2013
I have a sheet on the left is a list of document names, next to the list is a hyperlink to the actual document. On the right is a drop down list.
The list contains:
Started
In Progress
Complete
I want each person to be able to use the dropdown list to move to the next level but not be able to go backwards.
Example of how it would work:
A person would click the link to the document and set the status to Started in the drop down.They would read half the document and set the list to In progress. However they would not be able to go back to Started or a blank cell once they have set it to In Progress. The same applies to the next level, if they set it to Complete, they cannot change it to anything. Perhaps before "locking" it as Complete there could be a message box warning that the action is not reversible and giving the option to leave it as In Progress.
View 6 Replies
View Related
Jan 29, 2014
I know the disabling of 'Save As' has been covered alot. I have successfully disabled both Save and Save As at the file level by the following:
Making file Read Only (which disables Save option)
Inserting the following VBA to make the Save As disabled.
[Code].....
The remaining problem I have is disabling the Save As prompting at the program exit level.
For example, if I have a read only file with Save As disabled and I choose the workbook (file) level close window (X) button, I am prompted to save the file but am not allowed to. This is the behavior I am looking for.
HOWEVER, if I choose to exit the file by selecting the program (Excel) level close (X) button, I am prompted to save a copy of the file and it allows me to do it.
I do not want my ~180 users to have any option to save the file once it is uploaded to a centralized server location. It is a large file and I do not need multiple versions floating around.
Is it also possible to disable the 'Save As' on close at the Program level??
View 1 Replies
View Related
Nov 26, 2011
when you sort by VBA, you can only do 3 level of sorting. is there a workaround solution for 4 or more level to sort?
I am using excel 2007 but the data excel is xls.
View 4 Replies
View Related
