Linking to an Excel Worksheet From Access 2000

[Mitch]

I have the following code that I'm trying to get to link
to an excel workbook, but eveytime I reach the line Set
rstFACPURTemp = excelCnn("Select * From FACPUR") I get
the error "Runtime error -'2147217865(80040e37)': The
Microsoft Jet database engine could not find the
object 'FACPUR' Make sure the object exists and you spelt
it's name and path name correctly"

Dim excelCnn As ADODB.Connection
Set excelCnn = New ADODB.Connection
Dim strDataSource As String
strDataSource = AppPath & "FACPUR.xls"
excelCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
strDataSource & ";Extended Properties=Excel 4.0;"
Set rstFACPURTemp = New ADODB.Recordset
Set rstFACPURTemp = excelCnn.Execute("Select * From
FACPUR")

Any Ideas?

Thanks

Mitch

 

[Douglas J. Steele]

Try

excelCnn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & strDataSource & _
"DefaultDir=" & AppPath

 

[Mitch]

Thanks Doug for responding, however, I now get the
error: "Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Excel Driver] Failure Creating File."

Any ideas as to why?

Thanks

Mitch

 

[CSmith]

Hi,

You need the following:

strDataSource = AppPath & "\FACPUR.xls"

 

[Steven Parsons [MSFT]]

Hi Mitch -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post. Have you seen the following web page:

HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=KB;EN-US;257819

Please let me know if this helps to resolve your problem or if you would
like further assistance. I look forward to hearing from you.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)