Excel 4.0 ADO problem 
Author Message
 Excel 4.0 ADO problem

My client is sending me Excel 4.0 worksheets and I am getting the following
error:

Run-time error '-2147467259 (80004005)':

"ql4.xls$' is not a valid name.  Make sure that it does not include invalid
characters or punctuation and that it is not too long.

Here is the code:
------------------
    Set cnXLS = CreateObject("ADODB.Connection")

    XLSConvert = ""
    With cnXLS
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Extended Properties").Value = "Excel 8.0;HDR=" &
cHeaderFlag
        .Open cOutputFile

        Set rsSchema = .OpenSchema(adSchemaTables)

        Do Until rsSchema.EOF

            cSheetName = rsSchema.Fields("TABLE_NAME").Value
'<---------------------------IT FAILS ON THIS LINE

            rsXLS.Open "SELECT * FROM [" &
rsSchema.Fields("TABLE_NAME").Value & "]", cnXLS,  adOpenStatic

------------
It fails on the rsXLS.Open.  In this case, cSheetName is ql4#xls$.  I'm not
sure where the # is coming from.  If I simply open the worksheet and save it
as a current Excel version, the cSheetName is ql4.xls$ and everything is
fine.  Any assistance is appreciated.  I don't have the option to dictate
that the client use a later version of Excel.

Thanks,

Ken



Tue, 10 May 2005 08:46:03 GMT  
 Excel 4.0 ADO problem
Hi,

Did not try with Excel 4.0, but I think you need to change Extended
Properties for it to use Excel=5.0 or Excel=3.0.
Also, it is maybe stupid, but do you have ql4.xls spreadsheet in that file?

--
Val Mazur
Microsoft MVP


Quote:
> My client is sending me Excel 4.0 worksheets and I am getting the
following
> error:

> Run-time error '-2147467259 (80004005)':

> "ql4.xls$' is not a valid name.  Make sure that it does not include
invalid
> characters or punctuation and that it is not too long.

> Here is the code:
> ------------------
>     Set cnXLS = CreateObject("ADODB.Connection")

>     XLSConvert = ""
>     With cnXLS
>         .Provider = "Microsoft.Jet.OLEDB.4.0"
>         .Properties("Extended Properties").Value = "Excel 8.0;HDR=" &
> cHeaderFlag
>         .Open cOutputFile

>         Set rsSchema = .OpenSchema(adSchemaTables)

>         Do Until rsSchema.EOF

>             cSheetName = rsSchema.Fields("TABLE_NAME").Value
> '<---------------------------IT FAILS ON THIS LINE

>             rsXLS.Open "SELECT * FROM [" &
> rsSchema.Fields("TABLE_NAME").Value & "]", cnXLS,  adOpenStatic

> ------------
> It fails on the rsXLS.Open.  In this case, cSheetName is ql4#xls$.  I'm
not
> sure where the # is coming from.  If I simply open the worksheet and save
it
> as a current Excel version, the cSheetName is ql4.xls$ and everything is
> fine.  Any assistance is appreciated.  I don't have the option to dictate
> that the client use a later version of Excel.

> Thanks,

> Ken



Tue, 10 May 2005 09:29:03 GMT  
 Excel 4.0 ADO problem
I tested the code with a Excel 4.0 file, didn't found the problem. I
suggest you can installed latest Jet 4.0 service pack to see if it can
resolve the problem. Or you can test more EXCEL file to see if it will
always failed with same error.

Luke

(This posting is provided "AS IS", with no warranties, and confers no
rights.)



Tue, 10 May 2005 16:25:38 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using ADO and Jet 4.0 to access data in Excel

2. Excel 4.0 - VB 3.0 DDE Problem

3. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

4. looking for conversion software for MS Excel 4.0 (ELM) to MS Excel 5.0 visual Basic

5. microsoft excel 4.0 macros coversion to excel 5.0 macros

6. ADO Jet 4.0 performance problem vs Jet 3.51

7. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

8. Getting data from Excel to VB using ADO problem

9. ADO to Excel number format problem

10. ADO connection to Excel problem

11. ADO to Excel dataformat problem

12. ADO to Excel dataformat problem?

 

 
Powered by phpBB® Forum Software