Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-22-2020, 11:46 PM
SoMany SoMany is offline Importing a file to a sheet with a hyperlink? Windows 7 64bit Importing a file to a sheet with a hyperlink? Office 2016
Advanced Beginner
Importing a file to a sheet with a hyperlink?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 12-23-2020, 07:37 AM
Purfleet Purfleet is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 12-24-2020, 12:54 PM
SoMany SoMany is offline Importing a file to a sheet with a hyperlink? Windows 7 64bit Importing a file to a sheet with a hyperlink? Office 2016
Advanced Beginner
Importing a file to a sheet with a hyperlink?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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.
Attached Files
File Type: xlsx Example.xlsx (12.3 KB, 7 views)
File Type: xlsx Sentence.xlsx (8.6 KB, 7 views)
Reply With Quote
  #4  
Old 12-24-2020, 03:07 PM
Logit Logit is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #5  
Old 12-25-2020, 12:16 PM
Purfleet Purfleet is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsm Example_Purfleet.xlsm (20.2 KB, 6 views)
Reply With Quote
  #6  
Old 12-26-2020, 10:07 PM
SoMany SoMany is offline Importing a file to a sheet with a hyperlink? Windows 7 64bit Importing a file to a sheet with a hyperlink? Office 2016
Advanced Beginner
Importing a file to a sheet with a hyperlink?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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".
Reply With Quote
  #7  
Old 12-26-2020, 11:10 PM
Purfleet Purfleet is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by Purfleet View Post
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.......
Reply With Quote
  #8  
Old 12-26-2020, 11:29 PM
SoMany SoMany is offline Importing a file to a sheet with a hyperlink? Windows 7 64bit Importing a file to a sheet with a hyperlink? Office 2016
Advanced Beginner
Importing a file to a sheet with a hyperlink?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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.
Reply With Quote
  #9  
Old 12-26-2020, 11:48 PM
Purfleet Purfleet is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

You miught have missed off the last /

adjusted the code slightly, now try
Attached Files
File Type: xlsm Example_Purfleet2.xlsm (20.2 KB, 5 views)
Reply With Quote
  #10  
Old 12-27-2020, 02:01 AM
SoMany SoMany is offline Importing a file to a sheet with a hyperlink? Windows 7 64bit Importing a file to a sheet with a hyperlink? Office 2016
Advanced Beginner
Importing a file to a sheet with a hyperlink?
 
Join Date: Oct 2016
Posts: 51
SoMany is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
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.
Attached Files
File Type: xlsx MsForums1.xlsx (82.8 KB, 5 views)
File Type: xlsx MsForums2.xlsx (8.7 KB, 5 views)
Reply With Quote
  #11  
Old 12-27-2020, 03:38 AM
Purfleet Purfleet is offline Importing a file to a sheet with a hyperlink? Windows 10 Importing a file to a sheet with a hyperlink? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing a file to a sheet with a hyperlink? 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
Importing a file to a sheet with a hyperlink? 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 file to a sheet with a hyperlink? Importing a PST file Swoosh983 Outlook 1 08-20-2010 01:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft