CODEWITHSUNDEEP
vbaexcel
user387184
user387184

Reputation: 11053

accessing sheets in a different workbook from within a function in vba in Excel

I have two workbooks in Excel 2007, A and B.

Within A I have a function that needs to get certain values from workbook B. I find no way to do this within a function (within a normal sub is no problem, but I need a function - which actually is more complicated than just getting the cell(1,1) )

in A:

function getDataFromB(sheetName,row,col)
   x = Application.run ("E:\B.xlsm!getData",sheetName,row,col)
   getDataFromB = x
end getDataFromB

In B

function getData(sheetName,row,col)
   x=sheets(sheetName).cells(row,col)
   getData = x
end getData

Whenever getData within B is called it looks for sheetName in workbook A - not B. And writing 

x = workbooks("E:\B.xlsm").sheets(sheetName).cells(row,col)

does not work

How would I solve this?

Upvotes: 0

Views: 5957

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

TIRED AND TESTED

Change

function getDataFromB(sheetName,row,col)
x = Application.run("E:\B.xlsm!getData",sheetName,row,col)
getDataFromB = x end getDataFromB

to

Function getDataFromB(sheetName, row, col)
    Dim FilePath As String, FileName As String
    Dim wbTarget As Workbook
    Dim x As Variant
    Dim CloseIt As Boolean

    '~~> Set file name and path here.
    FileName = "B.xlsm"
    FilePath = "E:\"

    On Error Resume Next
    Set wbTarget = Workbooks(FileName)

    If Err.Number <> 0 Then
        '~~> Open the workbook
        Err.Clear
        Set wbTarget = Workbooks.Open(FilePath & "\" & FileName)
        CloseIt = True
    End If

    '~~> Check and make sure workbook was opened
    If Err.Number = 1004 Then
        MsgBox "File does not exist!"
        Exit Function
    End If

    On Error GoTo 0

    x = Application.Run(wbTarget.Name & "!getData", sheetName, row, col)

    getDataFromB = x

    If CloseIt = True Then
        '~~> If the target workbook was opened by the macro, close it
        wbTarget.Close savechanges:=False
    Else
        '~~> If the target workbook was already open, reactivate this workbook
        ThisWorkbook.Activate
    End If
End Function

Also in File B Change the code to

Function getData(sheetName,row,col)
   x=sheets(sheetName).cells(row,col)
   getData = x
End Function

You need to add "End Function" as I have done above.

Upvotes: 1

Related Questions