Capture data from close workbook

[motabrasil]

Dear Sirs,

I need to create a code to capture information from a DATABASE.file (closed Workbook) and Sum or Count, based on a several criterias, in the Dashboard.file (Active Workbook). See described below the initial Code.
How to count the numbers of non blanks rows from the DATABASE.file
column "C" for instance (see in the code highlighted in RED)?
When I run this code, it's capturing the last row from the active workbook.


Sub CaptureInfo()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Dim Results(0 To 1) As Long<o></o>
Dim LastRow As Long<o></o>
Dim I As Integer<o></o>
Dim FilePath$<o></o>
<o></o>
<o></o>
Const Filename$ = "DataBase.xls"<o></o>
Const SheetName$ = "Sheet1"<o></o>
FilePath = ActiveWorkbook.Path & "\"<o></o>
<o></o>
DoEvents<o></o>
Application.ScreenUpdating = False<o></o>
If Dir(FilePath & Filename) = Empty Then<o></o>
MsgBox "The file " & Filename & " was not found", , "File Doesn't Exist"<o></o>
Exit Sub<o></o>
End If<o></o>
<o></o>
Application.Calculation = xlManual<o></o>
<o></o>
LastRow = 1<o></o>
<o></o>
Do While Cells(LastRow, "C") <> ""<o></o>
LastRow = LastRow + 1<o></o>
Loop<o></o>
<o></o>
LastRow = LastRow - 1<o></o>
<o></o>
For I = 1 To LastRow<o></o>
If Cells(I, "F").Value < 0 Then<o></o>
If UCase(Cells(I, "AP").Value = "Vendor1") Then<o></o>
Cells(I, "F").Value = Cells(I, "F").Value<o></o>
Results(0) = Results(0) + Cells(I, "F").Value<o></o>
End If<o></o>
End If<o></o>
If Cells(I, "F").Value < 0 Then<o></o>
If UCase(Cells(I, "AP").Value = "Vendor2") Then<o></o>
Cells(I, "F").Value = Cells(I, "F").Value<o></o>
Results(1) = Results(1) + Cells(I, "F").Value<o></o>
End If<o></o>
End If<o></o>
<o></o>
Next I<o></o>
<o></o>
Sheets("Dashboard").Select<o></o>
<o></o>
Range("E6") = Results(0)<o></o>
Range("E7") = Results(1)<o></o>
<o></o>
<o></o>
Application.Calculation = xlAutomatic<o></o>
Application.ScreenUpdating = True<o></o>
<o></o>
MsgBox "Done!!!"<o></o>
<o></o>
End Sub



Thanks in advance
Mota

 

[motabrasil]

Any help???
I just want to know where I'm doing wrong when I try to find out the last row from the close workbook. See code described.

Thanks

 

[Norie]

Why not just open the closed workbook, get your data and then close it?

There are ways to get data from closed workbooks but they would generally involve reference that workbook somehow.

In the code you've posted I don't really see any reference to it apart from when you check for it's existence.

Am I missing something?

 

[motabrasil]

Hi Norie

Actually, I believe to save time when I keep the workbook closed.
First, I check if the file exist or not and then I need to find out the last row from this (Closed) workbook. However the code is set-up to count the active (open workbook) instead of count from the closed one.
My question is: how to fix that?

That's the part I check the file:

If Dir(FilePath & Filename) = Empty Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
MsgBox "The file " & Filename & " was not found", , "File Doesn't Exist"<o></o>
Exit Sub<o></o>
End If

Thank you

<o></o>

 

[AlphaFrog]

This works but it opens the DataBase file.

Sub CaptureInfo()

    Dim wbData As Workbook
    Dim wsDash As Worksheet
    Dim sFile$
    
    Set wsDash = ActiveWorkbook.Sheets("Dashboard")
    sFile = ActiveWorkbook.Path & "\" & "DataBase.xls"
    
    If Dir(sFile) = Empty Then
        MsgBox "File: " & sFile & vbCr & "...was not found", , "File Doesn't Exist"
        Exit Sub
    Else
        Application.ScreenUpdating = False
        Set wbData = Application.Workbooks.Open(sFile)
    End If
        
    With wbData.Sheets("Sheet1")

        'LastRow = .Cells(Rows.Count, "C").End(xlUp).Row    'Not needed
        wsDash.Range("E6") = Application.SumIf(.Columns("AP"), "Vendor1", .Columns("F"))
        wsDash.Range("E7") = Application.SumIf(.Columns("AP"), "Vendor2", .Columns("F"))
        
    End With
    
    wbData.Close SaveChanges:=False
    'wsDash.Select
    Application.ScreenUpdating = True
    
    MsgBox "Done!!!"
   
End Sub

 

[Norie]

What makes you think it will save time not opening the workbook?

 

[motabrasil]

Hi Alpha,

Your suggestion seems to be good. My doubts is concerning the SUMIF condition. If you see in my previous code we have two conditions to sum. First, the column "AP" must match with the vendor name and the second one is the column "F" must be less than 0. How to fix that?

Thank you

 

[motabrasil]

Hey Norie

On my mind, working with closed workbook would be the same that working in background.

Thank you!

 

[AlphaFrog]

What version of Excel do you have?


If you have Excel 2007, use this...

wsDash.Range("E6") = Application.SumIfs(.Columns("F"), .Columns("F"), "<0", .Columns("AP"), "Vendor1")
wsDash.Range("E7") = Application.SumIfs(.Columns("F"), .Columns("F"), "<0", .Columns("AP"), "Vendor2")

If you have Excel 2003, use this...

        strF = "[Database.xls]Sheet1!$F$1:$F$" & .Cells(Rows.Count, "C").End(xlUp).Row
        strAP = Replace(strF, "$F$1:$F$", "$AP$1:$AP$")
        wsDash.Range("E6") = Evaluate("SUMPRODUCT((" & strF & "<0)*(" & strAP & "= ""Vendor1"") * (" & strF & "))")
        wsDash.Range("E7") = Evaluate("SUMPRODUCT((" & strF & "<0)*(" & strAP & "= ""Vendor2"") * (" & strF & "))")

 

[motabrasil]

Hi Alpha,

Now it's working properly. Thanks for help.