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

[meer_ali]

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>

 

[Fluff]

Hi & welcome to the board.
How about

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]

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

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]

How about

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]

Perfect!!!

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]

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]

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]

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]

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]

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.