Excel Macro: SugarCRM, Checkboxes, and Excel Reports.

Working with SugarCRM and Excel lately we have run into one issue during export of reports data to a spreadsheet. 

If you use Checkboxes, the value in the Excel cell is the HTML source code for checkbox, as follows:

Yes: <input class="checkbox" type="checkbox" name="checkbox_display" checked="checked" disabled="disabled" />

No: <input class="checkbox" type="checkbox" name="checkbox_display" disabled="disabled" />

This poses an issue when you want to use actual values like 1 and 0, or Yes and No.

You could CTRL+H everytime you refresh the data, but that is tedious work.

  1. Turn on the Developer Tab
  2. Go to the Developer Tab, click on Macros.
  3. Macro Name: FixCheckboxes -> Create
  4. In the new window that appears you should see two lines:
Sub FixCheckboxes()

End Sub
  1. Copy and paste the script below, overwriting those two lines.
  2. Replace the Sheets("SourceDataFromSugar") with the name of the sheet that contains the source data from SugarCRM.
  3. Save and close the current window.
  4. Back within the excel document, go to the Developer Tab -> Macros -> Select: FixCheckboxes -> Run
Sub FixCheckboxes()
    Sheets("SourceDataFromSugar").Select

    cb_yes_find = '<input class="checkbox" type="checkbox" name="checkbox_display" checked="checked" disabled="disabled" />'
    cb_yes = '1'
    Cells.Replace What:=cb_yes_find, replacement:=cb_yes

    cb_no_find = '<input class="checkbox" type="checkbox" name="checkbox_display" disabled="disabled" />'
    cb_no = '0'
    Cells.Replace What:=cb_no_find, replacement:=cb_no
End Sub

You can now assign this macro to a button, add the button to your SourceDataFromSugar sheet and click it after refreshing the reports data.

At this time, I have a ticket open with SugarCRM requesting the ability to remotely activate certain Add-In functions. My goal is to create a one-click button that Logs into Sugar, Refreshes the reports data, and then runs the Search and Replace.

Print