• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Open specified Excel file from specified path by using VBA

Hi,

I want to open a specific excel file (name of the file is mentioned in the cell) saved in the specified path (the path is mentioned in the cell).

I mean, when I click on VBA button, then the specified saved in the specified path should open.

In attached excel;
Cell A2: Path
Cell B2: Excel File Name

And the most important, the excel file should be open as Read-only mode.

Once again need your help guys...

Thanks in advance.


 

Attachments

  • open excel file from path.xlsx
    8.7 KB · Views: 18
Last edited:
Code:
Sub Open_File()

Dim fPath As String, fName As String

fPath = Worksheets("Sheet1").Range("A2").Text
If Right(fPath, 1) <> "\" Then fPath = fPath + "\"
fName = Worksheets("Sheet1").Range("B2").Text

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=fPath + fName, ReadOnly:=True)

End Sub

The reason to use wb is that you can now reference that by using wb.

This code doesn't check or assume a file extension and so if that is important ie: Book1.xlsx is not the same files as Book1.xlsm, you should check the file extension
 
Dear Hui,

Good to get the reply from an excel Ninja like you.

But unfortunately, I am getting the below error ;
Actually, the mentioned path is correct. And the file is also available on the correct path.

upload_2019-3-18_15-53-56.png
 
On more thing sir,

I get the below code from another expert in this forum only;
I have to mention the path in "O2" cell while using below code.

Option Explicit

Sub OpenFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim tf As String
Dim fld As Object
Dim fln As Variant
Dim mainwbk As Workbook
Dim mainws As Worksheet
Set mainwbk = ThisWorkbook
Set mainws = mainwbk.Worksheets("Data")

Dim f As New Scripting.FileSystemObject
tf = mainws.Range("O2")
Set fld = f.GetFolder(tf)
Set fln = f.GetFolder(tf).Files

Dim c As Variant
Dim myfln As String

For Each c In fln
Application.Workbooks.Open c
myfln = ActiveWorkbook.Name

Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


As I am not very good in VBA, so I am really not able to understand the above codes.

The above code is provided by an expert of this forum only, but by using above VBA we can open all the excel files of the path. But I want only specific excel files only.

Thanks in advance sir...
 
Dear Hui,

Thanks once again.

Actully, We have the Network Shared Drive, hence before the Z://Shared/Data... , I have to mention the network mapping address as well.

The correct path should be like ; file\\\//171.98.221.90/LCA/Shared/Data...
 
Back
Top