Solved: Formatting Excel

[Imdabaum]

I am exporting a recordset into Excel and emailing it to customers from Access. Everything works but I have an issue setting the formatting however.

If I use the following, it works fine.
objWorkbook.ActiveSheet.ListObjects.Add(xlSrcRange, objWorksheet.Range("A1:I6"), , xlYes).Name = _
"Table1"
objWorkbook.ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"

But as you can expect not every customer has the same number of records. So I tried this little snippet that I found. But it doesn't work out.

objWorkbook.ActiveSheet.ListObjects.Add(xlSrcRange, objWorksheet.UsedRange, xlYes).Name = _
"Table1"
objWorkbook.ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"

Does the .UsedRange not work within Access even if you are operating on an Excel object? Any other ideas on how to dynamically get the range of the worksheet?

[hansup]

Any other ideas on how to dynamically get the range of the worksheet?

You know the worksheet starting row number where you insert the data, right? If so, from Access, determine the number of rows you're inserting, adding that to the start row# and subtracting 1 should give you the last worksheet row where you added data.

If that doesn't work, you could scan through the first column to find the row where the cell value is Null.

[Imdabaum]

Not sure what I did, code looks the same, but I closed for a meeting and when I came back, it works. I think I had tied up the Excel object between testing and implementing the dynamic Range functionality.

[Imdabaum]

You know the worksheet starting row number where you insert the data, right? If so, from Access, determine the number of rows you're inserting, adding that to the start row# and subtracting 1 should give you the last worksheet row where you added data.

If that doesn't work, you could scan through the first column to find the row where the cell value is Null.

Wow... that would have been an obvious way to handle it. Wish I would have thought of that.

Thanks for the tip. It will be a nice one to use in the future. Obviously since I have the recordset, I have a RecordCount which would be the last row of my table in Excel.

For all intensive purposes, the worksheet.UsedRange is working though.