Open Existing Excel File And Insert Row

Question:

I need to open an existing Excel file and insert a row (about 20 cols) of info at the end of the spread sheet. Save and close. I would love to use ADO, but OLE is fine. Where do I begin? I have looked through all the posts and haven't found anything about writing to an existing file.

Answer:

If I remember correctly... if you open an existing XLS and hit CTRL-END, the cursor will jump to the last row of data (or row below it). So I know the program keeps track of it. I've just never done this by OLE.

The trick is just to record an Excel macro to see what the OLE code looks like, then code equivalant code into your WinBatch program.

Take a sample spreadsheet, then open it, use the RECORD A NEW MACRO, then hit CTRL-END. End the recording and EDIT the macro.

You should see:

ActiveCell.SpecialCells(xlLastCell).Select

Note: the constant xlLastCell equals 11.

As of 2004A, WinBatch can only execute single dotted OLE statements. Therefore the WInBatch line would look like this

objSpecialCell = ActiveCell.SpecialCells(11)
objSpecialCell.Select

If you want the address of the last row you can use:

objSpecialCell = ActiveCell.SpecialCells(11)
Address = objSpecialCell.Select

and you see something like: $B$20

objSpecialCell = ActiveCell.SpecialCells(11)
Row = objSpecialCell.Row

and you'll see: 20

Obviously you want to jump to row 21...the rest should be easy.

Alternatively, this also works real well (where oXL = Application Object and oWS = Active WorkSheet )

xlUp = -4162
oRange = oWS.Range("A65535")
oEnd = oRange.End(xlUp)
oEnd.Select()
objActCell = oXL.ActiveCell
LastRow = objActCell.Row +1

Here is a full example:


;   Excel Last Row Script 

xlsFilename = AskLine("Excel OLE Example", "Enter the Path & Filename", "C:\test\MyExcelFile.xls")
WorkSheet = AskLine ("Last Row Example", "Please enter the name of the Worksheet" , "Sheet1")
Excel = ObjectOpen("Excel.Application")
Excel.visible = @TRUE
Workbooks = Excel.Workbooks
Workbooks.Open(xlsFilename)

CurrentWorkbook = Workbooks.item(1)
Worksheets = CurrentWorkbook.Worksheets

ActiveSheet = Worksheets.item(WorkSheet)  

xlDown = -4121
MyRange = ActiveSheet.Rows("10:15")
MyRange.Insert(::SHIFT=xlDown)
ObjectClose(MyRange)
ObjectClose(ActiveSheet)

CurrentWorkbook.Save()

ObjectClose(CurrentWorkbook)
ObjectClose(Workbooks)

Excel.quit
ObjectClose(Excel)
exit

Article ID:   W16623

File Created: 2005:02:18:12:21:38

Last Updated: 2005:02:18:12:21:38

Database Search

TechHome