sbzellison
New Member
- Joined
- Mar 28, 2014
- Messages
- 1
Hello future friends!
I need help to automate a tedious task. I have 480 excel files produced by a gas exchange analyzer (measures photosynthesis) that I need to alter slightly and summarize in a master workbook. Though I have some limited programming experience (R), I have never used VBA or macros. I'd be happy to learn! But so far, my searching to solve this problem has mired me in a sea of T.M.I.
I'm using Excel 2013 on Windows 7, but the 480 output files I'm working with are in Excel 1997.
Here's my problem (at least the one you guys can help me with!). Workbook "drk_sat_macro" is my master file, soon to be filled with useful data. It has two sheets: drk and sat. Both have two header rows, then 240 rows with an entry for "site", "sample", and "leaf area", then a bunch of unfilled values. I need to perform the following steps to get the summary workbook I need to:
1) Copy the value in row three, "leaf area"
2) Open an Excel 1997 workbook
3) Copy the value into a cell, and populate the two cells below it with the same value
4) Average 52 rows
5) Copy them, close the workbook and save as .xlsx
6) Paste their values next to "leaf area" in the master workbook in the drk sheet.
7) In the sat sheet, copy the value in row three, "leaf area"
9) Open a different Excel 1997 workbook, and repeat steps 3-7
Rinse and repeat 239 more times!
I recorded a macro to go through one iteration of the process, but I imagine it's riddled with errors and it obviously doesn't work when I try it on the next row, I imagine because it's not referencing the next file in the directory (and I get "Run-time error '9': Subscript out of range). The code is:
Sub drk_sat()
'
' drk_sat Macro
'
'
Range("C3").Select
Selection.Copy
Range("K10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K10:K12"), Type:=xlFillDefault
Range("K10:K12").Select
Range("E13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C:R[-1]C)"
Range("E13").Select
Selection.AutoFill Destination:=Range("E13:BD13"), Type:=xlFillDefault
Range("E13:BD13").Select
Selection.Copy
Windows("drk_sat_macro.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("sat").Select
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Range("K10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K10:K12"), Type:=xlFillDefault
Range("K10:K12").Select
Range("E13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C:R[-1]C)"
Range("E13").Select
Selection.AutoFill Destination:=Range("E13:BD13"), Type:=xlFillDefault
Range("E13:BD13").Select
Selection.Copy
Windows("drk_sat_macro.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("diet 6.3.13 1h drk_.xls").Activate
ChDir "C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13\diet 6.3.13 1h drk_.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Windows("diet 6.3.13 1h sat_.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13\diet 6.3.13 1h sat_.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("H9").Select
End Sub
Some sample data from drk_sat (columns 1:8 of 52, rows 1:6 of 242), worksheet drk (though sat looks the same):
<tbody>
</tbody>
Some sample data (columns 1:16 of 56, rows 8:12 of 12) from the file I want to use to populate row 3 of the above (the value from cell C3 above goes into K10:K12 here):
<tbody>
</tbody>
A potential complicator: the 480 data files do not all contain 3 rows of data. Almost all do, but some contain 4-6 rows. Ideally, I'd like the macro to not work on these files, but leave them alone and I'll treat them separately.
I'm happy to sink a lot of time into solving this, as I'd rather learn than do it by hand this year and next. Also, I'll be responsive, but likely moreso on Monday. And, if there's a way to upload some example files, I would be happy to do so if that would help.
I really appreciate ANY tips you smart people might have on streamlining this process for me. I would be happy to automate any step; even if it's not all wrapped up nicely in one macro, that would be fine. Or, any articles that might help me get started would be great. I need beginner-level stuff; this and this seem close, but I'm not yet familiar enough with macros/VBA to use this information.
Thanks so much!
I need help to automate a tedious task. I have 480 excel files produced by a gas exchange analyzer (measures photosynthesis) that I need to alter slightly and summarize in a master workbook. Though I have some limited programming experience (R), I have never used VBA or macros. I'd be happy to learn! But so far, my searching to solve this problem has mired me in a sea of T.M.I.
I'm using Excel 2013 on Windows 7, but the 480 output files I'm working with are in Excel 1997.
Here's my problem (at least the one you guys can help me with!). Workbook "drk_sat_macro" is my master file, soon to be filled with useful data. It has two sheets: drk and sat. Both have two header rows, then 240 rows with an entry for "site", "sample", and "leaf area", then a bunch of unfilled values. I need to perform the following steps to get the summary workbook I need to:
1) Copy the value in row three, "leaf area"
2) Open an Excel 1997 workbook
3) Copy the value into a cell, and populate the two cells below it with the same value
4) Average 52 rows
5) Copy them, close the workbook and save as .xlsx
6) Paste their values next to "leaf area" in the master workbook in the drk sheet.
7) In the sat sheet, copy the value in row three, "leaf area"
9) Open a different Excel 1997 workbook, and repeat steps 3-7
Rinse and repeat 239 more times!
I recorded a macro to go through one iteration of the process, but I imagine it's riddled with errors and it obviously doesn't work when I try it on the next row, I imagine because it's not referencing the next file in the directory (and I get "Run-time error '9': Subscript out of range). The code is:
Sub drk_sat()
'
' drk_sat Macro
'
'
Range("C3").Select
Selection.Copy
Range("K10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K10:K12"), Type:=xlFillDefault
Range("K10:K12").Select
Range("E13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C:R[-1]C)"
Range("E13").Select
Selection.AutoFill Destination:=Range("E13:BD13"), Type:=xlFillDefault
Range("E13:BD13").Select
Selection.Copy
Windows("drk_sat_macro.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("sat").Select
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Range("K10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K10:K12"), Type:=xlFillDefault
Range("K10:K12").Select
Range("E13").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-3]C:R[-1]C)"
Range("E13").Select
Selection.AutoFill Destination:=Range("E13:BD13"), Type:=xlFillDefault
Range("E13:BD13").Select
Selection.Copy
Windows("drk_sat_macro.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("diet 6.3.13 1h drk_.xls").Activate
ChDir "C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13\diet 6.3.13 1h drk_.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Windows("diet 6.3.13 1h sat_.xls").Activate
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sbellison\Desktop\Brooks 2013\6400 Data\Diet\6.3.13\diet 6.3.13 1h sat_.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("H9").Select
End Sub
Some sample data from drk_sat (columns 1:8 of 52, rows 1:6 of 242), worksheet drk (though sat looks the same):
| Photo | Cond | Ci | Trmmol | VpdL | CTleaf | Area | BLC_1 | |||
| site | sample | Leaf_Area_cm2 | out | out | out | out | out | out | in | out |
| d | 1_1 | 4.794542 | ||||||||
| d | 1_2 | 2.585801 | ||||||||
| d | 1_3 | 3.296194 | ||||||||
| d | 1_4 | 3.52709 |
<tbody>
</tbody>
Some sample data (columns 1:16 of 56, rows 8:12 of 12) from the file I want to use to populate row 3 of the above (the value from cell C3 above goes into K10:K12 here):
| Obs | HHMMSS | FTime | EBal? | Photo | Cond | Ci | Trmmol | VpdL | CTleaf | Area | BLC_1 | StmRat | BLCond | Tair | Tleaf |
| in | in | in | in | out | out | out | out | out | out | in | out | in | out | in | in |
| 1 | 11:30:24 | 140 | 1 | -1.62816 | 0.028201 | 461.5517 | 0.330628 | 1.066036 | 19.81934 | 6 | 5 | 0.5 | 9 | 19.79897 | 19.87685 |
| 2 | 11:30:29 | 145 | 1 | -1.75137 | 0.02786 | 469.7655 | 0.326433 | 1.065317 | 19.82107 | 6 | 5 | 0.5 | 9 | 19.80022 | 19.87786 |
| 3 | 11:30:32 | 148.5 | 1 | -1.68388 | 0.028 | 465.3521 | 0.327612 | 1.063837 | 19.81711 | 6 | 5 | 0.5 | 9 | 19.80084 | 19.87397 |
<tbody>
</tbody>
A potential complicator: the 480 data files do not all contain 3 rows of data. Almost all do, but some contain 4-6 rows. Ideally, I'd like the macro to not work on these files, but leave them alone and I'll treat them separately.
I'm happy to sink a lot of time into solving this, as I'd rather learn than do it by hand this year and next. Also, I'll be responsive, but likely moreso on Monday. And, if there's a way to upload some example files, I would be happy to do so if that would help.
I really appreciate ANY tips you smart people might have on streamlining this process for me. I would be happy to automate any step; even if it's not all wrapped up nicely in one macro, that would be fine. Or, any articles that might help me get started would be great. I need beginner-level stuff; this and this seem close, but I'm not yet familiar enough with macros/VBA to use this information.
Thanks so much!
