Getting value from closed workbook

[sn281]

Hi there,

I have this code which extracts the values I want from the spreadsheet I want, however there is only one problem - hopefully an easy fix.

This extracts the data from rows 5 to 28 and columns 26 to 36, but inputs these values into my sheet in the same place.
I would like the macro to be able to do what it is doing, but then instead of getting values and putting them in from 5,26 I want it to start pasting into cell B4 (or 4,2).

Is there an easy way to do this?

Thanks in advance,
sn281

Private Function GetValue(path, file, sheet, ref)

    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



Sub GetValue2()

    Application.Calculation = xlCalculationManual

    p = "O:\Power\PowerPnL"
    f = "PowerPositions-v6.0.xlsm"
    s = "Last Prices"
    Application.ScreenUpdating = False
    For r = 5 To 28
        For c = 26 To 36
            a = Cells(r, c).Address
            Cells(r, c) = GetValue(p, f, s, a)
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

 

[Domenic]

Try replacing...

Cells(r, c) = GetValue(p, f, s, a)

with

Range("B4").Offset(r - 5, c - 26).Value = GetValue(p, f, s, a)

Hope this helps!

 

[sn281]

This is absolutely perfect!!!! Thank you so much.

Out of interest and so I can learn, what exactly have you changed there? You select the range B4, but then what does the offset do? I would've have thought if you select B4 and then offset it would almost 'double move it' if that makes any sense!?

Thanks so much for your help though, much appreciated.

 

[Domenic]

Offset returns a range that is offset from the specified range. In this case, the specified range is B4, which is used as a starting point. The row offsets are determined by r - 5 (ie. 5-5=0, 6-5=1, 7-5=2, etc), and the column offsets are determined by c - 26 (ie. 26-26=0, 27-26=1, 28-26=2, etc).

So you can easily place the results in another location by changing the starting point. For example, to return the results starting at D5, you only need to replace...

Range("B4")...

with

Range("D5")...

 

[sn281]

Ah okay, of course, that's the reference point, then the offset moves it along from there.

I assumed because it had been told the amount of rows and columns it would know this already.

Thanks a lot for your help. Works a treat

 

[Domenic]

You're very welcome! Glad I could help!