Extracting data from various excel reports using VBA

[stretfordender]

Hi,

I'm trying to pull data from various excel spreadsheets into one summary report in a grid.

Each spreadsheet is held within a folder, named as a date such as 2008-10-14, 2008-10-21 etc. I have so managed to write some VBA so that when a date is selected at the top of my summary report, the date of the corresponding folder is inserted into a link which points to one report within the folder and pulls back one piece of data.

Here is my code so far:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim firstpart As String, secondpart As String, solutions_design_overall_status As String
firstpart = "'http://sharepointsite.net/Meetings/"
secondpart = "/[Vauxhall Report.xls]Worksheet1'!D11"
If Not Intersect(Target, Range("C6")) Is Nothing Then
Application.EnableEvents = False
    'Cells.Hyperlinks.Add anchor:=Range("V11"), Address:=firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart, TextToDisplay:=firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    vauxhall_overall_status = "=" & firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    Range("B2").Value = vauxhall_overall_status
Application.EnableEvents = True
End If
End Sub

At the moment all this VBA code is doing is taking the date from the summary report, creating a link which includes the correspending date for the folder to look in, and pulls the 'overall status' data from the Vauxhall report.

How do I write a loop so that once a date is selected from the summary report, it looks within the corresponding folder on the sharepoint site, as it is doing at the moment, and then pulls data from the Vauxhall report, Ford report, Fiat report etc. for each of the headings along the top of the grid (overall status, teamwork, communication, training)????

Many Thanks,
Alex

[MartinLiss]

I moved your thread to here.

[westconn1]

instead of hard coding secondpart you would need to get the worbook names from a range in a worksheet or a text file, or just hardcode into an array, then loop through the range or array to get the values for secondpart, and moving the target to an appropriate location, possibly using target.offset(rows,0)

[stretfordender]

would it be possible for you (or anyone who thinks they could help) to post a bit of pseudo code, to help me understand how to use the array and also target.offset(rows,0)??

thanks,
alex

[westconn1]

first you need to decide how to store the values, so a method to fill the array can be determined, then to loop through the array

for i = 0 to ubound(myarray)
   secondpart = myarray(i)
      ' you can change the variable name from specific (vauxhall) to a generic name [I]overall_status[/I], but it makes no difference
      vauxhall_overall_status = "=" & firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    Range("B2").offset(i,0).Value = vauxhall_overall_status
next

i didn't test this at all, the off set will make each row down the value of i, so the first will be on row 2+0, next row 2 +1, next row 2+2 etc

the array needs to contain a list of all the file names you want to process
you can hardcode an array like
myarray = Array("a", "S", "d", "r")
or read the content of a textfile or other methods, that can be changed without editing the vba code
or you can loop through all the files in a given folder

hth

[stretfordender]

westconn,

thanks for taking the time out to reply to my post.

As a relatively newbie to VBA, could you offer me more help and insight.

How will the array loop through to for example....vauxhall_teamwork, vauxhall_communication, ford_teamwork, ford_communication with vauxhall.xls and ford.xls being seperate excel files?

cheers,
alex

[westconn1]

are teamwork and communication sheets in each of your workbooks?

[stretfordender]

westconn1,

ive attached example workbooks for vauxhall and the summary report.

As you can see, I'm trying to pull the row of data from vauxhall into the summary report grid, and similarly for all the other reports Ford, Fiat etc.

hope this helps you to understand the problem.

thanks,
alex

[westconn1]

i an not sure of the results you want,
or the source of vauxhall_teamwork, vauxhall_communication
as i did not see them as sheets or named ranges

[stretfordender]

from the attachments, I'm trying to pull vauxhall_overall_status (Cell D11 in Report to PMT from Vauxhall) to Cell D19 in the summary report, and so on and so on.

[westconn1]

ok, i look next time