Unexpected error from external database driver (22)

[Steve S]

I get the error Unexpected error from external database (22) when I try to
perform a SQL Server DTS operation on an excel spreadsheet. It does not
happen to all spreadsheets, just ones I get from a particular vendor. He
claims it is in a MS Excel '97 format.

In order to get the file to work I have to File-open(file type is .xls),
Save-as type xls (Same type). After this operation of saving it in whta is
apparently the same format everything works fine.

I'm running SQL server 2000.

Any clues?

Steve S

[Douglas Laudenschlager [MS]]

Steve,

I can't find in Microsoft's support records that anyone has ever reported
this exact error message/number in connection with an Excel file, but it
seems obvious that the file from your vendor is either damaged or flawed in
some way, or not the version he says it is. I suspect it would be difficult
to pinpoint the problem. Any chance it's just a .CSV file that the vendor
renamed .XLS? Does the file size change noticeably when you re-save it?

You could automate the open/re-save using Excel Automation (hear that,
Allan?) but would want to take care to specify all options to suppress
dialogs and "are you sure" messages if your DTS packages run unattended.

-Doug

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

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

[Steve S]

Thanks for the reply. I did realize that there is a flaw in the file and it
is VERY difficult to pinpoint. The file is not a .csv file ( I opened it in
notepad to check) and the file size doesn't change after saving it. I
probably will either try to get my vendor to send in a csv file or do an
automated open/save

Thanks

Steve S

""Douglas Laudenschlager [MS]"" <doug...@online.microsoft.com> wrote in
message news:uxRofaxQCHA.2372@cpmsftngxa06...

[Steve S]

I tried running a VB App that does an automated save as. I found there is
no save as method I used the following code but got an error (after The
"File already exists message" that I haven't figured out how to suppress
yet)

Error Message =Run-time error '1004':

"You cannot save this workbook with the same name as another open workbook
or add-in. Choose a different name, or close the other workbook or add-in
before saving.

Here is the piece of code I am using;

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Open FileName:=m_sImpFile 'This is a global
variable with the filename
xlApp.Visible = False
xlApp.Save m_sImpFile
m_sActiveSheetName = xlApp.ActiveSheet.Name & "$" ' I need this
to pull the correct sheet as a database (the name changes)
xlApp.workbooks.Close

Also, I have determined it is an Excel 5.0/95 format

My connection string for ADO access is

strConnExc = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_sImpFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Any clues?

TIA

Steve

""Douglas Laudenschlager [MS]"" <doug...@online.microsoft.com> wrote in
message news:uxRofaxQCHA.2372@cpmsftngxa06...
>

[Allan Mitchell]

Excel 8 in your connection string is a little young for Excel 95 i.e
it should be Excel 5 I believe.

There are lots of articles in MSDN for error 1204.

XL2002: Error Message Method SaveAs of Object _Worksheet Failed

They in the main relate to Excel 2000 and 2002 but they might be worth
a look. Douglas has a large list of Excel related issue he usually
hands out as well.

Allan
www.SQLDTS.com

Allan Mitchell
www.SQLDTS.com

[jfb...@gmail.com]

On Tuesday, August 13, 2002 9:13:16 PM UTC+2, Steve S wrote:
> I get the error Unexpected error from external database (22) when I try to perform a SQL Server DTS operation on an excel spreadsheet. It does not happen to all spreadsheets, just ones I get from a particular vendor. He claims it is in a MS Excel '97 format.In order to get the file to work I have to File-open(file type is .xls), Save-as type xls (Same type). After this operation of saving it in whta is apparently the same format everything works fine.I'm running SQL server 2000.Any clues?Steve S

I had the same problem when trying to load data from an Excel 97-2003 worksheet, and the worst part is that some files worked perfectly and some didn't.
I finally figured it out, the problem had to do with the worksheet's name lenght. Using a shorter one solved the problem.
I hope this helps.
However, I still don't know how to automatically shorten the worksheet's name, so the process of loading data from Excel files can be automated. If someone knew how to do this and cared to share it with me I'd be very grateful.