AccessIsFun - -call me stupid, but I dont understand. 1

[missprogrammer]

sorry, should of been more detailed. I am pasting my results of several recordsets into excel using vba. My thing is I wont know how may records exist in each recordset. So I can I find the last used cell or fist empty cell.




AccessIsFun (Programmer) Nov 19, 2001
You could count the recordsets before you paste them. Then set your next starting point at RecordCount (of the first rescordset) + 2 (Excel starts at one, and if you're putting headers in, then you'll need to account for 2 spaces to make sure that you don't overlap).

Obviously adjusting for your next recordsets e.g. Starting point 3 = RecordCount1 + RecordCount2 + 1 et cetera

Dim RecCnt as Integer, rst as RecordSet
Set rst = CurrentdB.OpenRecordset ("[TableName]&quot
RecCnt = rst.RecordCount

Hope this helps

Kyle

 

[pcdaveh]

Public lRealLastRow As Long
Public lRealLastColumn As Long


lRealLastRow = 0
lRealLastColumn = 0
Range("A1&quot.Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1&quot, xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1&quot, xlFormulas, , xlByColumns, xlPrevious).Column
Cells(lRealLastRow, lRealLastColumn).Select

 

[Mossoft]

Try.....
Row number of last cell used:

<Sheet object>.range(<column to check>).specialcells(xlcelltypelastcell).row

e.g.

thisworkbook.ActiveSheet.range(&quot;A:A&quot.specialcells(xlcelltypelastcell).row

M

 

[MichaelRed]

perhaps you can avoid the issue by joining the recordsets in Ms. Access by use of a UNION query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[KyleS]

Very good point Michael. If she joined them all before exporting, then when to append wouldn't be an issue at all.

 

[missprogrammer]

OKAY GUYS GOT YOU THANKS ALOT.