Read fr Open File > if not open > open file > and read cells

[cliffwms]

I have this macro that is supposed to read from an open file, if the file is not open, then open it then read date fr ythe cells....my main problen is with the "flow":

Dim wb As Workbook
BookName = "I:\S3 DATA\MEPSFLR\FLR CDR\BDE_Reports\" & FileName & ""
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then
BookCheck = 1: GoTo AlreadyOpen
Else: BookCheck = 2:  GoTo OpenTheBook
End If
Next i
'----------------------------------------------------------------------
 
OpenTheBook:
    Set wb = Workbooks.Open("I:\S3 DATA\MEPSFLR\FLR CDR\BDE_Reports\" & FileName & "", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets("REPORT")
        ' read data from the source workbook
        .Range("F73").Formula = wb.Worksheets("MORNING REPORT").Range("B2").Formula
        .Range("L73").Formula = wb.Worksheets("MORNING REPORT").Range("L2").Formula
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
GoTo Done:
 
'----------------------------------------------------------------------
AlreadyOpen:
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "='[" & FileName & "]MORNING REPORT'!R73C12"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "='[" & FileName & "]MORNING REPORT'!R73C6"
    Application.ScreenUpdating = True ' turn ON the screen updating
    Range("C6").Select

oh yea, the OpenTheBook:
and the AlreadyOpen routines work
basically it is line 6 and 7 that I can't seem to figure out
so that it goes to and / or by passes the requisite routine(s)

any help would be greatly appreciated.
AND I thank all of those who have helped me in the past.

cliff

[GTO]

Greetings Cliff,

I did not get past this:
[vba]
BookName = "I:\S3 DATA\MEPSFLR\FLR CDR\BDE_Reports\" & FileName & ""
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then
[/vba]

I may be mis-reading, but it would appear to me that 'Bookname' will end up being a string respresenting the fullname of a workbook, but with a quotation mark tacked in at the end.

The next issue would seem to be that that comparing the fullname (Path + wb name) with 'Workbooks(i).Name' would fail, as .Name only returns the filename (without the path).

Mark

[cliffwms]

Hi GTO, what's up?

you are right
didn't catch that
probably why it doesn't flow
but the file/book name changes every month
that is why the:
BookName = "I:\S3 DATA\MEPSFLR\FLR CDR\BDE_Reports\" & FileName & ""
i have some code above it that selects the correct wb/filename for the corresponding date

any idea what I need to do to fix it.....and still be able to use a "revolving" file name

cliff

[GTO]

...i have some code above it that selects the correct wb/filename for the corresponding date

any idea what I need to do to fix it.....and still be able to use a "revolving" file name...

A fairly blind stab, but given that your current code provides the correct filename, check to see if the wb exists (is open) first, and if not, set a reference to it on opening...

Option Explicit
    
Sub Calling()
Dim RetVal As Variant
    
    If GetInfo(ThisWorkbook.Path, "Book2.xls", RetVal) Then
        MsgBox RetVal
    Else
        MsgBox "Failed"
    End If
    
End Sub
    
Function GetInfo(Path As String, WBName As String, RetVal) As Boolean
Dim wb As Workbook
Dim bolClosed As Boolean
    
    '//attempt to set a reference                           //
    On Error Resume Next
    Set wb = Workbooks(WBName)
    On Error GoTo 0
    
    '// If is Nothing, then not open                        //
    If wb Is Nothing Then
        
        '// Tack in trailing seperator                      //
        If Not Right(Path, 1) = "\" Then Path = Path & "\"
        
        '// attempt open                                    //
        On Error Resume Next
        Set wb = Workbooks.Open(Path & WBName)
        On Error GoTo 0
        
        '// if not Nothing, then opened. Else bailout       //
        If Not wb Is Nothing Then
            GetInfo = True
        Else
            Exit Function
        End If
        
        '// If wb was Nothing originally, flag, so we can   //
        '// close.                                          //
        bolClosed = True
    End If
    
    '// Do something here.  I just retuned a val.           //
    RetVal = wb.Sheets(1).Range("A1").Value
    
    '// If closed initially, re-close                       //
    If bolClosed Then wb.Close False
End Function

Hope that helps,

Mark

[GTO]

Sorry, I ACK'D a bit:

Function GetInfo(Path As String, WBName As String, RetVal) As Boolean
Dim wb As Workbook
Dim bolClosed As Boolean
    
    '//attempt to set a reference                           //
    On Error Resume Next
    Set wb = Workbooks(WBName)
    On Error GoTo 0
    
    '// If is Nothing, then not open                        //
    If wb Is Nothing Then
        
        '// Tack in trailing seperator                      //
        If Not Right(Path, 1) = "\" Then Path = Path & "\"
        
        '// attempt open                                    //
        On Error Resume Next
        Set wb = Workbooks.Open(Path & WBName)
        On Error GoTo 0
        
        '// if not Nothing, then opened. Else bailout       //
        If Not wb Is Nothing Then
            GetInfo = True
        Else
            Exit Function
        End If
        
        '// If wb was Nothing originally, flag, so we can   //
        '// close.                                          //
        bolClosed = True
    Else
        GetInfo = True
    End If
    
    '// Do something here.  I just retuned a val.           //
    RetVal = wb.Sheets(1).Range("A1").Value
    
    '// If closed initially, re-close                       //
    If bolClosed Then wb.Close False
End Function