Use String As File-hyperlink?
Jan 27, 2014
I want to add hyperlinks to certain cells using drag and drop. I've got a userform with a toolbar that I use as a drop zone for files (called tbDrop). In the userform, below the dropzone is a label called "label2"
The dragging and dropping works as a charm, label2 shows me the file location and adds it as a hyperlink to the cell.
However, when I click the hyperlink the file can't be opened.
I've used the result string, added it manually as a hyperlink and recorded it. That worked, but i saw no difference in the recorded macro.
VB:
Private Sub tbDrop_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
Label2.Caption = ""
Dim a As String
[Code]....
View 3 Replies
ADVERTISEMENT
Nov 24, 2011
I can't work out how to strip the hyperlink address in cell rRecordID(1,11).
Code:
Private Function GetRecord(lRow As Long)
Dim i As Long
Dim vMyCopy As Variant
[Code].....
View 3 Replies
View Related
Feb 26, 2008
I have a sheet which contains a folder listing of files with the filename displayed as a hyperlink.
I would like to have a formula which works out a couple of values based on the file name which has been copied into a given cell as a hyper link.
In my sheet Column F contains the file names (as hyperlinks)
e.g. Anab AM1 2006-02-27.pdf
or CD 01_02 2006_05_18(2).pdf
I'd like to create a formula for Column A which will give me the first 2 blocks of text which are seperated by a space (in the examples above it would be "Anab AM1" and "CD 01_02") Anything after the second space should be ommited (including the 2nd space itself)
I'd also like column B to find the date within the filename. Note this is not always the rightmost part of the filename (excluding the .pdf) I've worked out I can use the Find function to find the '2006' (I'll always have the same year in here as my folder listings are sorted by year) but it returns the character where 2006 is located e.g. for "Anab AM1 2006-02-27.pdf" it gives me 10.
My date formats are always in the form yyyy-(or_)mm-(or_)dd
View 9 Replies
View Related
Nov 12, 2008
I'm trying to insert a hyperlink to another excel file such that when you click on the link it opens that file up... When i go to insert a hyperlink and i select the file.. when i click on the file it says "the adress of this site is not valid. Check the address and try again" The file is on another drive on the server and i was wondering what the restrictions are in terms of where the files have to be in relativity to each other
View 4 Replies
View Related
Nov 17, 2012
I created a hyperlink to a .PDF and it works when I test it. However, when I save the file, go in later, and select the hyper link I am met with a 'Cannot open the specified file' message.
I have had this prior in Excel. The hyperlink works fine in Outlook, Word, and Access...
View 2 Replies
View Related
Jun 10, 2008
When you click on Cell C5 (which will say "ADD") I would Like the (Computer Browse Menu) to appear
you can then search for the file and press Attach
This will then create a hyperlink in that cell (or in a different cell) Saying "File Attached"
when you click on "File Attached" it will then open that file
I completly lost on how to do this
but here is the formula i have so far when it comes to click on the cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim arrValues
If Target.Count > 1 Then Exit Sub
If Intersect(Range("C5:C5000"), Target) Is Nothing Then Exit Sub
End Sub
View 9 Replies
View Related
Jun 23, 2008
I have an Excel 2003 application that creates a new workbook and adds hyperlinks that link up to other tabs withing the same new workbook.
Issue is that if say the default workbook name was "Book1" but the user saves this workbook with any other name, the hyperlinks die.
Is there anyway to use the current name of the file (instead of "Book1" in this example) in the formula "=HYPERLINK("[book1.xls]Sheet3!A1", "test")"
View 9 Replies
View Related
Jan 14, 2008
why no formula based hyperlinks will work in my workbook. even the simplest formula's. either it returns "cannot open specified file" or it does absolutely nothing even though the cursor acknowledges its a hyperlink.
View 5 Replies
View Related
Sep 29, 2012
Basically I have a folder with file names in the format:
"XXXXX characterstring"
where X is a digit
E.g.:
45627 -jdsuuuds.pdf
93823 Coates.pdf
98221 dagggiisa d sass.pdf
And I have an excel sheet with a list of the numbers at the start of these file names, eg:
45627
93823
98221
So what I want to do is have each cell link to the file with the corresponding number. So I click "45627" and it links to "C:path45627 -jdsuuuds.pdf"
I could do it if the file names matched exactly to the cell value, but when they don't.
It's super easy when the file name matches exactly:
VB: ActiveCell.Hyperlinks.Add ActiveCell, "C:path" & ActiveCell & ".pdf"
VB:
Sub makelinks() ChDrive "c"
ChDir "c:pathl"
For Each ce In Selection
filee = Dir(ce.Value & "*.pdf")
[Code]....
View 2 Replies
View Related
Aug 12, 2009
Is it possible to do hyperlink to a specific cell in one of the tabs of another excel file? I managed to link to another excel but can't make it go to a particular cell.
View 11 Replies
View Related
Jul 20, 2013
I have a spreadsheet with hyperlinks to various file types (PDFs, Docs, etc.). Is there any way that I can have the hyperlinks update themselves automatically when I move one of those files to another location?
View 2 Replies
View Related
Jul 5, 2006
How can I change the text color within a cell when I am using a concatenation formula? I have the following formula;
=" Total Bonus Earned ("&TEXT(A2/VLOOKUP($A$1,PR_DB,2,False),"#0.0%")&" of 2006 Earnings):"
In the text function portion I would like to format the result in red. An example of the result of my formula I am seeking is as follows; Total Bonus Earned (10% of 2006 Earnings): Do I need to create a custom format and if so how do I call it up within the text function?
View 2 Replies
View Related
Jul 10, 2006
I have a excel Product database and a Data Entry Userform. I can gte this to work fine but need to make some improvements to make it look professional.....here's what I am looking to do...:
1) When a user add a new product using the Data Entry form, I want the user to be able to add a hyperlink to the datasheet of that product and this hyperlink will then be saved in my database in column M of that product alongwith all other details in other columns. The DataSheet files are PDF files that are stored on the public drive. So I am hoping to use a browse function or similar to select the file to hyperlink to...
My current hyperlinks look like this which I have created manually directly into the column M (not through userform):
V:PublicCOMBData SheetsData SheetsXXXXX.pdf.............
View 3 Replies
View Related
Feb 15, 2008
I have the following formula =HYPERLINK(("G:ProjectTest Project"),"Link". Now this works fine, However I have a cell "A5" containing "Test Project" And I would rather the link refernced the cell contents So the code would look something like this; =HYPERLINK(("G:Project"+A5+"),"Link"). or something to that effect, this obviously doesnt work hence why I am here.
View 3 Replies
View Related
Jun 13, 2008
I made an excel sheet that links to other sheets on a different drive. It works while I am making it, but upon save and reopen all the hyperlinks produce this error: The address of this site is not valid. Check the address and try again.
Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Auto Merged Post Until 24 Hrs Passes;Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive. Figured it out. File, Properties, Summary tab, Hyperlink Box to base Drive.
View 2 Replies
View Related
Dec 26, 2013
On sheet4 is a list of dates and I named that range as UPDATES2013. Each cell in that range is a hyperlink. Sheet1 cell I3 is a data validation list with the named range UPDATES2013 as the list. When a selection from Cell I3 is made I can get it to follow they hyperlink with the code I have. The problem I am having is if there is no file found, then I would like my Userform1 to show. I have tried several different things with the code, but every time I think Im going to get it to work....it doesn't. It either wont show the Userform at all, or it shows it even if the file is found. I am currently putting the code to run the macro on the worksheet module using the worksheet_change event.
One last thing...I also have hyperlinks throughout Sheet1 that will lead to the same files as in the named range UPDATES2013 from sheet4. I need the same thing to happen if they click one of these hyperlinks instead of using the data validation list in Cell I3. Is there a way to make one code that will show Useform1 if a hyperlink is selected and the file cannot be located (whether it be by the individual cells on sheet1...or the data validation list in Cell I3)?Here is the code I am using right now.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
DoFollow2013 ActiveCell
End Sub [code]....
View 9 Replies
View Related
Oct 1, 2009
I'm trying to add code that create a hyperlink to a file in a recently created folder in the path of L:Elec Dept ProjectsRELEASED FOR CONSTRUCTION". The code below should find the newly created *BOM*.xls created and created a hyperlink in the next available row starting on row 27 and column O on my worksheet "BOM". The code currently runs with no errors, but I'm not seeing any resulting text with path / links created.
View 5 Replies
View Related
Mar 30, 2012
I have an Excel file that has a persons name in column A, company name in column B, email address in column C and a hyperlink to a specific file to be sent with the email in column D.
I have the code to send the email and it works fine.
I've searched the net for mail merge and hyperlinks and have only become confused.
Here is what I want to do, attach the mail merge created file to the email, then the file associated with the hyperlink, I want to attach the actual file from the hyperlink into the email.
View 1 Replies
View Related
Sep 19, 2007
I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.
Assume 2 workbooks - book1 and book2, both located in "c:" (root of drive C)
Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.
After MOVING(!) book1 to a new location (D:DATA) and clicking the HL - I get an Error "cannot open the specified file".
While book1 was still in root C: - I tried to force to c:ook2.xls by typing it in the HL address window but after saving, Excel deletes the "c:" and leaves "book2.xls" only – I think because both files were located I the same directory.
I am aware that my "problem" can be solved by using the function HYPERLINK – but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).
View 9 Replies
View Related
Jun 12, 2014
I would like to write a macro that would insert a hyperlink to another file an would prompt the user to select the location of the file once the macro was run. I am not sure how I would get my macro to prompt the user to define the location (which is located on a network drive).
View 7 Replies
View Related
Aug 30, 2012
I Have a CheckBox Thats Linked with CA22
In Cell CB22 I have the Following Formula:
=IF(CA22=TRUE,"1","")
In Cell CC22 I Have...
=IF(CB22="1","RM-4E ADD1",HYPERLINK('RM-4E (Add - 1) (2)'!A1,"RM-4E ADD1"))
The Sheet namne its supposed to Hyperlink to is called
RM-4E (Add - 1) (2)
When the Check Box is Unchecked it Changes CA22 to False and Puts in CC22 Text: RM-4E ADD1
When the Check Box is Checked it CHanges CA22 to TRUE and is supposed to create a Hyperlink to that sheet...Only when i click it it comes back with
'Cannot open the specified file."
I even went as far as typing out the Word Hyperlink( clicked the sheet and selected Cell A1 on that sheet but it still comes back with the error!
Excel 2010
View 9 Replies
View Related
Feb 27, 2013
How do I create a hyperlink in an Excel 2003 file that will open a PDF file with Reader?
I have an Excel file of hundreds of Customer names and invoice numbers. I have a directory of PDF files. The name of each PDF file is the customer name, a space, and the invoice number. I should be able to have a column in the Excel file that contains a formula that creates a hyperlink to open the corresponding PDF file.
I used the HYPERLINK function and built the path and file by concatenating information. When I click the hyperlink, I keep getting an Excel error: Cannot open the specified file.
Do you think this problem is something of my own doing or some "undocumented feature" in Excel?
View 1 Replies
View Related
Mar 31, 2014
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
View 1 Replies
View Related
Apr 3, 2014
I need hyperlink to pdf (as this is given in one block how to give hyper link to pdf file).
I want to give give pdf hyperlink in in excel to locate the pdf file with specific page or topic number in pdf file (like pdf file name is 'ABC' and page number '10')
Can i give this type of specific link in excel?
View 1 Replies
View Related
May 21, 2013
I need a macro to create a hyperlink i.e....... I have an excel sheet with numbers in column A.....I have files in a folder that matches the numbers in column A....
How would I create a macro to create a hyperlink from the numbers in column A to the "matching" file names in a folder?
View 3 Replies
View Related
Dec 18, 2012
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 Replies
View Related
Jun 16, 2014
I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code] .......
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
View 7 Replies
View Related
Sep 12, 2006
I wrote some code which has compiled a LOT of text files telling me what computers have licence for certain software.
The name of the txt file is the computer name and data within is simple:
--------------------
Audit of GQL license
Audit of Visio license
Audit of Frontpage license
Audit of Project license
Audit of Visual .NET license
Audit of Word (Office) licenses
Found Office
--------------------
as we can see here this computer has 1 licence for OFFICE.
If the string "FOUND" is found then i want to be able to paste the licence name in a spreadsheet and in the next cell underneath paste the filename it was found in the e.g.
VISIO
comp1,comp2,comp3,comp4
WORD
Comp2, comp3, comp4, comp5....and so on......
View 9 Replies
View Related
Apr 13, 2007
I've got a host of files within a host of directories, e.g:
c:ContractsJim BobJim Bob Site.xls
c:ContractsJane JonesJane Jones Place.xls
c:ContractsJeremy HillsHills House Lvl 1.xls
I'm working in VBA to try to extract the site name from the file string. Unfortunately there is no other reliable location where I can find an accurate name of the site, so it needs to be taken from the second subdirectory. So I'm using the following code to get the file location:
Sub FilePicker(fileToOpen As String)
ChDrive "C:"
ChDir "C:Contracts"
fileToOpen = Application. GetOpenFilename(FileFilter:="PPM Files (*.xls),_
*.xls", Title:="Select a PPM Scope File", MultiSelect:=False)
End Sub
This returns a string along the lines of "c:ContractsJeremy HillsHills House Lvl 1.xls". From this string, I need to isolate the string "Jeremy Hills".
View 3 Replies
View Related
Jul 2, 2009
I have this code that looks through my worksheet once the conditions are met it will email, and in column "M" I put a hyperlink to where the document is stored. All works as far as the email format, even grabs the hyperlink but it’s not clickable in the email.
Here is the code.
I am outlook 07 and vista 07.
Option Explicit
Const Startingrow = 11 'Data starts on row ##
Const AlarmDelay = 183 'send warning
Sub CheckTimeLeftFac()
'References needed :
'Microsoft Outlook Object Library
Dim i As Long
Dim j As Long
Dim msg As Long
Dim Lastrow As Long
Dim WhoTo As String
Dim SubjectLine As String
Dim MessageBody As String
Dim olMail As Outlook.MailItem
Dim olApp As Outlook.Application
Dim strLink As String
View 10 Replies
View Related
