Read a path to a folder from a Excel-Workbook cell using vba

Question

I have the following VBA-code

Dim directory As String, fileName As String
directory = "C:\User\Work\scorix\test_excel\"
fileName = Dir(directory & "*.xl??")

now I would like to change the code so that I would be able to read the path from a given Excel-Workbook cell and then build the fileName. If I try

directory = Worksheets("Summary").Range("B2").Value
fileName = Dir(directory & "*.xl??")

it dose not work. It means at the end of day directory is empty and therefore fileName is empty. In the next step I tried

  With ThisWorkbook
    directory = Sheets("Summary").Range("B2").Value
  End With

it works! (But, why?, probably I did not understand the definition of With) However, in the next step

fileName = Dir(directory & "*.xl??")

filename is still empty. I tried everything with ActiveSheet however, without success!

I don't see nay thing wrong with above codes, i verified locally as well. Are you sure of having excel files in directory, you are referring? Does your B2 cell have directory path mentioned correctly? please check it — Siva
– Siva, Commented Aug 22, 2016 at 11:51
Your code works as expected for me as well. The problem must be elsewhere. — Miqi180
– Miqi180, Commented Aug 22, 2016 at 11:55
Check if there is an \ at the end of directory, if not add it. fileName = Dir(directory & "\*.xl??") — Vincent G
– Vincent G, Commented Aug 22, 2016 at 12:09

Martin Dreher · Accepted Answer · 2016-08-22 11:59:32Z

seems to me those errors occur rather arbitrary, which in my experience can happen when working with several worksheets simultaniously. Maybe replacing

directory = Worksheets("Summary").Range("B2").Value

with

directory = ThisWorkbook.Worksheets("Summary").Range("B2").Value

or alternatively (what is what i prefer to working with a range)

directory = ThisWorkbook.Worksheets("Summary").Cells(2, 2).Value

or alternatively

With ThisWorkbook
    ' notice the dot in Front of "worksheets"
    directory = .Worksheets("Summary").Range("B2").Value
End With

fixes things.

Another situational approach might be to name your Sheet-objects in the VBA-Editor (edit the (Name) property in the property window).

Hope that helps.

P.S. Since you use the Dir()-Function, I trust you know that in order to get the 2nd+ File, you have to call it repeatedly (maybe in a loop) without supplying a directory.

blabb · Accepted Answer · 2016-08-22 12:09:52Z

1

dir returns the first file in the path\pattern to recurse you need to do DIR("") pass an empty string

answered Aug 22, 2016 at 12:09

blabb

9,1121 gold badge20 silver badges29 bronze badges

Comments

Nesteres · Accepted Answer · 2016-08-22 12:39:18Z

1

directory = Worksheets("Summary").Range("B2").Value
fileName = Dir(directory & "*.xl??")

there is nothing wrong with this code u might be writing the name of the worksheet wrong maybe?

 With ThisWorkbook
   directory = Sheets("Summary").Range("B2").Value
 End With

Don't forget about using "." before "sheets" when you are using with statements

fileName = Dir(directory & "*.xl??")

The main reason this code didn't work is propably because there are more than one files that ends with "*.xl??" in that folder

edited Aug 22, 2016 at 12:39

answered Aug 22, 2016 at 12:32

Nesteres

112 bronze badges

1 Comment

Martin Dreher Over a year ago

Dir(path) will always return the first file that matches the pattern. So, while it might not yield the result desired by the OP, it will return a string, if there are one or more files matching the search pattern

Collectives™ on Stack Overflow

Read a path to a folder from a Excel-Workbook cell using vba

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related