Accessing multiple excel workbooks/sheets

[bucci35]

Hi,

I have 15 workbooks that are updated by 15 stores. Weekly I pull them back to my desktop via remote access.
I need to access a few cells within each workbook/sheet and pull them to a blank workbook/sheet/cell.
Each week the names changes on all 15 by the weekending date and always preceded by the store number ex. 05_091606 with an underscore between the store number and the date.
I was wondering if their was a way to create a macro or something in a blank workbook that would access all of these automatically to make life easier?
Any ideas?

Thanks,
Dan

 

[sbendbuckeye]

Code modified to use the Dir function which will return an empty string if the file does not exist:

Public Sub PullData()
    Dim wkb As Workbook
    Dim lngStore As Long
    Dim strDate As String
    Dim strName As String
    strDate = Format(Sheets(1).Range("A2").Value, "mmddyy")
    For lngStore = 1 To 15
        strName = "YourWorkBookPath\" & Format(lngStore, "00") & "_" & strDate & ".xls"
        ' Ensure file exists
        If Len(Trim$(Dir(strName))) > 0 Then
            Set wkb = Workbooks.Open(strName, ReadOnly:=True)
            MsgBox wkb.Name & " " & wkb.Sheets("Sheet1").Range("A1").Value
            wkb.Close False
        End If
    Next lngStore
    Set wkb = Nothing
End Sub 'PullData

Good Luck!

 

[bucci35]

Ok,

This seems to work. It goes the cycle and finds and opens them all, but it doesn't paste the values in my new workbook? any ideas

Thanks

 

[bucci35]

Also,

How can I specify the exact sheet and cell I want it to copy and paste to my master workbook. The sheet and cell will always be the same

 

[sbendbuckeye]

Extract your data similarly to the following. Use the workbook object (wkb) to get to the sheets that you want. In the example below, it is retrieving cell C3 from sheet one in the various store files:

Dim strData As String
strData = wkb.Sheets(1).Range("C3").Value

Then create a new workbook to collect the data. Let's assume that you will put it in the first sheet in Column A. So you will need to keep track of which row you are using also.

Dim wks As Worksheet
Set wks = Workbooks.Add.Sheets(1)

Below is the code so far:

Public Sub PullData() 
    Dim wkb As Workbook 
    Dim wksStoreData As Worksheet
    Dim lngStore As Long, lngRow As Long
    Dim strDate As String, strName As String, strData As String

    ' Create consolidated store data sheet
    Set wksStoreData = Workbooks.Add.Sheets(1)
    wksStoreData.Range("A1").Value = "Consolidated Store Data"

    lngRow = 2
    strDate = Format(Sheets(1).Range("C3").Value, "mmddyy") 
    For lngStore = 1 To 15 
        strName = "YourWorkBookPath\" & Format(lngStore, "00") & "_" & strDate & ".xls" 

        ' Ensure file exists 
        If Len(Trim$(Dir(strName))) > 0 Then 

            ' Open file and read cell C3
            Set wkb = Workbooks.Open(strName, ReadOnly:=True) 
            strData = wkb.Sheets("Sheet1").Range("C3").Value 
            wkb.Close False 

            ' Save store data to column A in consolidated store workbook
            wksStoreData.Range("A" & lngRow).Value = strData

            ' Increment the row counter in the consolidated store workbook
            lngRow = lngRow + 1
        End If 
    Next lngStore 
    Set wkb = Nothing 
End Sub 'PullData

The net effect of the above is that you will have a new workbook created which will have the data from individual store sheet1 C3 copied into new workbook sheet1 column A. Good Luck!