Manipulating exported excel using iLogic

[farshidDWRFR]

Hi everyone,

I need help with 2 things:

I export BOM to 4 Excel spreadsheets (sorted by parts types) using iLogic, and I need:

1- Columns with certain titles deleted from Excel file before export.

2- There are parts with "0" quantity included in the Excel file. I need the rows containing cells with the value "0" deleted.

I appreciate everyone's help and recommendations.

Thanks

[A.Acheson]

Could you supply the ilogic rules your using to do the export?  Can you share the columns you want to export? Maybe an excel sheet?  Modifying the BOM export has limited options. You could use the xml option to modify the columns exported and then changed the data directly in excel.

[farshidDWRFR]

Hello there,

Thanks for your reply. My iLogic code is attached as a text file. I have also attached one of the four Excel files exported from this code. Looking at the Excel file, what I am after is a piece of code added to my current snippet that deletes the columns with blank cells plus the column with the title "Type" and rows with the value "0".

Please let me know if you can help me.

Thanks very much

[A.Acheson]

Here is the source for a  vba sub routine that deletes blank columns.  This is the only new section of code. Much of the other functionality was really just reworking what you had all ready. See attached file, test it out on a sample export and see if it fits the bill. 

Functionality Requests

1. To check Column by name and then delete "Type" , achieved by using Function FindColumn: 
2. To check quantity column for value "0" then delete row if found , achieved by Sub DeleteRow: 
3. To check for blank columns then delete, achieved by Sub DeleteEmptyColumns: 

[farshidDWRFR]

Hello and thanks for your reply.

I tested the code and it did not work as expected. I tried uncommenting the 3 lines after "Functionality Requests" and received the following errors:

Error on Line 2 : Class 'BOMExport' must implement 'Sub Main()' for interface 'Autodesk.iLogic.Interfaces.IRuleInterface'.
Error on Line 5 : First statement of a method body cannot be on the same line as the method declaration.
Error on Line 166 : Statement is not valid in a namespace.
Error on Line 187 : Statement is not valid in a namespace.
Error on Line 209 : Statement is not valid in a namespace.
Error on Line 226 : Statement is not valid in a namespace.
Error on Line 239 : 'End Class' must be preceded by a matching 'Class'.

What's your thought on this?

Thanks

[A.Acheson]

Apologies this is only instructions on what you were looking to achieve, please comment also 'Functionality Requests 

'Functionality Requests
'Function FindColumn: To check Column by name and then delete "Type"
'Sub DeleteRow: To check quantity column for value "0" then delete row if found
'Sub DeleteEmptyColumns: To check for blank columns find delete

[farshidDWRFR]

Actually, this was the first thing I did, however it did not work.

[A.Acheson · Accepted solution]

Please comment or remove all of these lines and retest. 

'Functionality Requests
'Function FindColumn: To check Column by name and then delete "Type"
'Sub DeleteRow: To check quantity column for value "0" then delete row if found
'Sub DeleteEmptyColumns: To check for blank columns find delete

[farshidDWRFR]

Hi again,

With some changes, I fixed the issue.

The problem was my BOM code creates 4 separate files and I needed to add the title and row remove codes for all 4 files. Now it works as expected.

Thanks for your help and support