Opening excel AFTER(!!!) export to it...

Wirloff

Member
Joined
Mar 2, 2005
Messages
19
Location
Belgium
Programming Experience
1-3
Hi,
in my application i'm transferring data from MS Access into pre-made Excel templates using datasets. I'm using the following code to start the excel application

VB.NET:
[size=2]objExcel = [/size][size=2][color=#0000ff]New[/color][/size][size=2] Excel.Application[/size]
[size=2][size=2]objExcel.Workbooks.Open(path)

objWorkSheet1 = objExcel.Worksheets(sheet)

objWorkSheet1.Select()

[/size][/size]

than I can start filling up the templates with
objExcel.cells(theRowIndex, theColIndex) = theValue

But this works very slowly. He has to jump from one to another active cell in the open excelfile. And if somebody clicks in the excel file while filling it up, it crashes.
My question is: is it possible to fill up the excel file in the background without allready having to open the excel file physically. And showing all of it to the user when done with importing. I hope that this would work faster...
Thx.
 

Administrator

VB.NET Forum Admin
Joined
Jun 3, 2004
Messages
1,461
Programming Experience
10+
You could probably do an export to a CSV format then execute it or open it directly using the System.Diagnostics.Process.Start method.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,345
Location
Sydney, Australia
Programming Experience
10+
You can access an Excel workbook using ADO.NET, which is much quicker than opening an instance of Excel. To do this, your connection string will look something like:

"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""<file name>"";Extended Properties=""Excel 8.0"""

The "Extended Properties" part can contain other information, like whether there is a header row (HDR=YES or HDR=NO). A select query would look something like this:

"SELECT * FROM [<sheet name>$<first column label><first row number>:<last column label><last row number>]"

I've never used ADO.NET to update a workbook, but the principles are the same. A search through Excel help in the application or the MSDN library on the Microsoft web site should yield the information you need. That's where I found the above info when I needed it.
 

cliffd64

Member
Joined
Jun 7, 2005
Messages
13
Programming Experience
5-10
Wirloff said:
Hi,
in my application i'm transferring data from MS Access into pre-made Excel templates using datasets. I'm using the following code to start the excel application

VB.NET:
[size=2]objExcel = [/size][size=2][color=#0000ff]New[/color][/size][size=2] Excel.Application[/size]
[size=2][size=2]objExcel.Workbooks.Open(path)
 
objWorkSheet1 = objExcel.Worksheets(sheet)
 
objWorkSheet1.Select()
 
[/size][/size]

than I can start filling up the templates with
objExcel.cells(theRowIndex, theColIndex) = theValue

But this works very slowly. He has to jump from one to another active cell in the open excelfile. And if somebody clicks in the excel file while filling it up, it crashes.
My question is: is it possible to fill up the excel file in the background without allready having to open the excel file physically. And showing all of it to the user when done with importing. I hope that this would work faster...
Thx.

If you make the excel application visible property = False, until you have finished loading the data, there is no way the user can see it.

As soon as you finish loading all your cells, make the excel app visible like this:

VB.NET:
		' view the spread sheet
		objExcel.Visible = True
 
Top Bottom