Linking Sheet data - help please

[ppbj23]

Hello, i'm just getting back into Excel and VBA. this may be a quicky for someone.

I extract data from various spreadsheets that I do not own, in to my own. In one report I need to extract the information in cell C8, everyday they create a new report which is just called the date 20110526.xls, then next one is 20110527.xls.
I need to create a macro to check the spreadsheet is available and then going into the spreadsheet and get the information in C8 and put in a cell in my sheet that corrosponds to the date.

Hope this makes sense.

cheers
Phill

 

[Misca]

Try:

Sub GetValue()
Dim SourceWB As Workbook
Dim ThisSheet As Worksheet
Dim c As Range
Dim Rng As Range
Dim FD As String
Dim MyFileName As String
Dim FileFound As Boolean
Dim ErrorMessage As String
Application.ScreenUpdating = False
On Error GoTo Err
FD = Format(Date - 1, "yyyymmdd")
MyFileName = "C:\MyFolder\" & FD & ".xls"   'Change to match your folder!
Set ThisSheet = ActiveSheet
Set SourceWB = Workbooks.Open(MyFileName)

If Not SourceWB Is Nothing Then
    FileFound = True
    Set Rng = ThisSheet.Cells.Find(What:=FD, _
                        After:=Cells(1, 1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
    
       ' If Not Rng Is Nothing Then 'If this line is active, you'll never get the second error message.
            Rng.Offset(1, 0).Value = SourceWB.Sheets(1).Range("C8").Value
       ' End If
    SourceWB.Close SaveChanges:=False
End If
Exit Sub
Err:
    If FileFound = False Then
        ErrorMessage = "The file " & FD & ".xls was not found."
    Else
        ErrorMessage = "The file was found but date was not found."
        SourceWB.Close SaveChanges:=False
    End If
    
    MsgBox (ErrorMessage)
End Sub

 

[ppbj23]

awsome stuff man