office automation turn off calculation?

Hi,

I’m adding data to excel on windows. its so slow!
i think its because excel is recalculating everything after every insert
i know in excel you can turn the calculation off but i can’t see how to do this from xojo.

anyone got any ideas?

Wow, we were just discussing this.

I don’t think you can, but are you adding your data a cell at a time? If so, try doing it a row at a time instead.

hi,

i have managed to get the rows to add, but the numeric columns are all text and end up with a green triangle n the cell
any idea how to change the format of a column?

i tried activesheet.column(i).numberformat = “0000,00”
but it does nothing, not even an exception

Can you post the code you’re using to set the values of the row?

Hi Russ,

Good question. Here is how to turn Excel calculations to manual (off):

[code] Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = True

excel.Application.Calculation = Office.xlManual[/code]

To turn the calculation back on, use this code:

excel.Application.Calculation = Office.xlAutomatic

To calculate automatically except for data tables, use this code:

excel.Application.Calculation = Office.xlSemiautomatic

Could you let me know if this helps increase the speed of Excel on your application?

Another tip to increase the speed of putting large amounts of data in excel is to have visible = false until all the data is added. This works for some and not for others.

Thanks :slight_smile:

Hi Kem,

the code I’m using is below. i found that if i didn’t add the line

param.type = oleparameter.ParamTypeString

nothing was added to the sheet. of course this is the reason everything is text. but i can’t see how to do
it otherwise.

I did think maybe i would add the data as column ranges instead of rows, taking account of the field type in the recordset.
but that might end up with some very large arrays Vs. lots of small arrays. (not sure of the real world difference)

if rs <> nil then

Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
Dim sheet As ExcelWorksheet

excel.Visible = false
book = excel.Workbooks.Add
'excel.ActiveSheet.Name = "Sheet1"


dim param as new oleparameter
param.type = oleparameter.ParamTypeString
dim RowArray() as variant

for column as integer = 1 to rs.FieldCount -1
  Rowarray.Append  rs.IdxField(column).Name
next

//Put the contents of your array into excel range with one command
param.ValueArray = RowArray
dim r as excelrange

r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(startrow,rs.FieldCount-1))
r.value2 = param

// Make the headers Bold
r.Font.Bold = true

dim row as integer = startrow +1
while not rs.eof
  redim RowArray(-1)
  
  for column as integer = 1 to rs.fieldcount -1
    Row.Append  rs.IdxField(column).StringValue
  next
  
  param.ValueArray = RowArray
  r = excel.range(excel.activesheet.cells(row,StartColumn),excel.activesheet.cells(row,rs.fieldcount-1))
  r.value2 = param
  
  
  rs.MoveNext
  row = row + 1
wend

Hi Eugene,

thats great! i will try that too.
i found that adding by rows is very fast over doing it cell-by-cell
but i feel turning off calculation as well as visible=false will be the
whole answer to making it really fast. (faster the better)

Hi Russ,

I modified a few parts of the code and this code takes about 17 seconds on my Windows 8.1 machine. It creates 9 columns of string data, and adds 1000 integer values for each of the nine rows, for a total of 9000 data points. Not sure how many data points you are sending to Excel. I turned off a few things in excel to increase the speed and then turned them back on again at the end.

[code] Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = “MySheet”

Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString

'Add 10 strings to array
Dim RowArray() as Variant
Dim i as Integer
For i = 1 to 10
RowArray.Append “Header:” + CStr(i)
Next i

param1.ValueArray = RowArray
Dim r as ExcelRange
Dim StartRow, StartColumn as integer
StartRow = 1
StartColumn = 1

r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True

'Add data in a column array
Dim ColumnArray() as Integer
For i = 1 to 1000
ColumnArray.Append i
Next i

For i = 0 to UBound(ColumnArray)
excel.Range(“A2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“B2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“C2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“D2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“E2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“F2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“G2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“H2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“I2”).Offset(i,0).Value = ColumnArray(i)
Next i
excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized

MsgBox “Done”[/code]

Hi Russ,

I tried code with a different OLEParameter, and was not able to make it work either. :frowning:

param.type = oleparameter.ParamTypeString

I’ll try a few more things and will give you an update if I am successful. :slight_smile:

Hi guys,

Eugene’s code where it starts to add data to each cell can actually be speeded up more like:

excel.range("A2:I1000").value=ColumnArray()

How faster would that be compared with 17 second, Eugene?

Hi Dennis,

Thanks for the code and it made a large difference in execution time! Less than 1 second.

Russ, how long does it take to pull the information from the database?

Here is the modified code with Dennis’ suggestion.

[code] Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = “MySheet”

Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString

'Add 10 strings to array
Dim RowArray() as Variant
Dim i as Integer
For i = 1 to 10
RowArray.Append “Header:” + CStr(i)
Next i

param1.ValueArray = RowArray
Dim r as ExcelRange
Dim StartRow, StartColumn as integer
StartRow = 1
StartColumn = 1

r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True

'Add data in a column array
Dim ColumnArray() as Integer
For i = 1 to 1000
ColumnArray.Append i
Next i

'Put array info into Excel
excel.Range(“A2:I1000”).Value = ColumnArray()

excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized[/code]

Dennis,

Thanks for the code to pass an array. All I seem to be able to send to the excel file is the number 1. It is definitely fast, and how is the string data passed to Excel?

Thanks.

[code] Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = True

'Make an array of 100
Dim ColumnArray() as String
Dim i as Integer
For i = 1 to 100
ColumnArray.Append(Cstr(i))
Next i

'Put array in first 100 rows - only the number 1
excel.Range(“A1:A100”).Value = ColumnArray
[/code]

hi, Sorry i forgot to follow the thread :slight_smile:

this code seems to put the first value in the array in every row or am i missing something?

Russ,

You’re not missing anything. In order to show the values the array need to be transposed.

It would help if You can provide either the code in use to grab the data from the database or an example. Are You using ADO?

The following code snippet may help but it’s subject to the code in use:

'Put array info into Excel excel.Range("A2:A1001").Value = excel.workWorksheetFunction.transpose(ColumnArray())

Hi Dennis.

well this is my code.
i tried = excel.worksheetfunction.transpose() and i got no errors, but no data either

if rs <> nil then

Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = "MySheet"

Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString

dim i as integer




'Add 10 strings to array
Dim RowArray() as Variant

for column as integer = 1 to rs.FieldCount -1
  rowarray.Append  rs.IdxField(column).Name
next

//Put the contents of your array into excel range with one command
param1.ValueArray = rowArray
dim r as excelrange
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(startrow,rowarray.ubound))
r.value2 = param1

// Make the headers Bold
r.Font.Bold = true

param1.ValueArray = RowArray

r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True



dim columnData() as ExcelVariantArray
for i = 1 to rs.FieldCount
  dim va as new ExcelVariantArray
  columnData.Append va
next


dim rowCount as integer
while not rs.eof
  
  for column as integer = 1 to rs.FieldCount -1
    columndata(column).data.append rs.IdxField(column).StringValue
  next
  rowcount = rowcount+1
  rs.MoveNext
  
wend



'Put array info into Excel
for column as integer = 1 to rs.FieldCount -1
  
  excel.Range(excel.activesheet.cells(2,column),excel.activesheet.cells(rowcount,column)).Value = columndata(column).data()
  
next


excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized

end if

oh, its a recordset object from an odbcDatabase.

Thanks Russ. I will take a look on it. Can’t promise that I can get back to You today but asap.

Hi Dennis, I have the answer!!!

basically you cannot assign a 1D array to a column,
even if you only want a single column in excel, the array must be 2D
then it works perfectly, fills thousands of rows in next to no time

I’m very happy now. thanks for your help!

Russ,

Great news!

Yes, that’s true and therefore I used the Transpose function,

Could You have the kindness to provide the code solution?

Hi Russ,

Are you willing to share the solution for filling a range of cells, i get the next error:Unknown name, (failed on “ValueArray2D”)

TIA,
Andre