EXCEL direct read/write

[TJNweb]

Hello,

Does anybody worked with Excel directly (no DDE/OLE). I am sure that somebody allredy has EXCEL engine for fast read/write xls files..
I will be gratefull for any links and examples (raw description of xls format is not enought..)

tjn.web@mail.ee

 

[johnwm]

I just add a Project/Reference to the Excel library. You can then Open the application, open workbooks, access worksheets and cells directly through the Excel object model

Dim appXL As Excel.Application
Dim wbXL As Excel.Workbook
Dim wsXL as Excel.Worksheet
Dim rngXL As Excel.Range
On Error Resume Next
Set appXL = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then
Set appWorld = CreateObject(&quot;Excel.Application&quot
End If
Err.Clear
On Error GoTo 0
Set wbXL = appWorld.Workbooks.Open(App.Path & &quot;\calcs.xls&quot
Set wsXL = wbXL.Sheets(&quot;Sheet1&quot
Set rngXL = wsXL.Range(&quot;a1&quot;, &quot;b90&quot
' do whatever here
Set shtXL = Nothing
Set rngXL = Nothing
Dim W As Workbook
For Each W In Workbooks
W.Close savechanges:=True
Next W
Set wbXL = Nothing
Set appXL = Nothing


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[TJNweb]

But I want to use direct writing because I need to put about 100000 records at once. As you can see it will take about few day to complete this operation...
I need to find fastest way to do that.
Anyway, thanks for example.

 

[johnwm]

Sorry I misunderstood - I thought you wanted to do things in Excel.

If it's just to use Excel as a database look up Excel ODBC driver!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[TJNweb]

Hmm..
It is good idea, but will I able to send command to format cells (merge, change font size)??

 

[Strannik]

Surely not. If you use Microsoft Jet to access Excel sheets, you may only view/modify cells values.

 

[johnwm]

Sorry I'm out of ideas. The methods above have always worked for me. If you just want to transfer data use ODBC - if you want to do stuff use Excel Object Model

The only other suggestion, which you have already rejected, is to look at directly reading and modifying an xls file. If you change your mind, you can get a comprehensive description of the xls file structure here:

http://www.wotsit.org/search.asp?page=2&s=database

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[johnwm]

I note in your second post that you need to put 100000 records at once - this may cause a problem - as far as I know Excel has a maximum of 65535 rows per worksheet.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[TJNweb]

Johnwm, thanks for your help,
Don't be angry with me, I am asking not to reject all suggestions, I am just looking for optimal solution..
I want to use more then one row (65535*256) * sheets = 10000000... cells

Actually I am looking for ref. to complete XLS engine if so exist.

 

[Strannik]

If it's not a problem can you tell why have you selected Excel worksheet as data storage ? I think there're a lot of other means to do that ...

 

[johnwm]

Not angry, just don't know what to suggest. What exactly are you trying to achieve? Are you using Excel functionality for actual inter-related calculations? Or for visual layout? or mainly data storage? There may be other approaches that we can suggest
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[SemperFiDownUnda]

What you are asking to do is a basic excel writing engine. As I know it there isn't such a beast.

Have you thought about putting your code into Excel instead of running via com in a vb program?

also &quot;100000&quot; and (65535*256) * sheets = 10000000
is very different
that is ALOT of data and it is going to be kinda slow
On my pittiful work machine (PIII 666mhz with 512 ram)
I can get, from vb ~23,000 cells values updated every minute
now for your initial requirement of 100,000 cells that is about 5 minutes (note we have no idea what cell formating you want to do but this would increase the amount of time it takes, for example just bolding the cell take more time then putting a value in it.)

take this code
[/code]
Dim xls As Excel.Worksheet
Dim rng As Excel.Range
xl.Interactive = False

Dim X As Long
Dim Y As Long
Dim Z As Long
Dim a As Long
For Z = 1 To 3
Set xls = application.workbook(1).Worksheets(Z)
xls.visible = false
Set rng = xls.Cells
For Y = 1 To 500
For X = 1 To 255
a = (X + Y) * Z
rng(Y, X).Value = a
rng(Y, X).Font.Bold = True
Next
Next
xls.visible = true
Next
me.SaveAs &quot;C:\test3.xls&quot;
me.application.Interactive = True
[/code]

you and run it in VB and native in Excel you'll find Excel is ~25 times faster

so your talking ~250k cells a minute apposed to
compaired to ~10k cells a minute

even then if you are talking 10,000,000 cells you still need ~40 minutes to do the writing (agian dependant on what formating you want done.)

Lengthy operations like this are best done batch and done on high end workstations.

I would be surprised to find anything out there that is as fast as you want. Why? Because if it was that fast don't you think Microsoft would have bought them out by now? 8)

 

[TJNweb]

I completed converter GFA->XLS (like vector graphic (report) - 1C bookkeeper program). I have used Excel object model. I have done program, but person who asked me to write it sad that it is too slowly !
Do something to increase speed !! I am looking...

 

[SemperFiDownUnda]

Tell him/her to get a duel AMD Athlon 2400+ MP with 4 gig of ram and a raid 0 array with seven 10000 RPM drives.

Really if you don't concider pushing the code over to Excel I don't see how you are going to get much better performance, assuming you've got good code to begin with.

When speed is a problem I tend to try a few things
1) buffer the user from the speed impact.
a) run reports batch at night
b) run reports on another machine async then notify app when done.
c) run reports locally but within another process and notify app when done.
2) Make sure the user sees an indication on about how long the operation is going to take
3) Allow user to do other things while request is being serviced (See 1.b, 1.c above)

Sorry if you don't concider moving the code an option but I don't believe the option you are hanging out for (a magic bit of code that will let you have all the functionality of the Excel library just many times faster.)

 

[TJNweb]

Thanks for everything, friends,
Yeh, everything has borders of reality and one solution here it is working with less data size.

Happy New YEAR !!!