I have a multisheet workbook where I am trying to modify all hyperlinks.
The links are all to jpgs and are currently on a web server, but I want to change them all to reference a local folder.
Eg: [url]
Obviously, I'll leave "file.jpg" alone as I don't want the specific image to change, just the place where it is trying to find the image.
I have a folder in Dropbox (important that it is stored there, it is shared with 15 people who are on a bunch of different networks) that contains 15 spreadsheets. One of them is the master, containing performance data for 14 people. The other 14 are for each individual person pulling their performance data from the master. Monthly, the master spreadsheet gets updated, and I want the user spreadsheets to update, so I can email the individual spreadsheets to each person so they can track their performance. Of course, they cannot see each others numbers, so they have no access to the folder. There are a handful of people that have access to the master. All the spreadsheets will live in this folder all the time.
The issue is that on my computer the path to the master is different then it is for other users because of where Dropbox stores local data by default, and for a lot of reasons, I cannot change it.
Is there any way to tell excel that the master (or the other 14) live in the same folder so there is no need for the first part of the path?
How to write the VBA which can multi-select the files and store the selected filepaths in the string.
View 4 Replies View RelatedI have a question regarding hyperlinks...now what i wish to do is force a hyperlink in what i thoguht would be esily dne in a worksheet change event...hw wrong was i.
Lets say i type 'test' in A1 and press enter...now because i ave pressed enter A1 is no longer the active cell so the code in the sheet change event inserts the code into A2 but because A is blank the 'Text to display' becomes the hyperlinks address.
I wrote a simple with/ end with thinking it would work but it doesnt because of the issue above (Hope i am making sense here)
I got round it by copying the activecell (which would be the cell underneath) and pasting it back to the original target cell, which because it contains text does what i wantbut i am hoping for a much cleaner solution.....
My target column is '1' and only one i will just need to exit the sub anywhere else on the sheet
When changes are made to one sheet, I want it to update a table in another sheet. However, a good portion of the changes will involve adding hyperlinks to my original sheet. However adding hyperlinks doesn't seem to fire the worksheet_change event.
Is there a way to listen for when a hyperlink is added (and to which cell)?
I have a question regarding hyperlinks. I created a combo box where if I select a product (s), the sales data will change in another cell by using the offset formula. I was wondering if there is a way where each data that changes in the cell can have a hyperlink to a different location in the workbook? Can you do this by a formula or only use of VBA?
View 1 Replies View RelatedIs there a way that under "Type the Cell Reference" that you can make it automatically change to the cell the hyperlink is on?
The reason why i ask this is because I have hyperlinks linking to there current cell but once i delete a row above that... the cell refernece doesnt change therefore changing the cell reference to the cell above it
I am trying to hyperlink to a cell, & i want to change the color of this cell after the hyperlink activated.
VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub
I change my tab names with the above but than my hyperlinks breaks.
I have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.
Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.
I have a workbook with a template worksheet. I can generate new worksheets using the Template, and create hyperlink to the new worksheet (Let's call it "New"). However, if an user changes the name of the worksheet from "New" to "SomethingElse", then the hyperlink breaks. How do I get the hyperlink to be automatically updated to "SomethingElse"?
View 4 Replies View Relatedis it possible to use references and concatenate them together that they are still references?
i have in:
file1.xls
sheetX:
A1: 'C:Infonic[file2.xls]sheet2'
file2.xls
sheetY:
A1: test
what i need is in sheet2 of file1
the value of file2.xls!sheetY!A1 = test
the problem is that i need to use the path to file2!sheet2 of the cell A1 in sheetX
is it possible to use sheetXA1 together with sheetYA1 as reference in sheet 2 so that i get back test as value.
I am using formulas that use ranges in outside file locations. Due to my company's file management "nuances" the file paths are exceedingly long and make it difficult to read formulas. Can I use the Define Name feature to enter the external file path and file name? My attempts so fare don't seem to be working.
View 1 Replies View RelatedWhen I use the folder path on my computer it works great. However, Dropbox is in slightly different locations on other computers that would need to run the macro. Is there a way to get to the files without modifying the code for ever computer? The only difference in the folder path would be everything up to where Dropbox is in the path and would need to include usernames as below.
[Code] .....
I am running the macro below which changes details in my spreadsheet using details from an external table.
Range("C7:E36").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C42:E51").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C58:E69").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=15
Range("C75:E86").Select
If I want to perform this process without having to change the directory to the t drive etc, how do I change the macro? Is there a way, that as long as each of the relevant sheets are in the same directory, i can point the macro there? Ie, as long as all saved together in My Documents or Desktop, the macro automatically looks there?
I have a file which I am using at work to compile production shift reports. I have created a sheet called Live Report and created a macro called 'New Day' that copies the entire sheet and renames it as yesterdays date (using a separate sheet called Data with a =today()-1 and text formulas thus the sheet tab for 14/2/07 is called 14 Feb) it then clears certain cells on the Live Report sheet so that information can be entered again.
My problem is that I want to now create a table of values that compiles values from each sheet for the month. What I wanted to do was have the names of all possible sheet tabs listed in column A and then as we get to each date the links will become active for the 20 or so values on each sheet. So each row will have the sheet tab name in column A and then columns B to R will have links to that sheet. As I am lazy and the linked values will always be in the same location I just wanted to drag the first row of links down the table using the value in Column A as part of the filepath/link.
My problem is no matter how much I try I can't use cell values in filepaths. If this is impossible I would like help with a macro that can find the right row in the table based on todays date and copy certain information.
I have about 20,000 hyperlinks in my excel sheet that I would like to assign to individual images.
For example, how do I link an image in cell A2, using a hyperlink from cell B2?
I know that if I wanted to simply hyperlink text, I would use =HYPERLINK(B2,"text"), but this reference won't work for images.
I also know I can right click on the image and insert a hyperlink that way, but this obviously isn't practical because I am working with over 20,000 links.
I have Data as give below in Sheet 1
srno
model name
total scope
drilling
lagging
skid
corrugation
1
HP 50BB/10.54
1023
24
22
12
200
[Code] .......
In sheet 2 i want to create a hyperlink such as "HP". If I click on HP it should extract all Model Names which start from HP and give me all corresponding results. Similarly for CM or SM
Note: HP / SM / CM are my Standard Names.
Result e.g.
Sheet 2 cell A1 contains HP
If i click on Hp it should return this table.
SR NO
model name
total scope
drilling
lagging
skid
corrugation
1
HP 50BB/10.54
1023
24
22
12
200
[Code] .......
This should be my result if i click on HP.
I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
I have about 1000 links (to files on a server) in my Excel 2007 spreadsheet. I would like to edit them all at once so the word 'link' is displayed but the link stays the same. I can do it for each entry, but have no idea how to do it all at once - is there a way?
View 6 Replies View RelatedIs it possible to link a cell with multiple sheets, normally a cell can be
linked with only one sheet.
I'm just now working on what appears to be a Macbook (never used Macs before) and I'm working on an Excel file for my job that is going to be pretty tedious for me unless I can come up with some form of automation (a macro).
Here's what I have to do. Every hyperlink in this excel file has the wrong address connected to it (don't ask). The basic problem is that every hyperlink goes [url] when they should all be [url]or something like that.
Now the good part is that I don't have to put the full [url]because since the Macbook is connected to the server that these links are linking to, I just need to say "folder/file" and the rest behind it is added automatically. THe problem is that I have no way to go to each and every hyperlink and just delete the [url] part.
I've tried using the macro recorder, doing this change to a single cell, then running it on other cells, but it doesn't work and I get errors. I have some programming knowledge, so I tried to go into the VB Script editor to edit the macro myself to my liking, but I don't know VB very well and it'd take me a while to learn it, on top of learning Excel's libraries.
So could anyone write up a quick macro for me that will go through every cell with a hyperlink and delete that [url] part of the address or teach me how do it?
I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])
This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?
Am creating a large medical teaching database on Excel that lists JPEG images (a few hundred) and the file paths for each image into separate Excel columns using a VBA macro.
The JPEG image filenames are numbered in ascending numerical format (i.e 1.jpeg).
We have multiple macros that have been developed by various users in the company. Many of these macros have file paths hard coded into the macro to reference file locations with networked drives.
We are planning on making some changes to the network file structure which will break these macros. I realize we will have to go in and manually change all these. I want to ensure that the end users have the flexibility they want, but not have to touch each macro if/when folder structures change, or files migrate to SharePoint etc.
ini files, master reference Excel file...,
I have a vlookup worksheet like described in this thread: [URL] .... This works perfect. I would like to be able to jump to the vlookup result using a hyperlink.
In the thread worksheet the formula in C2 is like
=VLOOKUP(A2,INDIRECT("'"&INDEX($G$2:$G$9,MATCH(TRUE,COUNTIF(INDIRECT("'"&$G$2:$G$9&"'!C2:C100"),A2)>0;0))&"'!C2:D100"),2,0)
In cell D2 I placed the formula (font: webdings )
=IF(C2="","",HYPERLINK("[Workbook1.xls]"&("Sheet2!"&(ADDRESS(MATCH(C2,Sheet2!$D:$D,),4))),"i"))
(Due to the Dutch Excel I use I replaced ";" to ",")
The link works because I hardcoded "Sheet2" into the formula where the result can be found. But it would be much nicer if I didn't had to put the sheetname into this formula.
Is it possible to use range G2:G8 like in the first formula to achieve this? I guess you have to use INDEX and/or INDIRECT but I barely understand the first formula ...
I have created a Macro that lists and creates links of all the Excel Documents in a Directory (500+). I would like to extract from each of those files data from a single cell on a certain sheet. (The sheet and cell are the same in each of these files). The code I have written opens each of these files, copies the data and pastes the value in the column next to each sheet. This is a very time consuming task because of the shear volume. I feel that there is a better way to do this, I'm just not seeing it. If you could come up with a way to create this as a reference that would be great too instead of having to rebuild the entire list if the data changes. Here is the code I have written.
Sub Test2()
' Select cell A1, *first line of data*.
Range("A1").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets("Contact Information").Activate
ActiveSheet.Range("B8").Select
Selection.Copy
ActiveWindow.Close
ActiveCell.Offset(0, 1).Select
PasteSpecial zlPasteValues
ActiveCell.Offset(1, -1).Select
Loop
End Sub
The VBA shell command is as follows:
programPath = "C:Program FilesInternet Exploreriexplore.exe" ' works
'programPath = "iexplore.exe" ' does not work
Shell programPath + " " + fileToLaunch, vbNormalFocus
but the drawback is that the invoked program (iexplore.exe, at least in my case) needs to have the FULL PATH to where the program exists = the "C:Program FilesInternet Explorer" which may or may not work on someone elses computer. This hardcoding will not work and is not transportable.
Is there a trick to find where the executing program lives? or launching it without the path?
I have a hyperlink mailto - =HYPERLINK(CONCATENATE("mailto:",C2,"?subject=",C3,"&body=",C4),"Request Update")
But the body only supports a line or two of text, which isn't useful, and doesn't allow for different lines to be placed within it.
I then tried addeding extra text with another cell - =HYPERLINK("Mailto: "&C2&"?Subject="&C3&"&Body="&C4&" "&TEXT(C5,"$#,##0.00")&".")
But this doesn't work either. Both come up with #value error with too much text..
I would like to add hyperlink to multiple JPEG images (separately) in Excel 2003 and send those images via email to another person. On receiving the mail at the opposite end, the person concerned should be able to view the images by clicking the link. How to do?
View 1 Replies View Related