Can I automatically reference the same cell in 30 diff files

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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Have you looked into the indirect formula?

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

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212

ADVERTISEMENT

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
 

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212

ADVERTISEMENT

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

Correct
 

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
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

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
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
 

Forum statistics

Threads
1,147,508
Messages
5,741,571
Members
423,668
Latest member
Audorin

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