Can you lookup a directory name stored in a sheet and use it to ChDir ?

[kevanr]

Hi
I have saved a directory location in a cell in a worksheet. In my macro I want to retrieve the directory and change to that directory. I have used the following simple code:

Sub TestingChDir()

Dim sDirectory As String
sDirectory = Worksheets("Assumptions").Range("J17")

MsgBox "sDirectory " & sDirectory

ChDir sDirectory

End Sub

I get the following error 76:



Can someone please help me with this?

Many thanks

 

[DanteAmor]

You can put here the data of cell J17.
And an image of your explorer showing that the directory does exist.

Try this

Sub TestingChDir()
  Dim sDirectory As String
  sDirectory = Worksheets("Assumptions").Range("J17")
  
  If Dir(sDirectory, vbDirectory) = "" Then
    MsgBox "Does not exists"
  Else
    ChDir sDirectory
  End If

End Sub

 

[kevanr]

If Dir(sDirectory, vbDirectory) = "" Then MsgBox "Does not exists" Else ChDir sDirectory End If

I tried this thanks but now get run time error 52 Bad filename or number

 

[DanteAmor]

You can put here the data of cell J17.
And an image of your explorer showing that the directory does exist.

Please provide that information.

 

[kevanr]

You can put here the data of cell J17.
And an image of your explorer showing that the directory does exist.

Please provide that information.

 

[DanteAmor]

In cell J17 you must put the data without quotes:

C:\TestChDir Foler

 

[DanteAmor]

If you are going to open the file in cell J8, it is not necessary to change the directory, it can be like this:

Sub TestingChDir()
  Dim sDirectory As String, sFile As String
  sDirectory = Worksheets("Assumptions").Range("J17").Value
  If Right(sDirectory, 1) <> "\" Then sDirectory = sDirectory & "\"
  sFile = Worksheets("Assumptions").Range("J18").Value
  If Dir(sDirectory & sFile) = "" Then
    MsgBox "Does not exists"
  Else
    Workbooks.Open (sDirectory & sFile)
  End If

End Sub

 

[kevanr]

If you are going to open the file in cell J8, it is not necessary to change the directory, it can be like this:

Sub TestingChDir()
  Dim sDirectory As String, sFile As String
  sDirectory = Worksheets("Assumptions").Range("J17").Value
  If Right(sDirectory, 1) <> "\" Then sDirectory = sDirectory & "\"
  sFile = Worksheets("Assumptions").Range("J18").Value
  If Dir(sDirectory & sFile) = "" Then
    MsgBox "Does not exists"
  Else
    Workbooks.Open (sDirectory & sFile)
  End If

End Sub

Thank you DanteAmor, that works perfectly, your help has been greatly appreciated !!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.