vba GetOpenFileName - can I amend macro to address FilePath from named cell in active workbook?

[JohnSawers]

Hi all.

I have a macro (see code below) that copies a worksheet (Sheet1) from a closed workbook into Sheet1 of the active workbook. So far, so good but I need the path to the closed workbook to be taken from a cell in the active workbook which is populated by a drop-down combo box. I have named cells for all the respective parts of the path such as:


Named Cell = Description Example
Path = Drive & Directory 'C:\Documents\
FileName = File name & extension [FILE.XLSM]
SheetName = Sheet name Sheet1'!
SheetRoute = Path up to sheet name 'C:\Documents\[FILE.XLSM]Sheet1'!

<colgroup><col width="142"><col width="333"></colgroup><tbody>
</tbody>

If anyone has any suggestions on how to address the named cell path (eg to "SheetRoute"), then this would be a great help.


CURRENT MACRO CODE

Sub test()

Dim FileFilter As String
Dim FileName As String
Dim SrcWkb As Workbook
FileFilter = "All Excel Files,*.xla;*.xls;*.xls*;*.xlt,All Files, *.*"
FileName = Application.GetOpenFilename(FileFilter)
If FileName = "False" Then Exit Sub
Set SrcWkb = Workbooks.Open(FileName)
SrcWkb.Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("A1")
SrcWkb.Close False

End Sub

 

[njimack]

Try the following line of code before opening the workbook:

ChDir Range("Path")

 

[JohnSawers]

Thanks for the quick response. Amending to macro to include this line of code however gets me an error message:

Run-time error '1001':
Method 'Range' of object_'Global' failed

 

[njimack]

Thanks for the quick response. Amending to macro to include this line of code however gets me an error message:

Run-time error '1001':
Method 'Range' of object_'Global' failed

Strange. Do you definitely have a range called 'Path'?

Maybe do it in 2 stages:
Dim Path As String
Path = Range("Path")
ChDir Path

 

[JohnSawers]

Again, another error message

Run-time error '76':
Path not found

In case it was a definitional issue on the named cell "Path", I re-defined "Path" to include the FileName and then also up to and including the Sheet name (to SheetRoute, ie 'c:\Documents\[FILE.XLSM]Sheet1'!), but got the same error message.


AMENDED CODE
Sub test()

Dim FileFilter As String
Dim FileName As String
Dim Path As String
Dim SrcWkb As Workbook
Path = Range("Path")
ChDir Path
FileFilter = "All Excel Files,*.xla;*.xls;*.xls*;*.xlt,All Files, *.*"
FileName = Application.GetOpenFilename(FileFilter)
If FileName = "False" Then Exit Sub
Set SrcWkb = Workbooks.Open(FileName)
SrcWkb.Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("A1")
SrcWkb.Close False

End Sub