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

 

[bucci35]

On the Set wkb...line

 

[Norie]

And the error message is?

 

[bucci35]

'C:\Documents and Settings\Dan\Desktop\01_092306.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

The file that I have currently open that I'm trying to access is 05_092306.

 

[Norie]

Does a file called 01_092306.xls exist in that directory?

If it doesn't you'll get the error because that's the file the code is looking for.

It isn't looking for a file called 05_092306.

 

[bucci35]

No thier isn't a file named that right now, but when all stores are done their will be. Right now I"m looking for just store #5. Eventually thier will be 15 stores that I'm looking for with the same weekending date which will change every week

 

[Norie]

Well your best option would be to put all the files you currently have in one folder.

Then you can have code that only looks at the files that are in that folder.

You can do this using Application.FileSearch, Dir or using the File System Object.

Try a board search, that should yield plenty of examples as this is a common request.

 

[sbendbuckeye]

put this after strName = yourpath, etc and see if it is correct:

Debug.Print strName

What Excel version and OS version are you using? Seems like one of the older ones required double quotes around embedded spaces in paths. Try manually copying a couple files down to the C:\ path and see if that makes a difference. Good Luck!

 

[Norie]

sbendbuckeye

As far as I can see the file 01_092306.xls doesn't actually exist yet.

 

[bucci35]

I can put all the files in a folder on my desktop labeled "labor", but I"m really not good enough to go any further than this. Could you possible rigth the macro for me? I hate to be a pain, but this is out of my reach as far as my ability in excel.

Thanks

 

[sbendbuckeye]

Hello bucci35,

For testing purposes

A. Create a test folder
B. Copy old versions of all 15 store/date files into the test folder or else copy one version 14 times and then rename them appropriately
C. Change the macro code above to use the test folder from A
D. Run the code and let us know if it works

Good Luck!