Using Excel with databases

[Stuartp]

I have been using an Access program that reads data from an Excel spreadsheet using the DoCmd.TransferSpreadsheet acImport in VBA.

I now want to improve the application by writing it in VB6, but still need to get the data into Access from Excel? Is there an equivalent command in VB6 or could someone tell me some other way of doing it?

As a last resort we could always open the database, import, then close it and run the VB program, but surely there is an easier way than this!

Help much appreciated.

Stuart

 

[makeshift]

U can do this with automation...Set a reference to Access 9.0 or Access 8.0 object library...then do the following

add a command button call command1

Private sub command1_click()

Dim a As New Access.Application

a.OpenCurrentDatabase "c:\db1.mdb"
a.DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Newemps.wk3", True, "A1:G12"
a.Quit

End Sub

This will transfer spreadsheet Newemps.wk3 to a table in ur DB call employees

 

[Stuartp]

Hi - I've tried this and I keep getting the error

Run-time error 3274 - External table is not in the expected format.

I have tried referencing excel, since i am trying to import from an excel 2000 .xls spreadsheet. Any ideas?

cheers

Stuart