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 doesn't seem to paste actual "text" values but instead an "object" that the user can't do things to like sum once the workbook is open.

 

two...

 

SaveAs Office2000 = 11 Arguments

SaveAs Office2003 = 12 Arguments

 

I've worked with third party tools such as Infragistics that are able to accomplish this so i know there's a way. I just don't know what it is!

[barski]

well i could'nt 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 here's the link

 

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

[Mike_R]

Hi Barski,

 

Sorry, I missed this thread... I don't 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 didn't understand your quote here:

well i could'nt 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 I'm 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 can't 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 I'm confused:

 

A number is not a number"

I don't know what you mean here?

 

 

 

The user can't 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 I'm pretty darned certain that you understand all that... So I must be missing something. :(

 

Sorry if I'm 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 doesn't return 80.40 it returns nothing.

[Mike_R]

That's 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?