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
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