On synchronize Exact Online GL transactions with Invantive Control for Excel error: "You are trying to move cells within a table on the worksheet"

Question

When trying to synchronize my model with Invantive Control for Excel, the following error occurs:

This is the complete error message:

Type: System.Runtime.InteropServices.COMException
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Range.Delete(Object Shift)
   at Invantive.Producer.Control.Utility.ResizeBlock(ModelCache modelCache, Workbook workbook, List`1 blocks, iea_blocks_v block, Cube currentCube, Cube desiredCube, Point3d startPoint, Int64 growLength) in File169:line 7968
   at Invantive.Producer.Control.Utility.AdjustBlockDimensionOneAxis(SystemWorkingContext context, ModelCache modelCache, Workbook workbook, iea_blocks_v currentBlock, Cube currentCube, Cube desiredCube, IEnumerable`1 anchoredBlocksResult, List`1 blocks, Point3d desiredStartPoint, Int64 growLength, iea_blocks_vBlk_repeat_rows_along adjustAxis, iea_blocks_vBlk_repeat_rows_direction adjustDirection) in File169:line 7293
   at Invantive.Producer.Control.Utility.AdjustBlockDimensions(SystemWorkingContext context, ModelCache modelCache, Workbook workbook, List`1 blocks, iea_blocks_v currentBlock, Cube currentCube, Cube desiredCube, Point3d desiredStartPoint) in File169:line 6617
   at Invantive.Producer.Control.SyncToDatabaseForm.SyncDownload(DoWorkEventArgs e) in File170:line 2173

What are the steps to solving this error?

EDIT

The SQL statement on the block in Invantive Control is:

select division_hid
,      division_name
,      reportingyear_attr
,      years_balance_code_attr
,      years_balance_description
,      open
from   BalanceLines
where  years_balance_balancetype_attr = "B"
and    reportingyear_attr = $X{eol_year_to}

The $X{eol_year_to} is a referenced to a named range eol_year_to whose value is used in the query.

I have added to the Excel table two columns, one with a vertical search on GL account classification code and one on GL account classification description. After that addition, the model no longer synchronizes with Exact Online.

Answer 1

Invantive Control puts data in an Excel table. I've added formulas to the last two columns of this table. Invantive Control may not shift these formulas since that would break the Excel table.

We've added the formulas to the SQL-code. The problem is solved now.

Answer 2

You can get the GL account classification code and description from Exact Online in one go also, please use something like the following to get a list of all GL transactions plus the classification code/description:

select tln.division division_hid
,      sdn.description division_name
,      tln.financialyear finyear_number_attr
,      tln.financialperiod finperiod_number_attr
,      tln.glaccountcode glaccount_code_attr
,      tln.glaccountdescription glaccount_description
,      tln.journalcode gltransaction_journal_code_attr
,      tln.currency gltransaction_journal_currency_code_attr
,      tln.amountdc amount_value
,      tln.vatpercentage amount_vatpercentage
,      tln.description
,      tln.accountname account_name
,      sysdate nu
,      '=I_EOL_GL_ACTCLN_CODE(,$C{E,.,.,^+4,.})' glactclncode
,      '=I_EOL_GL_ACTCLN_DESCRIPTION(,$C{E,.,.,^+4,.})' glactclndescription
from   transactionlines tln
join   systemdivisions sdn
on     sdn.code = tln.division
where  tln.financialyear   >= $X{eol_year_from}
and    tln.financialyear   <= $X{eol_year_to}
and    tln.financialperiod >= $X{eol_month_from}
and    tln.financialperiod <= $X{eol_month_to}
order 
by     tln.division
,      tln.financialyear
,      tln.glaccountcode 

Classifications on Balance

In your query, this would be:

select division_hid
,      division_name
,      reportingyear_attr
,      years_balance_code_attr
,      years_balance_description
,      open
,      '=I_EOL_GL_ACTCLN_CODE(,$C{E,.,.,^+3,.})' glactclncode
,      '=I_EOL_GL_ACTCLN_DESCRIPTION(,$C{E,.,.,^+3,.})' glactclndescription
from   BalanceLines
where  years_balance_balancetype_attr = "B"
and    reportingyear_attr = $X{eol_year_to}

Column Expressions

The process is now:

• The SQL loads all general ledger transactions of Exact Online for the indicated period into Excel.
• Do this by pressing Synchronize.
• Then the Excel formulas I_EOL_GL_ACTCLN_CODE and I_EOL_GL_ACTCLN_DESCRIPTION are evaluated.

These formulas take a unique division code (company) plus at least the GL account code. Since the GL account code varies per GL transaction, you want to refer to the contents of a specific column of your query. You can generate a hard-coded formula with the actual GL account code in it. But it is better to take the GL account code from another cell in Excel. The $C{...} syntax allows you during synchronization to replace it by a cell reference. Please consult the online manual and the model editor wizard.

The $C{E,.,.,^+4,.} means:

• Pivot method: use X/Y orientation is in (E)xcel. You could use D too to take any transposition from database into Excel into account, but is seldom used.
• .: current block, like '.' in vi sometimes referring to 'here'.
• .: current sheet, like '.' in vi sometimes referring to 'here'.
• ^+4: column, the left-most cell ('^') of the current Excel row and then four cells to the right.
• .: current row

You may also add two more coordinates to the column expression for column 2 and row 2 to indicate a range of cells.