office automation turn off calculation?

of course, i will get it all together and post it here. give me a couple of hours.

ok, well…

i created a class called excelVariantArray. this has a property which is a 2D array defined as data(-1,-1)

i then created a method called add (because the append array function doesn’t work on multi-dimentional arrays)

this just redims the array by adding a row and puts the data in it like this :-

dim rows as integer = Ubound(data,1)

redim data(rows+1,2)

data(rows+1,0) = value

then i get a recordset from the database and the code is below

as you can see, everything is still strings but for some reason it works properly.
i intend to make it so that i can pass in a workbook,sheet,row and column to start at
so that i can update a sheet rather than create a new one, for where people have
analysis sheets, and just need to get new data.

hope that helps, let me know if you need anything else

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).add 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 +1,column)).Value = columndata(column).data
next

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

end if

Exception err as OLEException
MsgBox err.message

Thanks Russ, i will give it a try tonight.

Russ,

Thanks for the input :slight_smile:

[quote]i intend to make it so that i can pass in a workbook,sheet,row and column to start at
so that i can update a sheet rather than create a new one, for where people have
analysis sheets, and just need to get new data.[/quote]

One powerful tool in Excel is that we can create a template file with all the necessary details and a defined data range. Whenever we open it a new copy of the template is created. The benefit of having a template override the time to make it.

Let me know if You have some interest in this.

Hi Dennis,

my users are not really very technical, i tried to get them to use the microsoft query in Excel and it failed for being too complex.
so i decided to get my tool to ask them a few parameters and then populate a sheet (either predefined) or a new book.

Should this thread not be in targets --> Windows

probably. Im, not sure how to move it though.

When you are author of the channel:

  1. Select button: Controls → Change Channel

so it shall be written, so it shall be done…