Proof of concept review - can't reference dynamically created table cells from second worksheet

[ajcourtney]

Thanks for a great forum! I've learned a ton so far. I'm soliciting recommendations on logistics and syntax. Some background: I don't have any experience with VBA or enough knowledge within Excel. Instead I have about 20 years of software development experience in financial svcs using portfolio management software designed to keep investment management and operational processes out of Excel and in the investment management platform. The problem with at least one of these platforms is it's based on 1980's flat file technology - so no relational DB technology in play.


During the past couple of weeks, I've been trying to use figure out how to use Excel as a reporting "add on" if you will to one of these software systems, making sure it would be scalable to 100's or 1000's of reporting instances. Specifically, I'm exporting multiple text files out of the investment management platform that contain 100-200 data points (e.g., investment holdings, market values, % of portfolio, total dividends, total interest, total distributions, etc.) that get placed in an Excel worksheet that other reporting worksheets (let's say 3-6 other sheets) within a single workbook use to build a client facing reporting package. There would be one workbook per client relationship which would enable you to customize the appearance on a per client level if so desired.


The way the macro currently works (Autpen) is it clears the contents in the import sheet from the prior import, it parses each text file into a static location on the import sheet, builds the necessary table or two, and sorts each table based on the appropriate column in the array. The only time a table is constructed is when sorting is required. Otherwise, it's a simple data import to regular cells.


I've got just about everything finished with two of the sheets but I'm having trouble reliably referencing cells in tables created on the import sheet from the reporting sheets. Apparently, you can't simply copy the table cell and paste the link to that cell into a separate sheet. If I try to do this with say =$A$1, the next time I open the sheet, "=$A$1" becomes "=$A$2".


So then I tried the =INDEX function to reference the correct table cell. So for the first table, the macro will correctly build Table1 as shown in the Excel Name Manager. =INDEX(Table1,1,1) should give me the correct cell value the next time the spreadsheet is opened, correct?


Instead, the cell shows: =INDEX(#REF!,1,1) presumably because the array hasn't yet been built the moment the second sheet looks at the reference to try to obtain the value, right?


After fighting with this process for the past six hours (and losing) and searching for the past two hours, I decided to start with a fresh workbook. Here's a sample of the macro that Excel recorded for me. Does anyone have any insight into what I'm doing wrong with Excel 2010? Am I looking at this process correctly or should I be tackling this process differently?


Thanks!

Sub Auto_Open()
    Cells.Select
    Selection.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\aaron\Desktop\courtad9.txt", Destination:=Range("$A$1"))
        .Name = "courtad9"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:E200").Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=ABS(RC[-1])"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H17")
    Range("H1:H17").Select
    Range("F1:K200").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$F$1:$K$200"), , xlNo).Name = _
        "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"
    ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[[#All],[Column3]]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[LockeGarmin]

To my surprise, when you clear contents on a sheet it will destroy any table that is fully within the range being cleared. So the reference ends up getting destroyed and Excel replaces it with "#REF!" in all formulas that referenced anything in the table. If I were you what I would do is instead of referencing the table directly in any of your formulas, create a named range that references the whole table and re-record your macro so that it edits the table named range. When the table gets destroyed the named range will have "#REF!", but that's the only reference you'll need to edit. The rest of your formulas in the formula tree won't need to get touched. The only drawback I can see from this is you'll have more work to do if you wanted to reference a particular column you'd need to make a new named range for each column. In other words you'd be losing the nice table syntax in your other formulas which may or may not be of any concern to you.

 

[ajcourtney]

Hey Locke - thanks for filling in the gap of my knowledge here. Your comments led to a re-think on this process which eventually led to a completely workable solution: prior to dynamically building each table, I simply have the macro delete the text file data link, then build the table atop the imported data, then sort, then copy the resulting table cell ranges into their final places on each reporting sheet. This results in a fully automated process.

Thanks for your insight! It really got me out of an endless loop.