This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.
Dim fName As String Dim fExt As String Dim fPath As String Dim fFullPath As String fName = ActiveCell.Value fPath = "M:Books" fExt = ".pdf" fFullPath = fPath & fName & fExt ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............
I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.
In trying to make an alphabetized record of all inventory for work, I ran into a snafu when creating shortcuts for other users to the different letters.
I hyperlinked something like a table of contents together and hyperlinked them to the cells, so clicking on B in the table of contents takes you to cell B64. I want to future-proof it with absolute references but haven't been able to.
I tried right clicking and typing in the cell name--but when I add more cells, instead of moving to B72 like it should, the hyperlink stays in B64, which is now part of the As.
I also tried: =HYPERLINK(A$64,"B") as well as =HYPERLINK(B_Start,"B") by naming the cell. The name went to B72, but the link stayed in B64
I'm having trouble creating a hyperlink that will automatically take a value from a cell and link me directly to a file of the same name across the company server.
For example in column B I have a number along the lines of
In a folder on the server I have a word document that goes by that exact file. I need to create a hyperlink that looks up that value and searches for that specific document in the location file, and then directly open that file.
The value in column B will change with each line so as I copy the formula/hyperlink down I want it to automatically go in and replace the text and then lookup the file.
I have tried the concatenate function within the hyperlink but not had much luck.
I have a problem in that in using the paste special on some links I created using HYPERLINK/VLOOKUP formulas the hyperlinks themselves have gone (ie nothing happens if you click on the data).
What I want is a macro that can turn a cell's contents into a hyperlink (i.e the location and friendly name are the same) by means of a simple keyboard stroke. I tried doing this using the macro recorder, but it didn't recognize when I was trying to paste the text into the Insert Hyperlink address bar and it just recalled the name of the address in the VBE window as if I'd written it in manually.
I have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.
The setup *I have a folder called Zircon Plant *That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders) *The Register is located in 03. Documentation *The PDFs i want to hyperlink to are located in 02. PDFs
What i want to do *i have 2000 rows of drawing names *the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21) *the cells i want to contain hyperlinks are V21 - V2020
so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.
(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)
If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"
based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is
' Select PDF range ActiveSheet.Range("u21").Select ' Promt PDF Hyperlinking ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"
I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets
I need to create a table of non numerical data that I can refer to in a formula, which will compare two pieces of data (both also inside this table), and based upon which is "higher up in the hierarchy" - the cell will display a certain result. Basically it will be an IF function using non numerical data that has a numerical value / "rank" attached.
Specifically - I need to allocate values to this list in cells A1:A5... EUR GBP AUD NZD USD ....where EUR is highest ranked and USD lowest.
Then in column B and C will be listed each of these values but in various combinations. In column D I will build simple IF functions to show the highest ranked in each combination. eg B1=EUR C1=GBP D1=IF(B1>C1,B1,C1)
My question is about creating a hierarchy of Data Types.
I need to code 3 Data Types: 1.) MealPlan 2.) Meal 3.) MealItem
MealPlan represents a daily meal plan. It should have a certain number of meals. An example of MealPlan would be "Healthy" and have a total of 3 meals. Meal represents a single meal. It should have a certain number of meal items. An example of Meal would be "Meal 1" and have a total of 3 meal items. MealItem represents a single meal item. It should have a certain number of protein calories, fat calories, carb calories. An example of MealItem would be "Broccoli" and have protein calories of .2, carb calories of .8, fat calories of .1.
By using Data Types, I hope to create meal plans, which are made up of meals, which are made of meal items. The following code creates a Data Type hierarchy for two Data Types. I've examined it closely but i'm still having a hard time recreating it for my needs as stated above.
I did all the search for multiple drop box or list box that can connect to other list boxes but no luck.
It's the same as when you surf with Internet Explorer, you can click "Views" in menu bar, list box appear and then choose "Encoding", another drop box appear, then when you choose "More", then another box appear.
Is it possible to have this sort of hierarchy list box in excel where drop box connect to another drop box?
How do I use VBA to rearrange the data from the hierarchy format of Table 1 into the flat format of Table 2. See attached file >Data_Belinda_June-5-07.xls
Table 1: Each record has information arranged in a hierarchy format. Level 1 information is indented by one space on one line; Level 2 information is indented by 2 spaces on the next line, etc.
Table 2: Data from Table 1 have been rearranged into 7 columns. Another column has been added to create a field for the Reference ID.
Reference ID: Two types: i) Created by prefixing with the letters BI , adding the first four letters from column 4 and the first four letters from column 5; ii) As in (i) with the addition of the entire word from column 6.
The report I have may have more than one hundred records and there may be more than ten people within each section.
The accounts listed on this report change on a monthly basis. I am attempting to create a macro or write a formula that can but used each month to fill in this list automatically. On Sheet 1 is a short example of what the report looks like when exported into excel. The numbers on the left are the numerical hierarchy. The long set of numbers are the account numbers and the others are the categories that each are located under.
Sheet 2 is what I need the end result to look like. One of the problems I am running into is how to handle the fact that the hierarchy re-uses numbers. The hierarchy always runs vertical, therefore "5" always falls under the "4" that is directly above but when there is another "4" it should be ignored and continue on up the chart until it finds the first "3" and place that in the cell to the right then find the first "2" and so on.
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).
I have two slicers that are in a hierarchy. These are attached to a Pivot table whose data source is an OLAP cube. Every Partner Parent is part of a Partner Group:
Partner Parents Slicers.png
My problem is that when I click SI Alliance in Partner Group, the Partner Parent slicer does not re-sort in any way. The corresponding selected Partner Parents are scattered throughout the alphabetical list. However, in a different document, I have slicers in a similar situation, except their Pivot table’s data source is a SQL Server database. When I click on a member of the higher up group (Accenture Global Client) the lower level group (Microsoft Account Name) sorts to show only the selected values at the top of the list:
This is with all selected - Accenture Slicers All.png
This is with just one Accenture Global Client selected. Note how the selected Microsoft Account Names have moved to the top of the list - Accenture Slicers Selected.png
How I can configure my Partner Parent/Partner Group slicers to behave like these Accenture/Microsoft slicers? Is there something that I can change in Excel or in the OLAP cube to make this happen? I have already tried right-clicking the slicer and going to Slicer Settings. The settings on the Parent/Partner Group slicers mimic those of the Accenture/Microsoft slicers exactly.
And on a different sheet in the same workbook, have 45 different values to be converted:
What is required?
Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.
Basically, for the 1st Cell for eg..
I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.
Result shall look like below:
Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:
2210-22G1-45R1-45G1-20R1-20GP- It becomes difficult for me. What i need exactly is:
Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.
i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)
right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)
what am i doing wrong? all the things i can find on google show it the way i am doing it.
Sub GetDirList(topfolder As String) Dim FolderArray() As Variant Dim FolderCount As Integer Dim FolderName As String
FolderCount = 0 FolderName = Dir(topfolder, vbDirectory) ' Loop until no more folders are found Do While FolderName <> "" If Not FolderName = "." Then If Not FolderName = ".." Then FolderCount = FolderCount + 1 Redim Preserve FolderArray(1 To FolderCount) FolderArray(FolderCount) = FolderName End If End If
I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.
If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.
this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.