Summing values from 2 different wrkbooks using GetValue

[yasmine26]

hey im trying to write a macro that would enable the user to do the following:

1-the user selects the first workbook from the computer files
2-opens it
3-perhaps the macro would save the name as a variable (lets say pushdata)
4-perform functions such as adding titles on pushdata (i have that part worked out)
5-the user selects the second workbook
6-once again the macro can save is as another variable lets say rawdata
7-then define a cell in pushdata in terms of cells in raw data

Ps. the pushdata and rawdata should be standard. and the cells selected to be substracted on raw data will always be the same

I have figured out a way using GetValue that works fine when the sheet in the sec workbook is named "sheet1" but would like to defined the sheet in terms of the workbook name instead...i dont know if im clear but here is what i have so far:

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String

'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If

'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)

'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)

End Function

Private Function FileNameOnly(pname) As String
    FileNameOnly = Dir(pname)
End Function
Private Function WorkbookIsOpen(wbname) As Boolean
    Dim z As Object
    On Error Resume Next
    Set z = Workbooks(wbname)
    If Err = 0 Then WorkbookIsOpen = True _
        Else WorkbookIsOpen = False
End Function
Sub clean3()
    Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FilePathPrimary As Variant
    Dim FilePathSecondary1 As Variant
    Dim FileNamePrimary As String
    Dim FileNameSecondary1 As String
    Dim FileNameSecondary As String
    Dim PrimaryWorkbook As Workbook
    Dim Value1 As Long
    Dim Value2 As Long
    Dim ExcessCount As Long
    
    Filt = "All Files (*.*),*.*"
    
    FilterIndex = 1
    
    Title = "Select the primary workbook"
    
    FilePathPrimary = Application.GetOpenFilename _
        (FileFilter:=Filt, _
        FilterIndex:=FilterIndex, _
        Title:=Title)
        
    If FilePathPrimary = False Then
        MsgBox "Cancelled"
        Exit Sub
    Else
        FileNamePrimary = FileNameOnly(FilePathPrimary)
    End If
    
    Title = "Select the secondary workbook"
    
    FilePathSecondary1 = Application.GetOpenFilename _
        (FileFilter:=Filt, _
        FilterIndex:=FilterIndex, _
        Title:=Title)
        
    If FilePathSecondary1 = False Then
        MsgBox "Cancelled"
        Exit Sub
    Else
        FileNameSecondary1 = FileNameOnly(FilePathSecondary1)
    End If
    
    If WorkbookIsOpen(FileNamePrimary) Or WorkbookIsOpen(FileNameSecondary1) Then
        Beep
        MsgBox "One or more of these files are already open!  Close out all open files and try again.", _
        vbCritical = vbOKOnly, "File is already open!"
        Exit Sub
    End If
    
    Workbooks.Open FilePathPrimary, UpdateLinks:=False
    
    Set PrimaryWorkbook = ActiveWorkbook
      
    FilePathSecondary1 = WorksheetFunction.Substitute(FilePathSecondary1, FileNameSecondary1, "")
    
    FileNameSecondary = Left(FileNameSecondary1, Len(FileNameSecondary1) - 4)
    
    If Len(FileNameSecondary) > 31 Then
        ExcessCount = Len(FileNameSecondary) - 31
        FileNameSecondary = Left(FileNameSecondary, Len(FileNameSecondary) - ExcessCount)
    End If

    
    p = FilePathSecondary1
    f = FileNameSecondary1
    s = FileNameSecondary
    A = "B1"
    Value1 = GetValue(p, f, s, A)
    
    p = FilePathSecondary1
    f = FileNameSecondary1
    s = FileNameSecondary
    A = "A1"
    Value2 = GetValue(p, f, s, A)
    
    PrimaryWorkbook.Sheets(1).Cells(2, 27).Value = Value1 - Value2

I hope that helps and thanks for your help!

 

[yasmine26]

actually i figured it out
thanks!