Help with using visual basic to create dynamic links to other excel files

[domiereavron]

Hello

I am working on file that has gotten way too large for a project.

Here is a sample version of the project

https://docs.google.com/file/d/0B5ivitjLxD8EZXc0MnBJWS10bVU/edit?usp=sharing


I am hoping to shrink down the file size using VBA. I have excel files for each day this year that contain all stats of each of our workers. The files have been named in a YYYY-MM-DD format. So August 1, 2013 would be 2013-08-01. On my main page, the user can enter their Start Date and how many days they would like to see stats for and enter the associate names on the left side. The report generates the header columns for each day the user is requesting stats. In my other tabs. The column header is created based on the associate names that were entered. Each day of this year is listed down the row in the first column.

In each cell, there is a formula, =IF(AND(B$1<>0, COUNTIF(Main!$4:$4,CALLS!$A10)>0), INDEX('\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\[2013-01-09.xlsx]2013-01-09'!$F:$F, MATCH(B$1,'\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\[2013-01-09.xlsx]2013-01-09'!$A:$A, 0)), "")

Basically it is saying if the date is found on the main page, and the associate is listed on top, do an index match search on this file. The file location is static and is always \\10.20.48.39\dc$\analytics\stats\rams\stats\
and the column it is indexing and matching is always the same.

Does anyone have an idea how to write VBA so that it would just generate the dates for each column header and write the link for each cell on the main tab to grab the data. So if the user entered in January 1st, 2013 for the start date and 10 days, it create the dates based on what was entered for the start date and how many dates as seen below:

1/1/2013</SPAN>

1/2/2013</SPAN>

1/3/2013</SPAN>

1/4/2013</SPAN>

1/5/2013</SPAN>

1/6/2013</SPAN>

1/7/2013</SPAN>

1/8/2013</SPAN>

1/9/2013</SPAN>

1/10/2013</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=10></COLGROUP>

Smith, Robert</SPAN>

Dankins, Jason</SPAN>

Simpson, Homer</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

Beside each name it would create the look up link for the data based on the date for each column.


INDEX('\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\[2013-01-01.xlsx]2013-01-01'!$F:$F, MATCH(A5,'\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\[2013-01-01.xlsx]2013-01-01'!$A:$A, 0)), "")

I dont know if that makes sense, but it would greatly cut down on the size of the file. Since it is currently using a massive lookup table, the file is 12 mb.

Any help is appreciated.

 

[domiereavron]

So I figured out most of the VBA, I am just getting stuck on the syntax for it to write the formula for the index part. Any suggestions?

Sub Create_Stats()
Dim Days
Dim StartDate
Dim Column
Dim Row

Dim ConvertedDate

StartDate = Sheets(1).Cells(1, 2).Value
Days = Sheets(1).Cells(1, 4).Value

For Column = 0 To Days - 1
Sheets(1).Cells(3, 2 + Column).Value = StartDate + Column
Next

Column = 2
Row = 4

While IsEmpty(Sheets(1).Cells(Row, 1)) = False

For Column = 0 To Days - 1

ConvertedDate = Format(StartDate + Column, "YYYY-MM-DD")

Sheets(1).Cells(Row, 2 + Column).Formula = "=INDEX(" '\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\["& ConvertedDate &".xlsx]"& ConvertedDate &"'"!$F:$F, MATCH(B$1,"'\\10.20.48.39\dc$\Analytics\Stats\RAMS\Stats\["& ConvertedDate &".xlsx]"& ConvertedDate &"'"!$A:$A, 0)), """")"
Next

Row = Row + 1

Wend


End Sub

 

[domiereavron]

Solved this problem with some research and playing around