Why can't you just use Find and Replace?
Hey there, 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 "http://localhost/microsoft user/folder/file" when they should all be "http://localhost/mac user/folder/file" or something like that.
Now the good part is that I don't have to put the full "http://localhost/mac user/folder/file" 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 "http://localhost/microsoft user/" 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 'http://localhost/microsoft user/" part of the address or teach me how do it?
Because the hyperlinks are not put simply as 'http://...'
They're dates, names, times, etc. that act as hyperlinks. The link to the file that they represent is a property of the cell or of the text (not sure which). It's not just that the cells have http://... written in them.
I probably could, but it's work related documents that I was told to try and keep confidential. I could make up a quick example in excel though and try to show you what I mean.
Now for this sample, see how the 'Result' column is full of words that are hyperlinks and imagine that I need each hyperlink to have the "C:\\Documents_And_Settings\" taken out of them, leaving the rest of the address. Also imagine that these hyperlinks are not just restricted to one column, but are spread across the entire spreadsheet for hundreds of rows and columns. I think you get the idea.
Also I made this on my main workstation, which has MS Office 2002 for MS XP, but I'm almost positive that this and the Mac 2003/2004 version I'm using is very similar and utilizes VB Script.
Last edited by Mathias Schnell; July 12th, 2007 at 04:06.
Have you tried to record the actions that you need to take to get the hyperlinks to work??
If so can you post the code... I'm sure that just handing out the file names won't be an issue.
Try something like this, as long as the username is the same for all the strings it should work
VB:Sub remove_chars() Dim x As Integer Dim y As Integer Dim strAddress As String For y = 1 To 10 ' starting and ending columns For x = 1 To 1000 ' starting and ending rows strAddress = GetAddress(Cells(x, y)) If InStr(strAddress, "C:\Documents_And_Settings\") > 0 Then Cells(x, y).Hyperlinks(1).Address = Right(strAddress, Len(strAddress) - InStr(strAddress, "C:\Documents_And_Settings\") - 25) ' change 25 to the number of chars in the string to remove minus 1 End If Next x Next y End Sub Function GetAddress(HyperlinkCell As Range) If HyperlinkCell.Hyperlinks.Count > 0 Then GetAddress = HyperlinkCell.Hyperlinks(1).Address End If End Function
Last edited by AndrewJ; July 12th, 2007 at 07:34.
This will search every cell in the used area and replace hyperlink addresses.
Change only the string you want deleted (keep the quotes). Note I left the final "/" off the replacement string so the new address will start with it!
As always, try this on a _copy_ of your workbook
VB:Option Explicit Sub CandCHyperlinx() Dim cel As Range Dim rng As Range Dim adr As String Dim delstring As String 'string to delete: CHANGE ME! (KEEP quotes!) delstring = "http://localhost/microsoft user" 'get all cells as range Set rng = ActiveSheet.UsedRange 'ignore non hyperlinked cells On Error Resume Next 'check every cell For Each cel In rng 'skip blank cells If cel <> "" Then 'attempt to get hyperlink address adr = cel.Hyperlinks(1).Address 'not blank? then correct it, is blank get next If adr <> "" Then 'delete string from address adr = Application.WorksheetFunction.Substitute(adr, delstring, "") 'put new address cel.Hyperlinks(1).Address = adr 'reset for next pass adr = "" End If End If Next cel End Sub
"Questions, help and advice for free, small projects by donation. large projects by quote"
I think you only need;
VB:Sub ReplacePartHyperlinkAddress() Dim hLink As Hyperlink Dim wSheet As Worksheet For Each wSheet In Worksheets For Each hLink In wSheet.Hyperlinks hLink.Address = Replace(hLink.Address, "http://localhost/microsoft user", "") Next hLink Next wSheet End Sub
There are currently 2 users browsing this thread. (0 members and 2 guests)