Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
A.Acheson
in reply to: hldr

The key to populating cells like your requesting  in excel is really where do you want to start and stop your input of data. 

Because the range can be any size you need to try and find out what the size may be. You want to know where the last blank cell is in a specific column in many cases. 

 

Here is a go excel method to achieve this. 

GoExcel.Open(""C:\User\Desktop\" & test & ".xlsm"", "Parts List")
'Column Title 
GoExcel.CellValue("K1") = "Mainassy" 
GoExcel.CellValue("L1") = "assembly1"
GoExcel.CellValue("M1") = "Amount"
GoExcel.CellValue("N1") = "1"
For i = 2 To 1000  'Loop to a known row number past the populated data in the excel sheet
	Cellvalue = GoExcel.CellValue("A" & i+1) 'Check a known column with cells that are consistently populated like "Item" from a BOM List + 1 then to end on right row
	'Cell content
	GoExcel.CellValue("K"&i) = "Mainassy" 
	GoExcel.CellValue("L"&i) = "assembly1"
	GoExcel.CellValue("M"&i) = "Amount"
	GoExcel.CellValue("N"&i) = "1"
	
	If Cellvalue = "" Then Exit For
Next

GoExcel.Save
GoExcel.Close

 

Here is another method using current region to find the table size, then find the last row then use autofill to populate data. You could also use a for loop like above to accomplish the same.  

https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofill

https://docs.microsoft.com/en-us/office/vba/api/excel.range.currentregion

 

'AddReference "Microsoft.Office.Interop.Excel.dll"
'Imports Microsoft.Office.Interop
'Imports Microsoft.Office.Interop.Excel

Sub Main()

Dim xlApp As New Excel.Application 'start a new instance of Excel
xlApp.DisplayAlerts = False
xlApp.Visible = True

Dim xlWorkbook As Workbook
xlWorkbook = xlApp.Workbooks.Open("C:\User\Desktop\" & test & ".xlsm")

Dim xlWorksheet As Worksheet
xlWorksheet = xlWorkbook.Worksheets.Item("Parts List")

'Column Title 
xlWorksheet.Range("L1").Value = "assembly1"
xlWorksheet.Range("M1").Value = "Amount"
xlWorksheet.Range("N1").Value = "1"

Dim LastCol,LastRow As Long
LastCol = xlWorksheet.Range("A1").CurrentRegion.Columns.Count 
LastRow = xlWorksheet.Range("A1").CurrentRegion.Rows.Count 'Find last row use any row that will have consistent content

'Cell content
xlWorksheet.Range("L2").Value = "assembly1"
xlWorksheet.Range("M2").Value = "Amount"
xlWorksheet.Range("N2").Value = "1"

'Cell Autofill
xlWorksheet.Range("L2").AutoFill(xlWorksheet.Range("L2:L" & LastRow))
xlWorksheet.Range("M2").AutoFill(xlWorksheet.Range("M2:M" & LastRow))
xlWorksheet.Range("N2").AutoFill(xlWorksheet.Range("N2:N" & LastRow))
	
xlWorkbook.Save
'xlWorkbook.Close (False)
'xlApp.Quit
End Sub

 I see that you are writing to a macro enabled work book so likely you can do the same within Excel VBA. I have a lot of Macros in my Macro workbook for processing BOM's and these are triggered by a population of a cell then the Excel VBA Macro takes over and finish the rest. It can be easier than converting the VBA macro to VB.NET. 

If this solved a problem, please click (accept) as solution.‌‌‌‌
Or if this helped you, please, click (like)‌‌
Regards
Alan