GoExcel.Cellvalues() without skipping empty cells

[Daan_M]

Hi,

I'm creating a BOM Excel export and i want to copy two colums (the articlecode and quantity) from this file to a different Excel file.

I copy the range of cells i need, but when i paste the cells into the other Excel document, it basically skips the empty cells and compacts the values, so the variables in the in wrong rows e.g;

BOM export

Current Result in other Excel

Desired result in other Excel

The code i use:

Imports System.IO
Dim oDoc As Inventor.Document = ThisDoc.Document
Dim oFullAssemblyPath = oDoc.FullDocumentName
Dim oFolderPath As String = Left(oFullAssemblyPath, (InStrRev(oFullAssemblyPath, "\", - 1, vbTextCompare) - 1)) & "\"

Dim oTempBomFileName As String = oFolderPath & "File1"
Dim oVaultExcel As String = oFolderPath & "File2"
Dim oVaultExcelSheet As String = "Sheet"

ThisBOM.Export("Structured", oTempBomFileName, kMicrosoftExcelFormat,oSheet)

Dim oArticles = GoExcel.CellValues(oTempBomFileName, "Sheet1", "J2", "J21")
Dim oQuantities = GoExcel.CellValues(oTempBomFileName, "Sheet1", "F2", "F21")

GoExcel.Open(oVaultExcel, oVaultExcelSheet)

GoExcel.CellValues(oVaultExcel, oVaultExcelSheet, "E34", "E53") = oArticles
GoExcel.CellValues(oVaultExcel, oVaultExcelSheet, "F34", "F53") = oQuantities

GoExcel.Save
GoExcel.Close

[m_baczewski · Accepted solution]

@Daan_M 
Hi, I believe that unfortunately, it’s not possible to do this using the methods available in iLogic, but you can achieve it this way:

Imports System.IO
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Sub main 
	
	Dim oDoc As Inventor.Document = ThisDoc.Document
	Dim oFullAssemblyPath = oDoc.FullDocumentName
	Dim oFolderPath As String = Left(oFullAssemblyPath, (InStrRev(oFullAssemblyPath, "\", - 1, vbTextCompare) - 1)) & "\"
	
	Dim oTempBomFileName As String = oFolderPath & "File1"
	Dim oVaultExcel As String = oFolderPath & "File2"
	Dim oVaultExcelSheet As String = "Sheet"
	
	ThisBOM.Export("Structured", oTempBomFileName, kMicrosoftExcelFormat,oSheet)
	
	If Not System.IO.File.Exists(oTempBomFileName) Or Not System.IO.File.Exists(oVaultExcel) Then
		Exit Sub
	Else
		excelApp = CreateObject("Excel.Application")
		excelApp.Visible = False
		
		excelWorkbook1 = excelApp.Workbooks.Open(oTempBomFileName)
		excelWorkbook2 = excelApp.Workbooks.Open(oVaultExcel)
		
		excel1Sheet = excelWorkbook1.WorkSheets("Sheet1")
		excel2Sheet = excelWorkbook2.WorkSheets(oVaultExcelSheet)
		
		sourceRange = excel1Sheet.Range("A1:B5")
		sourceRange.Copy
		targetRange = excel2Sheet.Range("A1")
		
		targetRange.PasteSpecial(2)
		
		excelWorkbook1.Save
		excelWorkbook2.Save
		excelWorkbook1.Close
		excelWorkbook2.Close
		excelApp.Quit
		excelApp = Nothing
	
	End If
	
End Sub

[Daan_M]

Hi, thank you for the reply, the solution works perfectly 🙂