LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Hi, I'm attempting to publish two inputs to a report template but on the same sheet, however the second pin seems to overwrite the space from the first with blanks. Not sure if this is a feature or I'm doing something wrong.

Output Spec :

<workbook>
<sheet inputIndex="1" outputIndex="4" dataStartRow="5" dataStartColumn="1" outputHeaderRow="false"/>
<sheet inputIndex="2" outputIndex="4" dataStartRow="5" dataStartColumn="6" outputHeaderRow="false"/>
</workbook>

So this shows correctly : <sheet inputIndex="2" outputIndex="4" dataStartRow="5" dataStartColumn="8" outputHeaderRow="false"/>

But the cells where this should publish are blank : <sheet inputIndex="1" outputIndex="4" dataStartRow="5" dataStartColumn="1" outputHeaderRow="false"/>

There is definitely data on the input node.

I could concatenate the two together initially, but I need to have at least 3 blank columns between the two datasets (Picky boss) so in Excel it looks like :
Header 1 ; Header 1 ; Blank ; Blank ; Blank ; Header 2 ; Header 2

Open to suggestions or corrections either way.

Cheers as always,

Dave

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

See if the attached works for you. Basically, I'm outputting to Excel first with the first set of data. Then using append to output the second set of data. Note the use of clocks to guarantee the execution of the 'Output Excel' node before the 'Append Excel' node.
Attachments:
Excel_Output.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

HI Ryeh, that works fine with an .xlsx template and report but unfortunately I can't use it as Append Excel node won't write to a Macro enabled Excel file .xlsm

The Output Excel node works fine with .xlsm, but the after Append Excel node the excel file is completely corrupted.

Is there a workaround, or alternatively is there a way to take two lists and put them in a new list side by side like the headers I mentioned above ? I found a way to add blank columns, but not a way to append a second list to the side of the first.

eg.
Header 1 ; Header 1 ; Blank ; Blank ; Blank ; Header 2 ; Header 2

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

What version of the LAE are you using?
Also, how many rows are on each of the record inputs?

I don't have much experience with xlsm files so am not sure how to create the sort of spreadsheet with macros that you are looking at using.
I believe that there is an issue on the Output and Append Excel file nodes in that if you are writing more than 1000 rows from different inputs to the same output worksheet only the last set of data written is preserved.
However, I think(/thought) that with the use of the Append Excel node and by setting "ExistingDataPreservation" to "Preserve All" this problem would not appear if you followed the steps in the brg attached by Ryeh.
That parameter would not, however, take effect on LAE releases prior to LAE 4.6.1 - which is why I'm trying to figure out which LAE version you are using.

If the problem you are experiencing isn't something which is fixed in a later LAE version than the one you are using are you able to post an example of the xlsm file you are using, and a description of the data (how many records, columns)?

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rhallmark

You could add agg nodes to each list to create a record count field and then join the two lists together with an xref based on the record count. Then cat the three outputs of the xref together and sort on the record count to put the lists in order.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

LS is V and the number or rows is less than 100 each. The Append Excel solution works fine, just not with Macro enabled spreadsheets - for whatever reason, it appears that this node does not work with Macro enabled spreadsheets. It will run without error and output as a .XLSM, but the file will be heavily corrupted.

rhallmark - not clear on what I need to do for your suggestion - is this designedd to add a row number to each row ? So record one is 1, record 2 is 2 etc. Then cross ref based on this ? All the data is unique so a count as such won't work as all records would be 1.

Cheers both for the reponses,

Dave

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Dave, this is what rhallmark meant. Create a new 'Count' field with the execCount (I used a filter), which will be used for the X-Ref key. Then cat the three outputs since you will get orphans if one source has more records than the other.
Attachments:
merge2ListsSideBySide.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dglavastorm

Perfect - just to clarify - execCount returns the row number ? Does it count if there is a Null field ?