Can I automatically reference the same cell in 30 diff files

[malkhazov]

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.

 

[Oaktree]

Have you looked into the indirect formula?

As a footnote, external files must be open when referencing them via indirect().

 

[CT Witter]

Re: Can I automatically reference the same cell in 30 diff f

You can try this for Q1 from post: http://www.mrexcel.com/board2/viewtopic.php?t=29842&highlight by Ivan F Moala.

This assumes that all files are in same directory, have different file names, but the sheet name you are pulling from is the same.

HTH,
CT

 

[malkhazov]

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?

 

[CT Witter]

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:

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

and

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

HTH,
CT

 

[malkhazov]

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

 

[CT Witter]

Re: Can I automatically reference the same cell in 30 diff f

Correct

 

[malkhazov]

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?

 

[CT Witter]

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