Importing a file to a sheet with a hyperlink?

SoMany · #1 12-22-2020, 11:46 PM

I can create a hyperlink to import a file, but how do you get the hyperlink button to import the file to a different sheet?

Purfleet · #2 12-23-2020, 07:37 AM

To my knowledge a hyperlink opens a file, thats it. I wasnt aware it can import - please provide an example of what you trying to do

SoMany · #3 12-24-2020, 12:54 PM

Quote:

Originally Posted by Purfleet

To my knowledge a hyperlink opens a file, thats it. I wasnt aware it can import - please provide an example of what you trying to do

Maybe I used the wrong words. I want the easy button to copy the "Sentence" sheet into the Data sheet using an "Easy Button" in the "Hyperlink" sheet. Currently, the "Sentence" sheet is copying into the "Hyperlink" sheet. And the word copy might be the wrong word I'm using here.

Logit · #4 12-24-2020, 03:07 PM

Testing the two workbooks you just posted, what I see is ...

The hyperlink button is not "copying" anything. All it is doing is taking
the user from the EXAMPLE workbook to the SENTENCE workbook.
That is what hyperlinks do ... take the user from one location to
another.

You should Google " VBA Copy Range Other Workbook "

What you are seeking is a macro to copy data between workbooks.

Purfleet · #5 12-25-2020, 12:16 PM

A bit basic, but this should do what you want.

The file location needs to be in cell B1 and the file name in B2

Code:

Sub ImportFile()

    Dim fLocation As String
    Dim fName As String
    Dim CurrentfName As String
    
    CurrentfName = ActiveWorkbook.Name
    fLocation = ActiveSheet.Range("B1")
    fName = ActiveSheet.Range("b2")
    
    If Len(Dir(fLocation & fName, vbDirectory)) = 0 Then
        MsgBox Title:="Error", Prompt:="Cant find file"
        Exit Sub
    End If
    
    If Right(fLocation, 1) <> "/" Then
        fLocation = fLocation & "/"
    End If
    
    Workbooks.Open (fLocation & fName)
    
    ActiveSheet.Copy Workbooks(CurrentfName).Sheets(1)
    
    Workbooks(fName).Close False

End Sub

SoMany · #6 12-26-2020, 10:07 PM

Quote:

Originally Posted by Purfleet

A bit basic, but this should do what you want.

The file location needs to be in cell B1 and the file name in B2

Code:

Sub ImportFile()

    Dim fLocation As String
    Dim fName As String
    Dim CurrentfName As String
    
    CurrentfName = ActiveWorkbook.Name
    fLocation = ActiveSheet.Range("B1")
    fName = ActiveSheet.Range("b2")
    
    If Len(Dir(fLocation & fName, vbDirectory)) = 0 Then
        MsgBox Title:="Error", Prompt:="Cant find file"
        Exit Sub
    End If
    
    If Right(fLocation, 1) <> "/" Then
        fLocation = fLocation & "/"
    End If
    
    Workbooks.Open (fLocation & fName)
    
    ActiveSheet.Copy Workbooks(CurrentfName).Sheets(1)
    
    Workbooks(fName).Close False

End Sub

I got an error message saying "Cant find file".

Purfleet · #7 12-26-2020, 11:10 PM

Quote:

Originally Posted by Purfleet

A bit basic, but this should do what you want.

The file location needs to be in cell B1 and the file name in B2

Did you put the file location & file name in as per above.......

SoMany · #8 12-26-2020, 11:29 PM

Quote:

Originally Posted by Purfleet

Did you put the file location & file name in as per above.......

Yes. Specifically, I copied the address bar at the top of the folder C:\Users\John Smith\Desktop\1 Work in Progress\Job\Excel\New folder into B1, then typed Sentece.xlsx into B2.

Purfleet · #9 12-26-2020, 11:48 PM

You miught have missed off the last /

adjusted the code slightly, now try

SoMany · #10 12-27-2020, 02:01 AM

Quote:

Originally Posted by Purfleet

You miught have missed off the last /

adjusted the code slightly, now try

It works now, but my understanding of VBA is too low. I'm currently going through tutorials to get a better understanding of this so I can apply it at work. Specifically, I tried to recreate the macro in a new file and folder and got the same error message. Below are the two new files.

I might need to simply learn more before advancing in this topic.

Purfleet · #11 12-27-2020, 03:38 AM

The first thing you need to take into account is that a Macro cant be in a .xlsx workbook. Trying saving the one with the macro as a .xlsm (macro enabled workbook), you also need to the code into a Module

Have a read of some of the blogs on here https://www.myonlinetraininghub.com/category/excel-vba

This one shows you how to record a macro to start with https://www.myonlinetraininghub.com/...macro-in-excel

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hyperlink to open another sheet in same workbook with filtered data	tarunbaweja	Excel Programming	1	03-20-2016 06:52 AM
Hyperlink Format varies, depending on whether Target file was saved or unsaved on Hyperlink Copy	RichardDavey	Word	0	05-26-2015 05:26 PM
An error occurred while importing this file <image file>	melvinjn	Drawing and Graphics	1	01-19-2015 01:32 AM
How to set a hyperlink from a pp presentation into a CELL from an excel sheet	Sabi	PowerPoint	1	04-30-2013 06:36 AM
Importing a PST file	Swoosh983	Outlook	1	08-20-2010 01:31 AM