Retrieving Data from another workbook

Hi,

I'm not sure where this should be posted, hope I'm in the right place.

I'm looking for a way to transfer data from a workbook that will be sent to me daily to a central workbook on my machine. The data from the workbook that I receive will be in the same cell(s), and it will be placed in the cell(s) below the data received the previous day. Example: Data from cell B3 will need to go into cell D9 one day, D10 the next day, etc. Can this be accomplished without VBA, or will I need to consider that route? Each workbook received will have a different name.

Thanks in advance to any and all responses!

Darrell

You can do it without VBA, but that means doing it all manually. If you want it automated, you need VBA.

Thanks, Bob!

Looks like hitting the books is in order. I know nothing about VBA. Any hints on where to start?

Thanks again,

Darrell

Got something working here, but it's not automated nor will extract data from a closed workbook. Hopefully another beginner can find something useful.

Darrell

Sub Transfer()
   
    reportname = InputBox("Enter the name of the source report, leave blank to Exit")

    If reportname <> "" Then
        Windows(reportname).Activate
        Sheets("Sheet1").Select
   
        Range("C12").Select
        Selection.Copy
   
        Windows("Dredge FY10 Yardage.xlsm").Activate
        ActiveWindow.WindowState = xlNormal
        ActiveWindow.WindowState = xlNormal
           
        destcell = InputBox("Enter the destination cell, leave blank to Exit")
        If destcell <> "" Then
   
            Range(destcell).Select
            Selection.PasteSpecial Paste:=xlPasteValues
        Else
            MsgBox ("You didn't enter a cell! I'm gone!")
        Exit Sub
        End If
    Else
    MsgBox ("You didn't enter a report! Bye!")
    Exit Sub
    End If
      
End Sub

Managed to find some code to add to what I had. This extracts data from a closed workbook. Hope it can be of help to someone.

* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Private Function GetYds(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
    GetYds = ExecuteExcel4Macro(arg)
End Function

Sub TestGetYds()
    Dim p As String, f As String
    Dim s As String, a As String
   
    p = ThisWorkbook.path
    f = InputBox("Enter the name of the source report, Leave blank to Exit")
    s = "Sheet1"
    a = "C12"

    If f <> "" Then
 
        MsgBox GetYds(p, f, s, a)
        destcell = InputBox("Enter the destination cell, Leave blank to Exit")

        If destcell <> "" Then

            Windows("Jadwin FY10 Yardage.xlsm").Activate
            ActiveWindow.WindowState = xlNormal
            Range(destcell).Select
            ActiveCell.Value = GetYds(p, f, s, a)

        Else
            MsgBox ("You didn't enter a cell! I'm gone!")
        Exit Sub
        End If
    Else
        MsgBox ("You didn't enter a report! Bye!")
    Exit Sub

    End If
End Sub