Ilogic to "Paste" BOM volume of part into a specific Excel Sheet, tab, cell

[sakkie.coetzee]

I think the title pretty much describes what I want to do.

My scenario. 

Firstly a disclaimer...I have to humbly apologize for knowing precious little about programming.

I have a part in Inventor for which I pull many parameters from Excel. (Link, easy)

I've also been able to include part volume into the BOM, and export the BOM into an intermediate excel sheet that Inventor creates.

From the excel sheet containing the parameter data, I then run a  query to pull the BOM data into my working file.

I use the difference between the blank volume, and the part volume to determine scrap.

Everything works, and I know there must be several ways to simplify this process, but as an academic exercise if nothing else, I'd like to drop the BOM data straight into a tab in the working Excel sheet, overwriting any previous data in that tab.

I tried to search for help, but do not know enough to even ask the search engine the correct question. I saw some VB code along these lines, but my system only runs iLogic.

[A.Acheson]

That should be an easy enough process. To clarify are you looking to copy one iproperty or the whole BOM to the excel sheet? Can you share by images how you are currently extracting the information? To get the BOM onto your tab it will likely be a copy sheet process from the newly exported worksheet to your current work sheet. Here is  an example of a copy sheet from one excel workbook to another. 

[sakkie.coetzee]

Thanks for the reply Alan.

I only need the assembly volume.

I tried...

Dim Volume As Integer = iProperties.Volume

GoExcel.Open("C:\Users\ZA-19800\Documents\0. Drawings\Inventor\00. Tooling\Punch Sleeves\Punch Sleeves - Inventor Models_11-26-2022\Punch Sleeves - Inventor Models_11-26-2022\Cans\Design_a_Can", "Sheet2")

With excelApp
	.Range("B2").select
	.ActiveCell.Value = Volume
End With

...but it keeps on coming up with an error message:

"Object variable or With block variable not set." identifying the line ".ActiveCell.Value = Volume"

The solution is probably stupid easy, highlighting my complete lack of programming knowledge.

[Cadkunde.nl]

Try the snippet in Excel Data Links --> Application

excelApp = GoExcel.Application

before you do with excelApp 

[sakkie.coetzee]

Dim AsmDoc As AssemblyDocument
AsmDoc = ThisApplication.ActiveDocument

Dim Volume As Boolean = iProperties.Volume

excelApp = GoExcel.Application
GoExcel.Open("C:\Users\ZA-19800\Documents\0. Drawings\Inventor\00. Tooling\Punch Sleeves\Punch Sleeves - Inventor Models_11-26-2022\Punch Sleeves - Inventor Models_11-26-2022\Cans\Design_a_Can", "Sheet2")

With excelApp
	.Range("B2").select
	.ActiveCell.Value = Volume
End With

 @Cadkunde.nl   thank you for the responds.  I added your recommendation, but it made no difference.

My previous post was not accurate.  It is THIS line that it does not like.

.Range("B2").select

[Cadkunde.nl]

To use GoExcel i think you also need to use the snippets. There is MultiValue list from Excel to use instead of range.

I prefer to do it without ilogic predefined snippets.

To do that, start your rule with:

AddReference "Microsoft.Office.Interop.Excel"
Imports Excel = Microsoft.Office.Interop.Excel

And to get range:

Dim excelApp As Microsoft.Office.Interop.Excel.Application

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False

Dim wb As Excel.Workbook = excelApp.Workbooks.open("filename")
Dim ws As Excel.Worksheet = wb.Worksheets(1)

Dim range As Excel.Range = wb.Sheets("name").Range(ws.Cells(1, 1), ws.Cells(2, 1))

'Release Excel
wb.close
excelApp = Nothing

[sakkie.coetzee]

I tried your recommended additions, but no go. I'm pretty sure I've got something fundamentally wrong.

I got it right somehow, with a VERY few lines in iLogic, and most of the lines was change to Comments leaving only 3 or so lines "active"

Well, that's what I thought, because when I deleted what I thought was only comments, it stopped working.

The part that I thought doing the work is copied below.

excelApp = GoExcel.Application
GoExcel.Open("C:\Users\ZA-19800\Documents\Can Data A.xlsx", "Volume")
GoExcel.CellValue("B2") = "123456"

[A.Acheson]

You actually only need one line if only using go excel see instruction post here 

GoExcel.CellValue("filename", "sheetname", "cellnumber") =""

Go excel is a very limit ilogic only function to access excel.  Accessing excel through VB.Net launguage by "Dim excelApp As Microsoft.Office.Interop.Excel.Application" is a longer method but you gain access to all excel functions similar to writing a macro in vba in excel.  Therfore go excel function snippets and the Excel Application snippets cannot be interchanged. Use one or the other. 

GoExcel.Open("C:\Users\ZA-19800\Documents\Can Data A.xlsx", "Volume") 
GoExcel.CellValue("B2") = "123456"

 

[Cadkunde.nl]

Acheson is right, that should do the trick for your situation.

I'm over complicating it

[sakkie.coetzee]

This single line rule runs with no error messages.  How-ever, the information to "paste" do not show in Excel.

I can change the file name, or sheet name, and the rule immediately comes up with an error message.

The information to "Paste" I can change to what-ever I want.  The rule runs flawless, but cell "B2" stays empty.

It does not matter whether I have the Excel sheet open or not.

GoExcel.CellValue("C:\Users\ZA-19800\Documents\Can_Data_A.xlsx", "Volume", "B2") = iProperties.VolumeOfComponent("Design_a_Can:1")

[A.Acheson · Accepted solution]

Is there a value from that iproperty? You can try a static string (“Test Value”) first to ensure you are getting access to the file. Remember to save if the excel sheet is closed.

GoExcel.Save

[sakkie.coetzee]

The below rule works Perfect. @A.Acheson  and @Cadkunde.nl , thanks for all the help.  Appreciated.

Even if the part volume has not been updated after dimensional changes, it still collects the correct volume, and pastes it into "B2"

Here is the crunch.  The Excel file cannot be open.

Now, to find a way around that limitation.

GoExcel.CellValue("C:\Users\ZA-19800\Documents\0. Drawings\Inventor\00. Tooling\Punch Sleeves\Can Data.xlsx", "Volume", "B2") = iProperties.VolumeOfComponent("Design_a_Can:1")
GoExcel.Save

[A.Acheson]

And here is a working example of the second longer method to access excel shown above by @Cadkunde.nl 

it will still have the same drawback of not being abled to write to the all ready open file but it has more functionality as it has access to all of excel functions. You can likely find some code on stack overflow to detect the open excel file. At least alerting you to the fact it is open.  if you don't the file will be opened read only and you get a chaos scenario.  The below is very similar to what is under the hood of the go excel function. 

AddReference "Microsoft.Office.Interop.Excel"
Imports Excel = Microsoft.Office.Interop.Excel

Dim excelApp As Microsoft.Office.Interop.Excel.Application
excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = True
Dim wb As Excel.Workbook = excelApp.Workbooks.Open("C:\Users\ZA-19800\Documents\0. Drawings\Inventor\00. Tooling\Punch Sleeves\Can Data.xlsx")

Dim ws As Excel.Worksheet = wb.Worksheets("Volume")
ws.Range("B2").Value = "1234"

'wb.save
'wb.close
'excelApp = Nothing