How to specify the current directory as path in VBA?

Question

I have a macro-enabled WorkBook. I need to specify the current folder in which the macro-enabled file is present as the path. I tried setting

path = ActiveWorkbook.Path

and

path = CurDir()

but neither of these work for me. Any idea on this?

ActiveWorkbook.Path is the right way to get the path. How and where are you calling this code? — Siddharth Rout
– Siddharth Rout, Commented Apr 18, 2012 at 18:32
Activeworkbook.path doens't exist until document is saved once does it? — xQbert
– xQbert, Commented Apr 18, 2012 at 18:32
If it's the file with the running macro then you want ThisWorkbook.Path — Tim Williams
– Tim Williams, Commented Apr 18, 2012 at 18:56
+1 Tim Williams. Yup :) hence it is important to know how and where is he calling this code from? — Siddharth Rout
– Siddharth Rout, Commented Apr 18, 2012 at 18:59
@Siddharth - I was going on "the current folder in which the macro enabled file is present" — Tim Williams
– Tim Williams, Commented Apr 18, 2012 at 19:02

Tim Williams · Accepted Answer · 2012-04-18 19:04:12Z

32

If the path you want is the one to the workbook running the macro, and that workbook has been saved, then

ThisWorkbook.Path

is what you would use.

answered Apr 18, 2012 at 19:04

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Siddharth Rout · Accepted Answer · 2012-04-18 20:14:10Z

7

I thought I had misunderstood but I was right. In this scenario, it will be ActiveWorkbook.Path

But the main issue was not here. The problem was with these 2 lines of code

strFile = Dir(strPath & "*.csv")

Which should have written as

strFile = Dir(strPath & "\*.csv")

and

With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _

Which should have written as

With .QueryTables.Add(Connection:="TEXT;" & strPath & "\" & strFile, _

answered Apr 18, 2012 at 20:14

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges