a FORMULA (not a macro) that can be used to diplay the parent folder name in a cell? I don't need the file name or workbook name, just the folder name.
Example,
My Workbook is stored in the following location: C:FilesExcelFiles
I want a fromula to return "ExcelFiles", since that is the parent folder
I have searched previous threads but found no resoultion.
I have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
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.
I need a macro which displays the folder and subfolder name from a specific path in the column A of the excel sheet in the same heirarchy. Also whenever a sub folder name is displayed in column A, I need its main folder name to be displayed in the same row Column B i.e offset(0,1). I have a macro, but it displays the folder name in the 1st cell of column B, I need it to increment to the subsequent cells based on subfolders.
Sub Step1()
Dim startRange As Range
Sheet1.Cells.Clear
Set startRange = Sheet1.Range("A5")
'Parent Directory - Change this to whichever directory you want to use
ListFoldersAndInfo "C:desktop", startRange
End Sub
[code]......
I am working to make an automatic result card in which excel automatically gets data of every student which is present in sheet within the workbook by simply writing roll number of the student in a cell. and i have done it easily by Vlookup and index functions but the only problem i am facing is, that i want to show the picture of each student by roll no. pictures are in one folder on my hard drive, now i want that as the roll number of the student is written the result excel should display the picture (from the folder, each picture named by roll)of the student. for example i write 1234 in roll no cell and want to get and show the picture no 1234 in another cell. (from the folder)
View 2 Replies View RelatedImagine 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):
[Code]......
(and about 5000 more sets with garbage page headers in between the sets of parent rows)
Basically the data spit out by machine is separated by a blank row.
Let's say I need all the data with the child row "tn" , so I would autofilter /PivotTable it for that entry, but that leaves me with a blank column A. It'd be no issue if I had the column A heading in the child rows (and the number of spaces in between is random like 2 sometimes 15+ so Vlookup/Index/Match aren't too useful). It is possible to have a function that repeats the first row until it hits a space?
Like so:
[Code] ......
Then I could autofilter/pivottable to get:
[Code] .....
I have the following table and i need to be able to present it an indented list of parent child relationship. The hierarchy can go 1000+ levels deep. How do i achieve this using VBA?
OName
OPackID
PPackID
PName
ParentID
Name1
556
556
Box1
389
[Code] .........
If there is a better way of presenting it, it isn't limited to an indented list.
The code below works really great which Andy helped me with, but now I've put the textboxes that are changing in a frame. That is because I need to scroll the textboxes. That works in another form I've got, but for this form I've also got the textbox, TBSum601, which is updated from the sub UpdateTotal() (see below). BSum601 is placed on the form but not inside the frame. When I run the application it stops in Sub TxtGroup_Change() at the line: TxtGroup.Parent.UpdateTotal
I think it's because of that the textboxes for the TxtGroup now are in a frame instead of as before, on the form. don't know exactly what the "Parent" does, but I think it's something I have to do with the code there.
Originally Posted by Andy Pope
Class event
VBA:
Private Sub TxtGroup_Change()
If Me.TxtGroup.Text = "" Then
Me.TxtGroup.Text = 0
End If
TxtGroup.Parent.UpdateTotal
End Sub
Public Sub UpdateTotal()
Dim lngTotal As Long
Dim lngIndex As Long
For lngIndex = 1 To UBound(X) / 4 ..................
I've been searching all morning through various posts on this subject, but I can't find anything that I can adapt to my needs. Here is the code I have:
ChDir "C:Documents and SettingsmynameDesktop"
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsmynameDesktopBook1.txt", FileFormat:=xlText, _
CreateBackup:=False
This file will be used accross multiple computers and therefore the directory will always change. What I need to hapen, is for this code to save the workbook in the same directory as the origional file. I also want the file name to refer to a cells value, as I will be having the user define the name through a userform.
I have data for , say - Cars, Company and Average.
I solve this upto 2 level i.e cars and companies.
But i want a third level also.
When i select Car from drop down list, using data - validation - list , in cells(1,1) , i should get all companies in Cells(1,2) and on selecting companies , i should get Average in cells(1,3).
If i do define name ,and then data validation list , and then use Indirect() function, i can solve it upto 2 level.
Eg. I can get company for car .. but how to get Average Also.
I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).
View 6 Replies View RelatedI'm trying to get the parent item name of a clicked cell (e.g. when I double click on field "Commessa 2" I'd like to get "Region 3" and pass it to another sheet)
Here is my code:
[Code] .....
When I run this code, I get the message "Unable to get the ParentItem property of the PivotItem class"
Attached Image : Capture.PNG
I have searched numerous times for the answer I need, but no joy yet.
I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.
I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts.
100
100
100
100
200
200
200
200
300
300
300
300
Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.
100
xxx100
xxx100
xxx100
200
xxx200
xxx200
xxx200
300
xxx300
xxx300
xxx300
I want an option, if click on drop down button on parent cell than few a cells will come out in which I have mentioned details of the parent cell.
By pressing the same button again those cells will be hidden or covered under parent cell.
I have a large flat hierarchy table. I am looking for a method to select a parent and return a list of my lowest level children. I am somewhat familiar with VBA but I don't know where to begin coding this.
This has been a great learning source over the years, but this is one of the few instances I cannot find my problem so I am now posting. Example below:
Child
Parent
4
5
3
4
2
3
1
5
40
50
30
40
20
30
10
50
22
100
If I select 5, I want values 2 & 1 returned.
If I select 50, I want values 20 & 10 returned.
If I select 100, the value 100 should return.
If I select 40, the value 20 should return.
I have a listbox with a state and below it several cities. For example:
New York
New York - New York City
New York - Nassau County
New York - Westchester
New Jersey
New Jersey - Newark
New Jersey - Monroe County
New Jersey - Passaic
I would like to select the state and have it auto select all of the related cities including the state. So if I select New York,
It would look like this, where all are highlighted (as if selected). I coudln't find the highlighter on the thread tool bar:
New York
New York - New York City
New York - Nassau County
New York - Westchester
My query is regarding internet explorer 6 automation via code in VBA (Excel 2003). I'm trying to open a page on clicking a button in main page.
I'm able to open both parent and child pages via VBA, but now want to automate events (like radio btn click etc) in child page.
below is my initial code -
'========
Sub ListLinks()
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim i As Long
'Create new instance of IE
Set IeApp = New InternetExplorer
I have a folder for each month, and in the folder there are worksheets for each day of the month. eg for January folder, it will contain 31 worksheets. They all have the same column headings. I want to know if there is any way i can use a macro to automatically insert a formula in each worksheet, using loop?
View 2 Replies View RelatedI have this formula that opens the current folder where my file is stored:
=HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Open Current Folder")
This works fine. I'd like it to open the folder one level up from the current folder, but I really don't understand formulas well enough to know if this is even possible.
My question is:
I do multiple audits using excel, and would like to start a database as I put more audits into one folder. So it updates the values as I insert more audits into the folder.
My audit is a workbook which has multiple worksheets. But for each audit, it is the same worksheet with the same cell. The audit consists of yes and no questions, where you put in a x for either one. I would like to start a database, so for each question on my audit, I would have a percentage of yes or no for all my audits. For instance, question 1 , 7 out of my 10 audits, I had yes for that question.
How do I write a macro, so it counts the x's for multiple workbooks, and updates automatically for each question as I add more audits into that folder.
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies View RelatedI am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
how to write vba code using vlookup formula insert pic from picture folder
View 5 Replies View RelatedI have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
I have a s/s which is built on IF functions and references other sheets and I have used the s/s in the past without issue. However now I want to ammend the formula a little and excel will only display the formula itself, not the result of the formula. I have tried CTRL ~ to turn on/off the show formula function but this makes no difference.
My reason for changeing the current formula is that I need to turn a 2 (numeric) to 02 (which can be either numeric or text). This is the new formula.
=IF($A4="","",IF(VLOOKUP($A4,HCGTH1_DATA,22)=2,"02",VLOOKUP($a4,HCGTH1_DATA,22)))
Is there an issue with the formula or some setting in excel?