winxmun
08-13-2009, 11:16 PM
Hi,
I've a VBA code that uses MoveLast & I hit error after running the VBA. Then I realised that it is due to empty record. From the Access help file, it mentioned as follow:
"A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error."
Is there a solution for this? :doh:
My VBA code as follow:
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NewBorr_SIBSAcNonBlank", dbOpenSnapshot)
rst.MoveLast
intRow = rst.RecordCount
rst.MoveFirst
initRowExcel = 3
x = 1
y = initRowExcel
avarRecords = rst.GetRows(intRow)
intRow = UBound(avarRecords, 2) + 1
intCol = UBound(avarRecords, 1) + 1
'-------------------open excel----------------------------------------
Set appXL = CreateObject("excel.application")
appXL.Visible = True
Set wbk = GetObject("D:\TFL_Weekly_Rpt.xls")
Set wbs1 = wbk.Worksheets(1)
wbk.Parent.Windows(1).Visible = True
For intRow = 0 To intRow - 1
For intCol = 0 To intCol - 1
wbs1.Cells(y, x).Value = avarRecords(intCol, intRow)
x = x + 1
Next intCol
x = 1
y = y + 1
Next intRow
rst.Close
I've a VBA code that uses MoveLast & I hit error after running the VBA. Then I realised that it is due to empty record. From the Access help file, it mentioned as follow:
"A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error."
Is there a solution for this? :doh:
My VBA code as follow:
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NewBorr_SIBSAcNonBlank", dbOpenSnapshot)
rst.MoveLast
intRow = rst.RecordCount
rst.MoveFirst
initRowExcel = 3
x = 1
y = initRowExcel
avarRecords = rst.GetRows(intRow)
intRow = UBound(avarRecords, 2) + 1
intCol = UBound(avarRecords, 1) + 1
'-------------------open excel----------------------------------------
Set appXL = CreateObject("excel.application")
appXL.Visible = True
Set wbk = GetObject("D:\TFL_Weekly_Rpt.xls")
Set wbs1 = wbk.Worksheets(1)
wbk.Parent.Windows(1).Visible = True
For intRow = 0 To intRow - 1
For intCol = 0 To intCol - 1
wbs1.Cells(y, x).Value = avarRecords(intCol, intRow)
x = x + 1
Next intCol
x = 1
y = y + 1
Next intRow
rst.Close