How do I keep track of worksheets and workbooks as they pass between subroutines when .Activation is not working?

Question

Background:

Daily oil analysis reports from the lab come in and are stripped from e-mails into a folder. Saved in a folder by date, each sample dispatch may be between 1 and 20 samples, not every date, but any time of the day. The *.csv files are stored in a dated folder, and the files are saved with their date and time. That part I have deconstructed from the VBA script I inherited.

These files are ignored until the end month (there is a parallel .pdf report that we use for immediate action for the results), when they are rolled up into a monthly / continuous report for longer term trend analysis (each engine sampled only shows up once every two months or so). Problem appears that the lab has changed the .csv report.

The original script took columns that appeared in regular well defined (as in fixed columns). The new report has dynamic columns (as in: if no data for any sample, no column). Worse, they have changed the order of the columns when they do present. The automated roll up report, which used to walk through each dated folder, extract each file, and append it to the master workbook has ceased to have meaning with the garbage data.

The easy solution, I feel, is to rebuild the data in each sample file, by rebuilding and reformatting the columns in a new sheet (searching for the column header, error capture if not present, copy and paste, reformat / do unit conversions etc.), and then copying that new sheet into the master, save and close the file, and repeat with the next one.

The issue I have is that when I pass file names, to the subroutine that opens the new file, every sheet that gets added is in the master file, and the subsequent cut an paste operations are failing (subscripts out of range, or my favorite 400). I am calling every book by the dated name, and every page by the Sheet designation, but the looped repeated activations have me getting lost.

I think I am referencing the sheets incorrectly based on the passed names. There must be an easier way. All other examples seem to have fixed names (if only life were that simple).

Update 1: Forgot to mention that I prototyped the spreadsheet build in one of the files, and then tried to transfer it into here as a sub-routine. Fell apart there. I have cleaned up much of the inherited code and economized the spaghetti, but this is my first attempt at manipulating another worksheet in another workbook from within the original calling one. Again, most examples used fixed names, which are not an option here.

Sub CopyPasteOAP(bookname)

    Dim datarow As Long

    'Grabs index of last data entered to prevent overwrite
    ThisWorkbook.Activate
    Sheet1.Range("B2").Activate
    Range("B2").End(xlDown).Select
    datarow = ActiveCell.Row

    'Restructures the Data Columns into a new worksheet for copying
    Workbooks(bookname).Activate
    Worksheet.Add

    'Date Column
    Sheet1.Range("G1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("A1").Select
        ActiveSheet.Paste
    'Unit Column
    Sheets.Select Sheet1
        Range("B1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("B1").Select
        ActiveSheet.Paste
    'Enter in OAP
    Sheets.Select Sheet2
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "OAP"
        Selection.Copy
        Range("C2:C50").Select
        ActiveSheet.Paste
    'Fault Description = Lab Comments
    Sheets.Select Sheet1
        Cells.Find(What:="Lab Comments", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("D1").Select
        ActiveSheet.Paste
    'Severity Column
    Sheets.Select Sheet1
        Range("H1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("G1").Select
        ActiveSheet.Paste
    'Notes = Lab Recommendations
    Sheets.Select Sheet1
        Cells.Find(What:="Lab Recommendations", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("L1").Select
        ActiveSheet.Paste
    'Fuel in Oil
    Sheets.Select Sheet1
        Range("BL1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("M1").Select
        ActiveSheet.Paste
    'Coolant in Oil
    Sheets.Select Sheet1
        Range("BP1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets.Select Sheet2
        Range("N1").Select
        ActiveSheet.Paste
    Sheets.Select Sheet1

    'Copies from the new sheet
    'Application.Workbooks(bookname).Activate
    Sheets.Select Sheet1
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    'Pastes to the master copy
    Application.ThisWorkbook.Activate
    Sheet1.Range("A" & datarow + 1).Select

    'Paste Special, Match Destination Format
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

    'Adjusts Font (otherwise new is all neon green)
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

    'Clears Clipboard because the prompt is annoying
    Application.CutCopyMode = False

    Application.Workbooks(bookname).Activate
    ActiveWorkbook.Close False

End Sub

Maybe some sample filenames and data to demonstrate the issue. I was following you for a good while there but then got lost. Regading adding code, just edit your question and post the code (and hopefully some DATA) — Hambone, Oct 21 '15 at 15:17
You may want to start by reading this: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros. I can't tell for sure, but it likely has something to do with all the `.Select` and `.Activate` in the code. — Kyle, Oct 21 '15 at 15:40
Are you married to doing in this in Excel/VBA? It seems like you are using Excel as a means to do stuff with the content, but since they are all CSV I, for one, would be more inclined to use other tools that might be better suited to manage these tasks. Can you store the data in a database? Even something like SQLite might yield huge benefits, or you can go to more robust tools like SQL Server Express or MySQL. — Hambone, Oct 21 '15 at 16:35
They are in the same Workbook: Sheet1 is the raw .csv data, Sheet2 is the destination for the amended columns. — Dashboarder, Oct 21 '15 at 16:35
@Hambone, For the time being, this is what we have. The final table is pivotted in many ways for reporting on the parameters, and has been used as the single source of data for many subsequent reports. The maintenance information (pre and post sample) is tracked within as are a number of operational parameters that are manipulated in Excel (charting oil pressures etc). There may indeed be better ways, but these units will be with us for another 10 years, and the data goes back to 2008 in the file. — Dashboarder, Oct 21 '15 at 16:39
Am I not passing the correct information for the file? Insomuch as I can open it with the file name, do I need to pass the path with it in order to address the sheets? — Dashboarder, Oct 21 '15 at 16:41
Dashboarder -- I hear you loud and clear, but it's not an either-or. Once in an RDBMS, the data is easily mined and reported out via MS Query, which is built into Excel, and I think you will marvel at the increased visibility/flexibility/capability you have once in a database. I understand the resistance, but I think if you were to successfully go down this path you would kick yourself for not doing it sooner — Hambone, Oct 21 '15 at 16:42
@Hambone. Thanks for the encouragement, and I agree, and it may be the way to go. But, right now the objective is get the report working again to meet the mandate for monthly reports (behind by a couple of months). Then I can add it to the list of things to do. Sadly, the reality is we are just jumping from the fire to the frying pan and back again, and development time is at a premium. — Dashboarder, Oct 22 '15 at 15:22
@Hambone, I have again been denied access to acquire the applications or training to import and process this information as a Database. I am constrained to use Excel as the limited database that it is. I concur there are many things I could do with SQL. The real issue remains however that the data is dynamic over time and there is much work to be done just monitoring and fixing defects (iferror statements everywhere) when they show up. — Dashboarder, May 31 '16 at 19:31
That's crazy, man... there are tools out there that do the heavy lifting for you, and they are FREE. And they integrate with Excel beautifully. You really like this employer, right? Yes, you can do this in Excel/VBA. I'm sure of it. It just seems like a monumental task for what a DB does out of the box. Again, I know it's out of your hands, but I have to shake my head. One last stab... SQLite? Your management doesn't even have to know. — Hambone, Aug 03 '16 at 03:07

Tim Williams · Answer 1 · 2015-10-21T19:50:29.717

Here's how your code looks "cleaned up" - notice there's no need to select/activate anything, and you can use variables to refer to workbooks and worksheets etc.

EDIT: completed but untested

Sub CopyPasteOAP(bookname)

    Dim wbMaster As Workbook, wsMaster As Worksheet
    Dim wbSrc As Workbook, wsSrc As Worksheet, wsNew As Worksheet
    Dim f As Range, rcopy As Range

    Set wbMaster = ThisWorkbook
    Set wsMaster = wbMaster.Sheets(1)

    Set wbSrc = Workbooks(bookname)
    Set wsSrc = wbSrc.Worksheets(1)
    Set wsNew = wbSrc.Worksheets.Add(after:=wbSrc.Worksheets(1))

    With wsSrc
        .Range(.Range("G1"), .Range("G1").End(xlDown)).Copy wsNew.Range("A1") 'Date Column
        .Range(.Range("B1"), .Range("B1").End(xlDown)).Copy wsNew.Range("B1") 'Unit Column
        .Range(.Range("H1"), .Range("H1").End(xlDown)).Copy wsNew.Range("G1") 'Severity Column
        .Range(.Range("BL1"), .Range("BL1").End(xlDown)).Copy wsNew.Range("M1") 'Fuel in Oil
        .Range(.Range("BP1"), .Range("BP1").End(xlDown)).Copy wsNew.Range("N1") 'Coolant in Oil
    End With

    wsNew.Range("C2:C50").Value = "OAP" 'Enter in OAP

    FindAndCopy wsSrc, "Lab Comments", wsNew.Range("D1") 'Fault Description = Lab Comments
    FindAndCopy wsSrc, "Lab Recommendations", wsNew.Range("L1") 'Notes = Lab Recommendations

    Set rcopy = wsNew.Range(wsNew.Range("A2"), wsNew.Range("A2").End(xlToRight))
    Set rcopy = wsNew.Range(rcopy, rcopy.End(xlDown))

    With rcopy.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

    'copy to first empty row
    rcopy.Copy wsMaster.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

    wbSrc.Close False


End Sub


'utliity sub: find and copy a column if it exists
Sub FindAndCopy(sht As Worksheet, hdr As String, rngDest As Range)
    Dim f As Range
    Set f = sht.Cells.Find(What:=hdr, LookIn:=xlValues, LookAt:=xlPart, _
                  SearchDirection:=xlNext, MatchCase:=False)

    If Not f Is Nothing Then
        sht.Range(f, f.End(xlDown)).Copy rngDest
    Else
        MsgBox hdr & " not found!", vbExclamation
    End If
End Sub

Thank you Tim, your code is indeed elegant, and efficient, but the issue I added in my answer remains. The Sheet2.Range("...") steps fail in the execution, as they are Sheets specified outside of the Workbook that is running the VBA code. — Dashboarder, Oct 21 '15 at 18:36
It is infuriating, as I can see the sheet CodeName in the properties page, and it appears just like every other Workbook. I will experiment with the other Worksheet call protocols. The advantage of the first sheet in the CSV is that it is the name of the file "bookname", and then the added sheet becomes "Sheet1". Perhaps there is hope yet! — Dashboarder, Oct 21 '15 at 18:42
Success!!! Changed the sheet references to: wbSrc.Sheets(booknamelesscsv) for the ".csv" file data opening Sheet (Sheet1), and to wbSrc.Sheets("Sheet1").Range("A1") for the compilation sheet (Sheet2). Copy and paste away, reformat, and then copy the result to the master workbook, close file, and on to the next. — Dashboarder, Oct 22 '15 at 15:23

Dashboarder · Answer 2 · 2015-10-22T15:32:23.160

After a little more reading, I discovered that I was not allowed to use the CodeName property between Workbooks. It works within a Workbook to access the Worksheets, but not to activate and select a Worksheet that is outside the Workbook that the VBA code resides in.

I will explore the other options that have been presented, but this at least helps me to stop pulling my hair out trying to figure out what works in one Workbook will not work in another. Learning the hard way - a lesson I will not soon forget!

Success!!!

Taking the optimized code from Tim, adapted for the .csv case of the first sheet being named the Workbook name. Dropped the .csv, and then referenced each by the sheet "name". Works perfectly. Props to Tim for the optimization hints which also allowed for some speed improvements.

Truncated the Workbook Name so it would match the name of the sheet (Sheet1)

booknamelesscsv = Left(bookname, Len(bookname) - 4)

Started Routine (referencing the named sheet)

With wbSrc.Sheets(booknamelesscsv)

Copied the data from the named sheet to the new sheet "Sheet1" (Sheet2)

.Range(.Range("G1"), .Range("G1").End(xlDown)).Copy wbSrc.Sheets("Sheet1").Range("A1")

All is now working. Data imported, formulas formulating, reports reporting.

Thank you.

How do I keep track of worksheets and workbooks as they pass between subroutines when .Activation is not working?

2 Answers2