PDA

View Full Version : Read fr Open File > if not open > open file > and read cells



cliffwms
06-28-2010, 11:21 AM
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
06-28-2010, 11:38 AM
Greetings Cliff,

I did not get past this:

BookName = "I:\S3 DATA\MEPSFLR\FLR CDR\BDE_Reports\" & FileName & ""
BookCount = Workbooks.Count
For i = 1 To BookCount
If BookName = Workbooks(i).Name Then


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
06-28-2010, 12:02 PM
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
06-28-2010, 12:48 PM
...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
06-28-2010, 12:57 PM
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