Accessing multiple excel workbooks/sheets

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello

1. Put your date in a cell, say A2, in sheet1.
2. Run this macro
Code:
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

Active Member
Joined
Jul 6, 2002
Messages
319
HI,

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

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
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.
Code:
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

Active Member
Joined
Jul 6, 2002
Messages
319

ADVERTISEMENT

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

Active Member
Joined
Jul 6, 2002
Messages
319
Clarification,

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Specifically have you changed YourWorkBookPath here?
Code:
strName = "YourWorkBookPath\" & Format(lngStore, "00") & "_" & strDate & ".xls"
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
What's the error?
 

Forum statistics

Threads
1,136,878
Messages
5,678,297
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top