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]

Hello

1. Put your date in a cell, say A2, in sheet1.
2. Run this macro

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\" & lngStore & "_" & strDate
        Set wkb = Workbooks.Open(strName, ReadOnly:=True)
        MsgBox wkb.Name & " " & wkb.Range("A1").Value
        wkb.Close False
    Next lngStore
    Set wkb = nothing
End Sub 'PullData

Good Luck!

 

[bucci35]

HI,

When I do as you say it bugs out on the Set wkb...line. Any ideas?

 

[sbendbuckeye]

Hello,

Modify the code as below. I forgot that the index needs to be formatted and the extension needs to be added. Sorry for messing you up.

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"
        Set wkb = Workbooks.Open(strName, ReadOnly:=True) 
        MsgBox wkb.Name & " " & wkb.Sheets("Sheet1").Range("A1").Value 
        wkb.Close False 
    Next lngStore
    Set wkb = nothing 
End Sub 'PullData

Good Luck!

 

[bucci35]

HI,

I still bug out at the same point. I"ve tried multiple formatting ways for the date in cell a2 thinking this was it, but to no avail. What should the format be? Where does the 5_ come into play for the store numbers? Could this be the issue?
Thanks,

 

[bucci35]

Clarification,

I have the date formatted to display 092306 (mmddyy).

 

[Norie]

Have you changed the code to reflect the actual path where the workbooks are?

Specifically have you changed YourWorkBookPath here?

strName = "YourWorkBookPath\" & Format(lngStore, "00") & "_" & strDate & ".xls"

 

[bucci35]

I did not, but I just entered the following below and I still get the same bug out. Did I enter it correctly?


strName = "C:\Documents and Settings\Dan\Desktop\" & Format(lngStore, "00") & "_" & strDate & ".xls"

 

[Norie]

What's the error?

 

[bucci35]

Run time error 1004