Labradar Macro.. put data on one page

[JCCinOhio]

I got tired of having to open 10+ files to get the data when doing load development, so wrote this macro.. Puts Avg, SD, ES, etc from all folders onto one page.

Edit: See post #4 below first, got the file to save as a zip file.

Of course it won't let me upload an Excel spreadsheet.. so if you want to use it you'll have to figure it out.

- Google 'excel developer tab' and get the tab to appear in your tabs.. (up there with 'Home', 'Insert', etc)
- click on the developer tab
- click on 'visual basic'
- click 'insert' (up by file, edit, etc), and click 'module'
- copy/paste the whole module below from 'Sub.." to "End Sub"
- save as Macro enabled spreadsheet
- you can close the visual basic editor, hit the x..
- see the template file attached on what data will go where on Sheet 1.. make yours the same.. save it..
- when you open it in the future you will have to enable macros.

- copy the whole LBR folder from your sd card onto the root C drive... so it will be C:\LBR\...

- in Cell F3, put the number of series' that are in the folder
- back on the developer tab, hit 'Macros', and then 'Run'
- Delete old data before running again.. or save as something else.
- good luck.. hope you enjoy..

- Macro starts below..
********************************

Sub collect_data()


Application.DisplayAlerts = False
Application.ScreenUpdating = False

the_path = "c:\lbr\"

For i = 1 To Cells(3, 6)

a = Len(i)
If a = 1 Then zz = "000" & i
If a = 2 Then zz = "00" & i
If a = 3 Then zz = "0" & i
If a = 4 Then zz = i

Dim y As Workbook
Set y = Workbooks.Open(the_path & "sr" & zz & "/SR" & zz & " Report.csv")

da_series = Cells(3, 2)
da_avg = Cells(11, 2)
da_sd = Cells(15, 2)
da_es = Cells(14, 2)
da_min = Cells(13, 2)
da_max = Cells(12, 2)
da_shotnum = Cells(4, 2)

y.Close True

ActiveSheet.Cells(7 + i, 1) = da_series
ActiveSheet.Cells(7 + i, 2) = da_avg
ActiveSheet.Cells(7 + i, 3) = da_sd
ActiveSheet.Cells(7 + i, 4) = da_es
ActiveSheet.Cells(7 + i, 5) = da_min
ActiveSheet.Cells(7 + i, 6) = da_max
ActiveSheet.Cells(7 + i, 7) = da_shotnum

Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[alfack]

Nice work! That is kind of a pain.

 

[_Windrider_]

Nice job. Macros make life so easy. You may be able to post a zip file with the template in it.

 

[JCCinOhio]

Here's the macro spreadsheet in a zip file. Looks like it loaded ok, so to use it:

- extract the file..
- when you open the spreadsheet there should be a security warning. Click 'Option', and 'Enable this content'
- If there is no developer tab you'll still need to load it as above; load the developer tab, and go to it..
- save the LBR folder to c-drive as above
- put the number of series' in the c:\LBR\ folder into the space as above
- click on 'Macros', and Run

 

[Raptorman2014]

Cool, thanks

 

[CanonSterVA]

Great...
Thank you for taking the time to solve a small problem.
Myron