This Analytics Edge wizard is used to write the results of an analysis to a worksheet. It can either overwrite an entire sheet, or be written to a specific location. It can also optionally copy the results to a Google Sheets file as well.
It is normally the last step in an analysis sequence, but the macro can continue processing data and write to other worksheets or files.
Worksheet – enter a name for the worksheet to write to (located in the current workbook). If the worksheet does not exist, it will be created.
Replace entire worksheet – removes all data and formatting before writing the contents of the table to the worksheet. This is the most efficient option and should be used for large downloads.
Insert into worksheet – specific the top-left cell for the results to be written to. This option will clear previous data at that location.
Clear data and formatting – clears the existing data and formatting from the worksheet. The existing data may contain more rows and columns than the new data, so the add-in clears the block of contiguous data it finds at the target cell.
Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data (numeric versus text versus date).
Overwrite (do not clear) – do not clear any of the worksheet. The results overwrite the existing cell values (formatting is not affected). Note that if there is no new data to write, the existing data will not be cleared.
Do not include header – check this to prevent the column names from being included in the first row of the results written.
Transpose – check this to write each column as a row in the worksheet.
Add hyperlinks to web addresses – if the results include web addresses, they will be converted to hyperlinks that can be clicked on.
Format as Excel table – format the output as an Excel table using one of the predefined Excel formats. Select from the formats listed or pick the empty entry for no colour formatting.
CAUTION: do NOT try to write to a range that includes merged cells!
Copy to Google Sheets – click the Select/Modify button to send a copy of the results to a Google Sheets worksheet. This is an alternative to using a separate Write to Google Sheet function. See: Write to Google Sheet.
Updated for v10.0.6