Can I automatically reference the same cell in 30 diff files

M

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
I have two somewhat related questions.

Q1. I download data into an Excel spreadsheet from an outside data provider every market trading day. The spreadsheet never changes but the name of the file changes everyday (new date = new name). At the end of the month, I need to string together daily figures into one monthly figure as part of a return calculation. Currently, I either cut and paste manually or link to one of the daily files and manually cange the name of the file in the link for all the other dates. Is there someway to write a formula or VBA code that will automatically reference the same cell but in 30 different Excel spreadsheet (maybe someway to select a group of files and ask Excel to get the value the same cell in all these files).

Q2. I have a single Excel spreadsheet that has a bunch of tabs(worksheets) that are identically formated but the data changes everyday and ofcourse each tab has a different name. I also have a summary page that has to show a value in the same cell for each of the tabs in the spredsheet. I currently either cut and paste or link to the cell in one tab and manually change the name of the tab in the link about 25 times. Is there an automatic way to do this. The number of tabs in the spreadsheets fluctuate between 20 and 25 on a monthly basis.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Have you looked into the indirect formula?

As a footnote, external files must be open when referencing them via indirect().
 
Upvote 0
Re: Can I automatically reference the same cell in 30 diff f

CT --

the link that you provided is right on target, my files are identical from day to day except for their names and I am trying to do the same thing as the guy who posted the message was trying to do.

I know you did not write the code provided in your link, but do you know which part of the code deals with new files being created. If for example I create a new file (as I do everyday) in a designated directory will the code automatically add it to its output?
 
Upvote 0
Re: Can I automatically reference the same cell in 30 diff f

malkhazov-

Yes, the code is setup to search the given directory and extract all excel files (those that end in *.xls). Therefore, whenever you add or delete files to that directory the code will pick up the changes.

See in code:

Code: 
'This is the Dir to search in
sDir = "C:AGSMPT"

and

Code: 
strFileLoc = Dir(sDir & "*.xls")
'and the do while loop
Do While Len(strFileLoc) > 0

HTH,
CT
 
Upvote 0
Re: Can I automatically reference the same cell in 30 diff f

the Len function is the one that checks if there is anything in the directory, correct?

so if Len (xxxxxx) >0 that means that there are files in the directory
 
Upvote 0
Re: Can I automatically reference the same cell in 30 diff f

I copied the code that you directed me except with my path name, however I am having a hard time getting it to work.

I think that it has to do with the
Len(strFileLoc) > 0
I re-wrote the formula to see if its finding the files in the directory by doing
If Len(strFileLoc) = 0 then [A1] = "found nothing"
and it keeps showing the "found nothing" message which I take to mean it did not find the files in the directory

I also did
If Len(sDir) > 0 then [A1] = "found your files"
and it looks like that worked and it found the excel files in the directory
I tried running the program with Len(sDir) > 0 instead of Len(strFileLoc) and it have me the following error message;
9
Subscription out of range


Do you have any other input?
 
Upvote 0
Re: Can I automatically reference the same cell in 30 diff f

What exactly are you attempting to do??

The code provided in the link should work fine. Whenever you add or delete files to the specified directories and re-run the macro, the changes should appear (ie either more or less data) in the sheet with the macro.

CT
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Can excel return results that are calculated from separate items in a drop down list
2 3 4
Replies
33
Views
768
Peter_SSs
Peter_SSs
S
  • Question Question
Help with a developer tool to be able to change data in certain cells
Replies
7
Views
196
dreid1011
dreid1011
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
639
Amo840812
A
JCabral
  • Question Question
Working days per worker, per event and month
Replies
2
Views
262
JCabral
JCabral
R
  • Question Question
Automate data pull from one file to another
Replies
1
Views
467
John_w
J

Forum statistics

Threads
1,224,802
Messages
6,181,048
Members
453,014
Latest member
Chris258

Share this page

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
Back
Top