#1
|
|||
|
|||
Importing a file to a sheet with a hyperlink?
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? |
#2
|
|||
|
|||
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
|
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
Did you put the file location & file name in as per above.......
|
#8
|
|||
|
|||
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.
|
#9
|
|||
|
|||
You miught have missed off the last /
adjusted the code slightly, now try |
#10
|
|||
|
|||
Quote:
I might need to simply learn more before advancing in this topic. |
#11
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
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 |