I need to open a file within a folder by entering a textstring in another cell.

[sashaz]

I need to add a link and open a file by entering a textstring in another cell. Is it possible to achieve that? Or, following the below example, maybe 5 macros to do that job better?

A simplified example of what I am trying to achieve would be:

I have five Power Point files stored in a folder named Cars. The Power Point files are named: Toyota, Honda, GM, Ford, and Audi. I want that macro (or macros) to do the following:

Once I typed in a row A, anywhere, say in cell A3 a word Toyota - it would add a link to the file Toyota to the cell B3 and opened that Power Point file at the same time?
Then, say, when I typed the word GM in a cell A4, a macro would add a link to the file GM in a cell B4 and opened that file called GM for me etc?

So, I will always be entering car makes incolumn A and I always want the hyperlink to be open in column B of the same row. The hyperlink should stay added to the corresponding B cell permanently until I delete it if need be.

Hopefully it is not overly confusing description.

I can do some basic stuff with VBA but not too much.

Thank you for your time.

 

[Jerry Sullivan]

Hi sashaz,

Below is a method that doesn't require VBA. Copy the formula into B3, then copy it down for as many rows as you will need.

Adjust the file extension from .pptx to match your PowerPoint file extension.

Book1
	A	B
1	Folder Path:	C:\Test\
2
3	Toyota	Link to Toyota
4	GM	Link to GM
5
Sheet1

Cell Formulas
Range	Formula
B3	=IF(LEN(A3),HYPERLINK(B$1&$A3&".pptx","Link to " & A3),"")

 

[rpaulson]

try this on a copy of you file.
right click on the sheet name then view code and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
mypath = "C:\cars\"

r = Target.Row

Select Case Target.Value
Case Is = "Toyota"
myfile = "Toyota"

Case Is = "Honda"
myfile = "Honda"

Case Is = "GM"
myfile = "GM"

Case Is = "Ford"
myfile = "Ford"

Case Is = "Audi"
myfile = "Audi"

Case Else

Exit Sub
End Select

ActiveSheet.Hyperlinks.Add Anchor:=Cells(r, "B"), Address:=mypath & myfile & ".pptx", TextToDisplay:=mypath & myfile & ".pptx"
Cells(r, "B").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End If
End Sub

hth,
Ross

 

[sashaz]

Thank you Jerry. It works. Every time I cannot figure out how to do things with Excel and someone does it on the fly it seems like a miracle to me. So, your solution is another miracle. Now will work with Ross's suggestion.

 

[sashaz]

Ross, I am getting the message: Compile error: Invalid outside procedure. If it is something very easy for you that may be fixed in a minute and you know what it is, then please suggest. If not don't waste your time on it anymore.

I am happy with Jerry's formula. It creates a link where I want it. The only thing is that it does not open the file for me but I can leave with it. Now it only takes me one click instead of 4-5 like it was before to get what I need.

Thank you both for your time.

 

[Leith Ross]

Hello Sashaz,

This VBA macro is triggered whenever a cell in column "A" on "Sheet1" is changed. The Link is added to the cell in column "B" of the same row only if the cell in column "A" is "Audi", "Ford", "GM", "Honda", or "Totyota" (case is ignored). Afterward, the file will be opened automatically. Change the Filepath and Filetype to match your needs. You will need to add the macro to each worksheet you will be using to enter the data and the links.

Private Declare PtrSafe Function ShellExecute _
    Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As LongPtr, _
         ByVal lpOperation As String, _
         ByVal lpFile As String, _
         ByVal lpParameters As String, _
         ByVal lpDirectory As String, _
         ByVal nShowCmd As Long) _
    As LongPtr


Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Filename    As String
    Dim Filepath    As String
    Dim FileSpec    As String
    Dim FileType    As String
    Dim ret         As LongPtr
    
        Filepath = "C:\Test\"
        FileType = ".txt"
        
        If Target.Cells.Count > 1 Then Exit Sub
     
        If Target.Column <> 1 Then Exit Sub
     
        Select Case Target.Value
            Case Is = "Audi", "Ford", "GM", "Honda", "Toyota"
                Filename = Target.Value
            Case Else
                Filename = ""
        End Select
     
        If Filename <> "" Then
            FileSpec = Filepath & Filename & FileType
            With Target.Offset(0, 1).Hyperlinks
                .Delete
                .Add Anchor:=Target.Offset(0, 1), Address:=FileSpec, TextToDisplay:="Link to " & Filename & FileType
            End With
            ret = ShellExecute(0&, "open", FileSpec, vbNullString, vbNullString, 1)
        End If
        
End Sub

How to Paste a Macro into a Worksheet

1. Copy the macro with Ctrl+C.
2. Right Click the Sheet Tab at the bottom of the worksheet.
3. Click View Code
4. Paste the macro into the module with Ctrl+V.
5. Save the macro using Ctrl+S

 

[sashaz]

Thank you Leith. Tried for an hour now to make it work and cannot. I am sure it is me, not your code. I don't see any obvious mistakes that I could fix on my side but still nothing is happening once I enter any text in column A on Sheet1.

Anyway, I am happy with the very first response from Jerry with one IF formula. It works and it does what I need. It does not open the pptx file for me but I can live with that. In reality I have 32 categories to choose from which was the most frustrating task after performing 4 clicks to get there before. I was scrolling through files manually and sometimes was making mistakes by selecting a wrong file for the hyperlink. Now I don't spend any time at all, the link is ready for me as soon as I enter my category name and it opens with one click. I am a very happy camper.

Thank all of you for your time and knowledge again.