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>
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,716
Office Version
365
Platform
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
40,716
Office Version
365
Platform
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
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
40,716
Office Version
365
Platform
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
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
40,716
Office Version
365
Platform
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
40,716
Office Version
365
Platform
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,099,119
Messages
5,466,796
Members
406,499
Latest member
COACHTEEJ

This Week's Hot Topics

Top