To hyperlink &open file from foldername which ismentioned in activecell as value

meer_ali

New Member
Joined
Jan 1, 2018
Messages
12
Hi Friends,

can you pls help me out with the code which can hyperlink the active cell value mentioned in the sheet,and open that folder and its file whichever i will choose to open from it. My code is doing upto 70% of the task, but unable to open the choosing file from it. It throws the error as : " object required."


It is highly appreciated if you can guide me in this regards,

Thanks, and Best regards,


The code is as mentioned below:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Sub opendfiles()
Dim myfile As Variant
Dim Counter As Integer
Dim path As String
MyFolder = "C:\backup" & ActiveCell.Value & ""
ChDir MyFolder
myfile = Application.GetOpenFilename(, , , , True)
Counter = 1
If IsNumeric(myfile) = True Then
MsgBox "No files selected"
End If
While Counter <= UBound(myfile)
path = myfile(Counter)
fso.MyFolder.Open path
Counter = Counter + 1
Wend
End Sub</code>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.
How about
Code:
Sub opendfiles()
   Dim myfile As Variant
   Dim Counter As Integer
   Dim path As String
   Dim myFolder As String
   
   myFolder = "C:\backup\" & ActiveCell.Value & "\"
   ChDir myFolder
   myfile = Application.GetOpenFilename(, , , , True)
   Counter = 1
   If IsNumeric(myfile) = True Then
      MsgBox "No files selected"
      Exit Sub
   End If
   While Counter <= UBound(myfile)
      Workbooks.Open myfile(Counter)
      Counter = Counter + 1
   Wend
End Sub
 

meer_ali

New Member
Joined
Jan 1, 2018
Messages
12
Hi Fluff,

Appreciate your quick reply. :)

The file like ms word or picture gets open into excel file format with jumble characters into it. It doesn't opening the file as it is format.

can you guide me how can i put attachments into this reply...so that i can send u error msg
Thanks.





Hi & welcome to the board.
How about
Code:
Sub opendfiles()
   Dim myfile As Variant
   Dim Counter As Integer
   Dim path As String
   Dim myFolder As String
   
   myFolder = "C:\backup\" & ActiveCell.Value & "\"
   ChDir myFolder
   myfile = Application.GetOpenFilename(, , , , True)
   Counter = 1
   If IsNumeric(myfile) = True Then
      MsgBox "No files selected"
      Exit Sub
   End If
   While Counter <= UBound(myfile)
      Workbooks.Open myfile(Counter)
      Counter = Counter + 1
   Wend
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub opendfiles()
   Dim myfile As Variant
   Dim Counter As Integer
   Dim path As String
   Dim myFolder As String
   Dim wShell As Object

   Set wShell = CreateObject("Shell.Application")
   myFolder = "C:\backup\" & ActiveCell.Value & "\"
   ChDir myFolder
   myfile = Application.GetOpenFilename(, , , , True)
   Counter = 1
   If IsNumeric(myfile) = True Then
      MsgBox "No files selected"
      Exit Sub
   End If
   While Counter <= UBound(myfile)
      wShell.Open myfile(Counter)
      Counter = Counter + 1
   Wend
End Sub
 

meer_ali

New Member
Joined
Jan 1, 2018
Messages
12

ADVERTISEMENT

Perfect!!!:):LOL::biggrin:

My dear friend you are amazing..

Thanks alot for your help in this mrexcel forum. With in minutes I resolved this issue.

This is a very fabulous blog forum.

How do i make this as resolved and put you as FIVE STARS...

Thanks once again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
On this forum we don't marked posts as resolved, as for the five stars, I've no idea
 

meer_ali

New Member
Joined
Jan 1, 2018
Messages
12

ADVERTISEMENT

Hi Fluff,

I'm getting a new error : Run-time error '76' path not found, when i pasted this same code on another sheet and tried. Is there anything that can be done for this type issue. sorry to bother you with this.

Appreciate your help.

Thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
The code should be in a standard module rather than a sheet module.
The problem may be that you had the wrong cell as active when you ran it.
Is there a particular cell that contains the path?
 

meer_ali

New Member
Joined
Jan 1, 2018
Messages
12
Hi Fluff,

I re-checked all the things as you have mentioned- i'm using the code on standard module and i'm on the foldername cell while clicking the macro to open but still it is giving the same 76 error path not found.

Is there any replacement command for change directory (ChDir) ChDir myFolder

because the error is encountering at this point only. Pls guide here.

Thanks alot for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You can delete it if you like, all that will happen is the Dialogue box will open in the current default directory, rather than in the specified directory.
If you are getting that error it means there is something wrong with your file path.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,376
Messages
5,635,895
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top