Export to Excel crashes app

General
1

Hi,
When built for Windows my application can export the contents of a listbox to spreadsheet so that the user can manipulate the results elsewhere.

However, I have a problem that if the user clicks into the spreadsheet at all while it’s getting populated, the export stops and the application crashes. If I’m running in debug mode it still crashes, so doesn’t show me if there is an issue with my code.

I have here some simplified code that still crashes if the user changes anything with the Excel application.

Dim excel As New ExcelApplication
    Dim book As ExcelWorkbook
    Dim sheet As ExcelWorksheet
    dim alphabet() as String = Array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")
    
    excel.Visible = True
    book = excel.Workbooks.Add
    
    dim columnname as String
    dim i2, i2total as Integer
    dim i, itotal as Integer
    i2total = columncount-1
    itotal = rowcount-1
    excel.ActiveSheet.Name = header
    
    
    for i = 1 to itotal
      for i2 = 1 to i2total
        columnname = alphabet(i2-1)
        excel.Range(columnname + Str(i), columnname + Str(i)).Value = returncell(i-1, i2-1)
        //return cell is a method that returns the text in the cell, I use this because I've written my own listbox to speed things up.
        
      next
    next

Exception err as OLEException
  MsgBox err.message

How can I get my application to stop crashing when this happens? The code never gets to the OLEException block at the end.

Regards,
Josh

2

Use this ole parameter:
dim param as new oleparameter
Add a new array with all of your values
dim MyArray as variant
Put the contents of your array into excel range with one command
param.ValueArray = MyArray
dim r as execlrange
r = excel.range(“A1”, “H45”)
r.value2 = param

3

Hi Joshua,

Horst’s example looks like it would work. Here is a modified version of the supplied code example that works. I put some real numbers in to see if there were any errors.

[code] Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
Dim sheet As ExcelWorksheet
dim alphabet() as String = Array(“A”,“B”,“C”,“D”,“E”,“F”,“G”,“H”,“I”,“J”,“K”,“L”,“M”,“N”,“O”,“P”,“Q”,“R”,“S”,“T”,“U”,“V”,“W”,“X”,“Y”,“Z”)

excel.Visible = True
book = excel.Workbooks.Add

dim columnname as String
dim i2, i2total as Integer
dim i, itotal as Integer
i2total = 26
itotal = 26
excel.ActiveSheet.Name = “A Header”

for i = 1 to itotal
for i2 = 1 to i2total
columnname = alphabet(i2-1)
excel.Range(“A” + Str(i), “B” + Str(i)).Value = 5
//return cell is a method that returns the text in the cell, I use this because I’ve written my own listbox to speed things up.
next
next

Exception err as OLEException
MsgBox err.message[/code]

Maybe check the values that are being used. As an example, check to make sure the cell values are all positive, (msgbox may help here), and check for the returncell value.

The logic and code seems to work, just not sure what the actual values are being added.

I hope this helps :slight_smile:

4

Hi Horst and Eugene,
Thanks very much to both of you for your responses!

I’ve applied your response Horst and it seems to be doing the job a lot better thanks. It is certainly a lot faster!

The only thing that I’ve had to change is to add the line…

param.Type = OLEParameter.ParamTypeString

just above the line…

param.ValueArray = myarray

The application still crashes if the user tries to use excel while it’s still being populated, however the population happens much faster now so is much less likely to cause a crash.

Thanks again,
Regards,
Josh

5

Here is my code to write the contents of an array to an XLS file (Excel 2007 on Window7). I have set the parameter excel.visible = true.

#####################################################################################
dim excel as new excelapplication
dim param as new oleparameter
dim paramHead as new oleparameter
Dim book As ExcelWorkbook
dim r as excelrange
dim XLSarrayX as integer = 11
dim XLSarrayY as integer = HostCount
dim XLSarray(-1, -1) as Variant
redim XLSarray(XLSarrayY, XLSarrayX)
dim MyCell as string
dim Headline(11) as Variant
Headline(0) = “Hostname”
Headline(1) = “Type”
Headline(2) = “Location”
Headline(3) = “Rack”
Headline(4) = “SC0”
Headline(5) = “SC1”
Headline(6) = “Serial No.”
Headline(7) = “Change Date”
Headline(8) = “Changed by”
Headline(9) = “OS Type”
Headline(10) = “Remark”
Headline(11) = “City”

for m as Integer = 0 to 11
XLSarray(0,m) = Headline(m)
next

param.type = oleparameter.ParamTypeString
excel.visible = true

book = excel.Workbooks.Add
book.Sheets(1).Activate
excel.ActiveSheet.Name = “Hostliste”

’ Headline
param.ValueArray = Headline // XLSarray
MyCell = “L1”
r = excel.range(“A1”, MyCell)
r.value2 = param
’ Headline in Bold
'excel.range(“A1”, MyCell).Font.FontStyle = “Bold”
excel.range(“A1”, MyCell).font.bold=true
’ Headline Fontsize 8 Points
excel.range(“A1”, MyCell).Font.Size =“8”
’ this will change the text colour of the cell or range of cells
excel.range(“A1”, MyCell).font.colorIndex=6
’ this will change the background colour of a cell or range of cells
excel.range(“A1”,MyCell).interior.ColorIndex=25
’ activate Autofilter
excel.range(“A1”,MyCell).AutoFilter

excel.ActiveWorkbook.Author = “Horst Jehle”
excel.ActiveWorkbook.Title = “Hostlist”
excel.ActiveWorkbook.Subject = “Hostlist”
dim MyDateString as string
MyDateString = ConvertSQLDate(cstr(app.BuildDate))
excel.ActiveWorkbook.Comments = "Created with " + app.ShortVersion + " - Version: " + cstr(app.MajorVersion) + “.” + cstr(app.MinorVersion) + “.” + cstr(app.BugVersion) + “.” + cstr(app.NonReleaseVersion) + " Builddate: " + MyDateString // + “.” + cstr(app.BuildDate.Month) + “.” + cstr(app.BuildDate.Year)
excel.ActiveWorkbook.Keywords = “Hostnames, Unix, Sun, Oracle, Linux”

// Put the contents of your array to the excel range
param.ValueArray = ExportData
XLSMaxLines = zeile+1
MyCell = “L” + str(XLSMaxLines)
r = excel.range(“A2”, MyCell)
r.value2 = param

// All rows with AutoFit
excel.Cells.EntireColumn.AutoFit
excel.Rows(“2:2”).Select_
excel.ActiveWindow.FreezePanes = True
#####################################################################################

Now you should check on which line the Excel application chrashes. Set a break point on each line and walk thru you code step by step. You can also make a test with my example and put only the headline into the Excel workbook.

6

Hi Joshua,

One possible option to prevent Excel from crashing when the user clicks on the Excel program is to set

excel.visible = false

at the beginning of the code and then set

excel.visible = true

at the end of the code when all the data has been sent to excel. With all of the data placed in Excel, then the program will not give an error.

7

What i did for my export to excel feature is simply create a csv file or the other way is to create a XLS file but in html format.