CODEWITHSUNDEEP
vbaexcel
maniA
maniA

Reputation: 1457

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

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!

Upvotes: 0

Views: 4989

Answers (3)

Nesteres
Nesteres

Reputation: 11

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

Upvotes: 1

blabb
blabb

Reputation: 9007

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

enter image description here

Upvotes: 1

Martin Dreher
Martin Dreher

Reputation: 1564

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.

Upvotes: 3

Related Questions