PDA

View Full Version : Sub Returning Workbook



clew
06-24-2010, 11:38 AM
Hello. I'm trying to use a sub to open up a workbook. The code I'm working on is:

Dim wb As Workbook
Set wb = WorkbookOpen("C:\File.xls")

Private Function WorkbookOpen(Path As Variant) As Workbook

On Error GoTo EndofSub

Dim wb As Workbook
Set wb = Workbooks.Open(Path, True, True)
WorkbookOpen = wb

EndofSub:


End Function

The reason I open the workbook inside a sub is to isolate an error that occurs if the selected file is already open. If you click "No" to re-opening the file, the Open method fails and raises an error.

I'm not sure why the code above doesn't work. It either does not return a workbook object, or it does not set the variable wb as that workbook.

Any help would be appreciated. Thanks!

Bob Phillips
06-24-2010, 11:56 AM
Dim wb As Workbook
Set wb = WorkbookOpen("C:\File.xls")

Private Function WorkbookOpen(Path As String) As Workbook

On Error Goto EndofSub

Dim wb As Workbook
Set wb = Workbooks.Open(Path, True, True)
Set WorkbookOpen = wb

EndofSub:


End Function

mdmackillop
06-24-2010, 11:56 AM
Sub Test()
Dim wb As Workbook
Dim Path As String
Dim WBook As String
WBook = "File.xls"
Path = "C:\"
On Error Resume Next
Set wb = Workbooks(WBook)
On Error GoTo 0
If wb Is Nothing Then Set wb = Workbooks.Open(Path & WBook)
End Sub