Solved: Function - Lookup sub for file path

[NewDaddy]

Hi All

I am attempting the art of using functions. I am trying to build a function that returns the filename and path of a file I wish to open.

Basically I want to lookup a file name reference (OpenFile1, OpenFile2 etc) to obtain its Workbook Name and Path to return to open file routine.

I have created my own custom menu/toolbars for my team which apart from report creating etc has a list of files we use. At the moment this file is in our excel start-up folder and hidden on opening. I aim to change this to an addin in the future.

As file name(s)/location(s) change I have created a template file with the sub name and its corresponding file name & path to open from where I can easily change them as needed.

I can, of course, do this by writing code within each open file sub but thought there could be a way in utilising the power of custom functions but am not sure how to tackle this. Is there a way of using the Sub Name (ie OpenFile1) as the reference to lookup?

I have attached my ?template? lookup file list. But basically column A is the ?Open File No? while column B has the File Path and Name.

Here is what I have so far.

[VBA]
Function WorkbookOpen(book) As Boolean
On Error GoTo NotOpen
x = Workbooks(book.xls).name
WorkbookOpen = True
Exit Function
NotOpen:
WorkbookOpen = False
End Function

Private Function GetWBookFPath() As String
'BookToTest = ActiveWorkbook.name
'TestSheet = ActiveSheet.name
Workbooks.Open FileName:="S:\Central Finance\Admin\Template Files\File List.xls"
FileList = ActiveWorkbook.name
FileListSht = ActiveSheet.name
OpenFilePath = Application.WorksheetFunction.VLookup("OpenFile1", Workbooks(FileList).Worksheets(FileListSht).Range("A1:B65000"), _
2, False)
'Workbooks(BookToTest).Activate
'Worksheets(TestSheet).Activate
End Function

Sub OpenFile1()
Dim FName As String
?GetWBookPathName (OpenFilePath)
OpenFile = GetWBookFPath(OpenFilePath)
If Not WorkbookOpen(OpenFile) Then
Application.DisplayAlerts = False
On Error GoTo ErrorHandler_1
Workbooks.Open FileName:=FName
Application.DisplayAlerts = True
End If
Exit Sub
ErrorHandler_1:
MsgBox Err.Number & Err.Description
End Sub

Sub OpenFile2()
?same as above
End sub

etc

[/VBA]

Any help would be greatly appreciated.

Cheers

[Simon Lloyd]

Try this http://www.excelguru.ca/node/48 it's exactly what you need written by Ken Puls (an administrator here).

Regards,
Simon

[NewDaddy]

Try this http://www.excelguru.ca/node/48 it's exactly what you need written by Ken Puls (an administrator here).

Regards,
Simon

Hi Simon

Thanks, I will have a look and post back.
Thanks for the quick response.

Cheers