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
[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:
- Select button: Controls → Change Channel
so it shall be written, so it shall be done…