Print Page - Insert row into excel worksheet

Title: Insert row into excel worksheet
Post by: simonf on June 28, 2023, 11:15:45 PM

I am trying to add a new row to the top of an existing Excel file each day. The following script overwrites the top row rather than inserting a new row.
Can insert a new row at the top of the sheet?

Code Select


here=DirScript()
LocalFile=strcat(here,"CSVs\book2.csv")

row = 1
Headingtxt = 'Sample Heading text'

objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open(LocalFile)


	;Insert Row of Data
	objCells=objXL.Cells(row)
	objCells.Value=Headingtxt
	ObjectClose(objCells)

CurrentWorkbook = objWorkbooks.item(1)
CurrentWorkbook.Save()
objWorkbooks.Close()
objXL.Quit()

Title: Re: Insert row into excel worksheet
Post by: stanl on June 29, 2023, 02:45:19 AM

maybe something like

Code (Winbatch) Select



objXL = ObjectOpen("Excel.Application")
objXL.Visible = @TRUE
objWorkbooks=objXL.WorkBooks
objWorkbooks.Open(LocalFile)
;insert top row
objWorkbooks.Activesheet.Rows("1:1").Select()
objXL.Selection.Insert(::Shift=-4121)

Title: Re: Insert row into excel worksheet
Post by: simonf on July 02, 2023, 09:08:09 PM

Perfect, thank you Sir. I adjusted the "Select" slightly and it works as required;

Code Select

wrkshtname = objActiveSheet.Name
objWorkSheet = objXL.Worksheets(wrkshtname)
objRows = objWorkSheet.Rows("1:1")
objRows.Select
objXL.Selection.Insert(::Shift=-4121)

WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: simonf on June 28, 2023, 11:15:45 PM