VBA to allow directory from cell

[JumboCactuar]

Sorry if title is confusing, i have this vba script which works fine

It loads up a sheet from another workbook.

Sub LoadSheet()

Dim FileName As String
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim ActiveListWB As Workbook


ChDrive "C:"
ChDir "C:\users\jay\desktop"


    Set WS2 = ActiveWorkbook.Sheets("Data")
    FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                               Title:="Select Timesheet to Import", _
                                               MultiSelect:=False)
    
    If FileName = "False" Then
            Exit Sub
        Else
            Set ActiveListWB = Workbooks.Open(FileName)
    End If
    
    Set WS1 = ActiveListWB.Sheets("NewData")
    
    WS1.UsedRange.Copy WS2.Range("A1")
    
    ActiveWorkbook.Close False


End Sub

i want to change this part

ChDrive "C:"
ChDir "C:\users\jay\desktop"


so i can change drive and directory from a cell

ive tried using:
drive1 = Range("B3")
directory1 = Range("B4")

and

ChDrive " & drive1 & "
ChDir " & directory1 & "

but its giving error

any help appreciated

 

[Norie]

Re: Help modifying this vba to allow directory from cell

No need for " &.

ChDrive drive1
ChDir directory1

 

[Fluff]

Re: Help modifying this vba to allow directory from cell

Try

ChDrive Range("A20").Value
ChDir Range("A21").Value

Ensuring that the drive letter end with a :
& the path ends with a \

 

[JumboCactuar]

Re: Help modifying this vba to allow directory from cell

both work fine

thankyou

 

[Fluff]

Re: Help modifying this vba to allow directory from cell

Glad WE could help & thanks for the feedback