Exporting to Excel A A Couple of issues

[barski]

one...
A year or so ago i with the help of this forum, mike r specifically, i found the way to make exports to excel run much, much faster using arrays to "paste" the data into excel. Which does work great however it doesnt seem to paste actual "text" values but instead an "object" that the user cant do things to like sum once the workbook is open.

two...

SaveAs Office2000 = 11 Arguments
SaveAs Office2003 = 12 Arguments

Ive worked with third party tools such as Infragistics that are able to accomplish this so i know theres a way. I just dont know what it is!

 

[barski]

well i couldnt figure a way to keep the "data types" without doing it out of process, which makes it Sloooooooow. so i went with using a dataset to "talk" to excel. this also got rid of the need for the varying methods between different versions of excel for things like saveas. for anyone interested heres the link

http://support.microsoft.com/?id=316934#12

 

[Mike_R]

Hi Barski,

Sorry, I missed this thread... I dont understand the problem?

(1) The first issue seems to be compatibility across more than one Excel version. (Excel 2000 vs. Excel 2003.) If you want one set of code, you obviously need to avoid using the extra paramters available for the newer version. Binding to the lowest version probably works (it definately does in VB 6.0), but worst case, you could use one set of code, compile to, say Excel 11.0 (calling it "MyExcel11.dll") and then change the references to Excel 10.0, change and then compile, calling it, say, "MyExcel10.dll". That would DEFINATELY work 100%.

(2) I didnt understand your quote here:

well i couldnt figure a way to keep the "data types" without doing it out of process...

So this was a DLL, but you needed to do it via Automation? Or by "out of process" you mean using ADO?

Tell us a bit more about what you are trying to do here, something tells me that your original goal can be done, but Im not sure 100% what you are trying to do! Can you show us a code example?

(Nice link on ADO.NET with Excel and VB.NET, btw.)

,
Mike

 

[barski]

what i mean by "out of process" is if i populate an excel worksheet by looping through my data and inserting the data into excel with something like this

xlSheet1.Cells[i,1] = data;

then it runs really really really sloooow

however it i do something like this

xlRng = xlSheet.get_Range(xlSheet.Cells[1,1],xlSheet.Cells[myArray.GetLength(0),myArray.GetLength(1)]);
xlRng.Value = myArray;

it is really really fast but when you open the workbook a number is not number. the user cant do things like add or subtract the values in that cell.

 

[Mike_R]

Ok, so you open the Workbook and then load an Array of data into the Range. So far so good...

Then Im confused:

A number is not a number"

I dont know what you mean here?

The user cant do things like add or subtract the values in that cell.

Why not? Is the sheet protected?

If you want the user to be able to edit the cells, then make the Application, the Workbook and the Worsheet visible and the Worksheet unprotected (or at least the cells in question unlocked). If you wish to prevent the user from editing the cells, then hide the Application, the Workbook and/or the Worksheet or Protect the Worksheet and lock the cells in quetion.

But Im pretty darned certain that you understand all that... So I must be missing something.

Sorry if Im dense, if you can somehow get this through my thick skull, I can probably help...

Mike

 

[barski]

when the user opens an exported workbook similar too...

Employee PayRate
John 10.00
Mary 30.15
Steve 40.25

and they want to sum pay rate so they click sum and select the payrate amounts it doesnt return 80.40 it returns nothing.

 

[Mike_R]

Thats strange...

Do you want to attach a sample Workbook that has this property? And/or show some code that I could run to create this?