Navigating Excel with Automation

I am needing to export a report to excel using Automation. The problem is that the number of columns is dynamic so I can’t call cells statically, almost all cell references have to be dynamic. This creates problems as I try to populate a row; I was using Sendkeys function to send {Tab} as many times as needed, but this creates problems when multiple instances of excel is open. Is there a way of referencing an adjacent cell - specifially the cell to the left of the active cell?

I finally found a way of doing it, who would have thought it would have taken so long to figure out such a simple task. For those of you who want to do a similar thing but don’t know how here is what I did. I selected the first cell - the one I knew - and then used “xlApp.ActiveCell.Offset(‘No of rows’,‘No of Columns’)” to move my selection.

I do have another question though; how would I make the excel window come to the front of other windows. I’be played around with setting the Window State but it didn’t seem to work. It’s only an annoyance when excel is minimized when the report is run, otherwise excel pops up automatically.

Beware of COM automation drawbacks. If you have small workbooks you can use our GemBox.Spreadsheet Free Excel component for XLS/CSV/XLSX reading/writing/reporting.