Open File Without Calling Filepath

Question

I'm using excel VBA. I want to press a button that opens another file directly without the effect of "choosing file window".

This is the current code:

Sub loadFile_click()
Workbooks.Open("C:\Users\GIL\Desktop\ObsReportExcelWorkbook.xlsx")
End Sub

In this case, the file is in the same folder as the main file.

Is there any way to do it without entering the file's path?

score 33 · Accepted Answer · answered May 04 '11 at 18:31

33

If the file name is fixed you can use the ActiveWorkbook.Path function to return the path of the current workbook:

Dim FileName as string FileName = ActiveWorkbook.Path & "\myfile.xlsx

Check if you need that extra slash character.

answered May 04 '11 at 18:31

Simon

6,062
13
60
97

score 12 · Answer 2 · answered May 04 '11 at 18:36

12

If the file is in the same folder as the document containing your VBA macro, use

ThisWorkbook.Path

for example:

Workbooks.Open(ThisWorkbook.Path & "\ObsReportExcelWorkbook.xlsx")

(this works even if the document is not the active one any more, or you changed the current directory).

answered May 04 '11 at 18:36

Doc Brown

19,739
7
52
88

How can i open the folder location instead of the Workbook? – DeerSpotter Aug 21 '15 at 16:15
1

@MaximTeleguz: define what you mean by "open folder location", or post a question here on SO with a clear description of what you have in mind. – Doc Brown Aug 21 '15 at 16:42
1

@MaximTeleguz: you seem not to understand how SO works. This is not a discussion forum. If you just post answers to a question which was not asked by the OP, your answer will be downvoted and deleted. If you have a question, ask it **separately**. If you want to answer you own question, that's perfectly ok. Please make yourself familiar with the site rules. – Doc Brown Aug 21 '15 at 17:04

score 2 · Answer 3 · answered May 04 '11 at 18:30

2

If it is in the same folder, then

Workbooks.Open("ObsReportExcelWorkbook.xlsx")

or

Workbooks.Open(".\ObsReportExcelWorkbook.xlsx")

should work. I just tried both in Excel VBA, with success.

answered May 04 '11 at 18:30

rajah9

11,645
5
44
57

1

I tried both too. I get Error 400. It seems excel takes as "current path" the same as the last file saved. To solve this, @Doc Brown solution's works to me – cesargastonec Jun 12 '18 at 16:34

score 0 · Answer 4 · answered Aug 13 '19 at 06:32

0

Workbooks.Open ("D:\data\Mohit Singh\msk\data\book1.xlsx")

answered Aug 13 '19 at 06:32

Mohit Singh

1

DeerSpotter · Answer 5 · 2015-08-24T14:30:08.613

Open Folder of ActiveWorkBook

Sub OpenFolderofProject()
Dim strMsg As String, strTitle As String
Dim sFolder As String

sFolder = ThisWorkbook.Path

    strMsg = "Open WorkBook Folder Location? "
    strTitle = "Folder Location:" & sFolder

        If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
        Else
           Call Shell("explorer.exe " & sFolder, vbNormalFocus)
        End If
End Sub

Context: Usually your revisions on your project consistently Change, or you have a automaticially generated Workbook with a dynamic name. Whatever the Case. This will know your workbook path location and ask if you want to open that specific folder.

This was very useful for me when i dynamically saved out a bunch of Excels programmatically in the same folder of the workbook. Because instead of closing/minimizing the workbook to go to explorer. I could focus on the project and not lose train of thought.

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. — Tunaki, Aug 21 '15 at 17:42
Hey, could you show me how to post code in the comment section so it would be readable. Also i was led to this page from that specifc question on google. More people will be led here just like i was. To save time they will see my response. — DeerSpotter, Aug 21 '15 at 17:58

Open File Without Calling Filepath

5 Answers5

Linked