Test for already Opened Excel Spreadsheet Fails 2

[DianeA]

I have an Access Form which passes a value to an excel spreadsheet. I want to test to ensure that the spreadsheet is not already open prior to me giving the OPEN cmd. If it is OPEN then proceed to select the sheet and move data in.

I have two issues but lets start with this one....

1. If I start with the excel file open and then perform this macro the Function ISOPEN does not perform "For Each wb in Excel.Workbooks" and instead goes directly to IsOpen=False and hence proceeds to open a "read-only" copy of the spreadsheet again.

See code below

____________________________________________
Function IsOpen(FILE As String) As Boolean
Dim wb As Excel.Workbook
For Each wb In Excel.Workbooks
If wb.Name = FILE Then
IsOpen = True
GoTo exitSub
End If
Next wb
IsOpen = False
exitSub:
End Function

______________________________________________

Private Sub ToExcel_Click()
Dim app As New Excel.Application
Dim sht As Excel.Worksheet
Dim wbk As Excel.Workbook

Set app = CreateObject("Excel.Application")

With app

If IsOpen(FILE) Then
GoTo skip
End If

.Visible = True
Set wbk = app.Workbooks.Open(PATH & FILE)
skip:
Set sht = .Worksheets("calcs")
sht.Select
.Range("P20").Select
.Range("P20") = TTL

End With
Set app = Nothing
Set wbk = Nothing

EndSub

 

[firefytr]

No need to loop through the workbooks to test for an open file...

Function WbOpen(wbName As String) As Boolean
'Originally found by Jake Marx, modified by Zack Barresse
    On Error Resume Next
    WbOpen = Len(Excel.Application.Workbooks(wbName).Name)
End Function

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci