For some reason this code fails when i try and write past row 65536 in excel. The code creates an excel 12 workbook, and when i open it, it has over 1 million rows in it..
I even tried similar code within excel and that works fine. I have excel12.0 object library referenced..
Anyone know how to fix this?
Code:
Sub TestWriteExcel()
On Error GoTo PROC_ERR
Dim app As Excel.Application, wb As Workbook, ws As Worksheet
Set app = New Excel.Application
Set wb = app.Workbooks.Add
wb.SaveAs "U:\Data\CEF\test", xlExcel12
DoEvents
Set ws = wb.Worksheets.Add
ws.Name = "test"
ws.Cells(65536, 1).Value = 1
ws.Cells(65537, 1).Value = 2
wb.Save
PROC_EXIT:
On Error Resume Next
wb.Close
app.Quit
Set ws = Nothing
Set wb = Nothing
Set app = Nothing
Exit Sub
PROC_ERR:
Debug.Print Err.Number & vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
I even tried similar code within excel and that works fine. I have excel12.0 object library referenced..
Anyone know how to fix this?