PDA

View Full Version : Failing to open existing workbook



CatDaddy
08-21-2012, 02:29 PM
I made a little function to check if a workbook is open and I stepped through it and the path and everything is correct and it is throwing no errors but the workbook is still not opening:

Public Function OWB(wb As String)
Dim pathO As String
Dim ExcelApp As Excel.Application
Set ExcelApp = GetObject(, "Excel.Application")
On Error Resume Next
pathO = ThisWorkbook.path & "\" & wb
Set WB_check = Workbooks(wb)
If Err <> 0 Then
On Error GoTo 0
ExcelApp.Workbooks.Open fileName:=pathO
End If
End Function

mancubus
08-21-2012, 02:44 PM
worked for me...


Sub test()

Dim s As String
s = "DataFile.xlsx"

OWB (s)

End Sub

CatDaddy
08-21-2012, 02:57 PM
I am trying to run it from as a worksheet function...is that not going to work?

mancubus
08-21-2012, 02:58 PM
afaik, files cannot be opened via worksheet functions...

CatDaddy
08-21-2012, 03:06 PM
fck me...theres probably no way around it either huh?

mancubus
08-21-2012, 03:23 PM
i dont know.

but, as you may know, you can return "true" or "false" in a cell.


Public Function IsWorkBookOpen(WorkBookName As String)
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=186

Dim WB As Workbook

On Error Resume Next
Set WB = Workbooks(WorkBookName)
On Error Goto 0

IsWorkBookOpen = Not WB Is Nothing

End Function

shrivallabha
08-22-2012, 10:43 AM
Use hyperlinks formula as it will allow you to click on it to open file.

Or

Worksheet_Change event.