NewDaddy
04-20-2007, 01:40 AM
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.
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
Any help would be greatly appreciated.
Cheers
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.
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
Any help would be greatly appreciated.
Cheers