Excel Integration

With enough duct tape (VBA) anything is possible with Excel...

Just trying to think through what you're after here. When you say 'tied to the folder that houses the data points' do you mean directly connected to ZZTakeoff somehow or could an Excel/CSV export saved in the project folder work as the starting point? How are you linking them now?

You can dynamically populate a spreadsheet template with Name, Quantity and Unit values from an XLSX file with a VBA routine like the one below. Best practice is to save it in an XLAM (add-in) file instead of the template and assign it to a button or shortcut-key.

_____________________

Sub LoadPayItems_SelectedColumns()

  Dim wbSource As Workbook

  Dim wbThis As Workbook

  Dim wsSource As Worksheet

  Dim rngPayItems As Range

  Dim SourcePath As String

  Dim LastRow As Long

  Dim NameCol As Long, MeasCol As Long, UnitCol As Long

  Dim DataRange As Range

  Dim TargetRange As Range

  ' Reference to this workbook

  Set wbThis = ThisWorkbook

  ' Build path for Takeoff.xlsx in same folder

  SourcePath = wbThis.Path & Application.PathSeparator & "Takeoff.xlsx"

  ' Open Takeoff.xlsx

  Set wbSource = Workbooks.Open(SourcePath)

  Set wsSource = wbSource.Sheets(1)

  ' Find the last row of data in column A

  LastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row

  ' Locate the column indexes by header row (assume row 1 has headers)

  NameCol = Application.Match("Name", wsSource.Rows(1), 0)

  MeasCol = Application.Match("Measurement1", wsSource.Rows(1), 0)

  UnitCol = Application.Match("Unit1", wsSource.Rows(1), 0)

  If IsError(NameCol) Or IsError(MeasCol) Or IsError(UnitCol) Then

    MsgBox "One or more required columns (Name, Measurement1, Unit1) were not found.", vbCritical

    wbSource.Close SaveChanges:=False

    Exit Sub

  End If

  ' Build source range with only selected columns

  Set DataRange = Union(wsSource.Range(wsSource.Cells(1, NameCol), wsSource.Cells(LastRow, NameCol)), _

             wsSource.Range(wsSource.Cells(1, MeasCol), wsSource.Cells(LastRow, MeasCol)), _

             wsSource.Range(wsSource.Cells(1, UnitCol), wsSource.Cells(LastRow, UnitCol)))

  ' Define PayItems range in this workbook (resized dynamically)

  On Error Resume Next

  wbThis.Names("PayItems").Delete

  On Error GoTo 0

  Set rngPayItems = wbThis.Sheets(1).Range("A1").Resize(LastRow, 3)

  wbThis.Names.Add Name:="PayItems", RefersTo:=rngPayItems

  ' Transfer the values column by column

  rngPayItems.Columns(1).Value = wsSource.Range(wsSource.Cells(1, NameCol), wsSource.Cells(LastRow, NameCol)).Value

  rngPayItems.Columns(2).Value = wsSource.Range(wsSource.Cells(1, MeasCol), wsSource.Cells(LastRow, MeasCol)).Value

  rngPayItems.Columns(3).Value = wsSource.Range(wsSource.Cells(1, UnitCol), wsSource.Cells(LastRow, UnitCol)).Value

  ' Close source workbook

  wbSource.Close SaveChanges:=False

  MsgBox "PayItems has been populated with Name, Measurement1, and Unit1.", vbInformation

End Sub

I can share some photos later that will help show the process i am trying to describe.

In the past using PlanSwift we could only link one external estimation template per pdf file, but one of our PDF files could have hundreds of models on it. The models all have their own external pricing template.

We would start a new folder in the right hand margin for every new model. When we opened the estimation tab, it would show only folders, open the folder and all of our quantities would show.

Please screenshot.

Not sure your trade but it sounds like you have a different template for each type of product/pay item and you want to collate them into a single quote?

Different template for every folder.

Hi Chris,

I am following your post to clearly understand your requirements. You mentioned Different Folders, have you considered setting up a different Layers, then you can have a report Group or Filter by Layers.

The PDF will only show the takeoff's on the particular Layer.

In an ideal world all of the values for the individual folder would be populated onto separate Excel templates.