- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
Or if this helped you, please, click (like)
Regards
Alan