Importing Excel using Visual Basic

[sineados]

Hi,

i want to import an excel spreadsheet using Visual Basic. I have done it in VBA using the docmd.transferspreadsheet but I cant seem to find an easy way of doing it using vb.

anyone any ideas.
thanks,

 

[sineados]

Sorry I should have said I am importing the spreadsheet into a SQL server table.

 

[SkipVought]

Hi,

Check out

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForExcel.

Query the sheet(s).

Skip,
[sub]
[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors [/sub]

 

[gmmastros]

Forget about VB for a moment. You can issue a command to Microsoft SQL Server to import the data for you.

The SQL Command is...

Select * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=[!]C:\test.xls[/!]','SELECT * FROM [[!]Sheet1$[/!]]')

You would use VB to issue the command to SQL Server. And, of course, the command given above only selects the data, but it should be easy enough to modify it so that it inserts the data in to a table.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom