Cannot insert sheets into the destination workbook

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hi,

We are having a problem with Excel which I could do with a solution if
possible.

The situation is this we write a bespoke application which sits on an IBM
Universe database which we access using a program called SBClient, one of the
feature of this is we are able to run queries against the database and have
the output export in to Excel.

The problem we are having at the moment is when we try to run this query we
get a message in Excel saying:

"The file you are trying to open 'SBCLIENT.XLS' is in a different format
than specified by the file extension. Verify that the file is not corrupted
and is from a trusted source before opening the file. Do you want to open the
file now?"

We say yes to this and the information starts importing into the spreadsheet
but then we get another message saying:

"Excel cannot insert the sheets into the destination workbook, because it
contains fewer rows and columns than the source workbook. To move or copy the
data to the destination workbook, you can select the data, and then use the
Copy and Paste commands to insert it into the sheets of another workbook."

When we click OK on this message the import stops.

We are not importing much data there are only around 6 columns and not very
many rows either.

Can anyone help?

Thanks

Anthony
 
It sounds like you're using xl2007.

Native xl2007 allows up to 1 meg rows and 16k columns. These files have
extensions like .xlsx (no macros) and .xlsm (with macros).

But if the file is saved in the xl97-xl2003 format, it'll have 64k rows and 256
columns. These files should have an extension of .xls.

It sounds like you need to save the sbclient.xls with the correct extension (or
the correct file format).

And if the source file has 1M rows (xl2007 format), you'l have to make sure that
the "receiving" file is also this format.
 
Workaround in Macros

Hi, here is the workaround code I use to save the 2007 workbook in old 2003 format, and reopen it in compatibility mode in order to be able to move/copy compatilble 64k lines sheets.
Enjoy

Application.DisplayAlerts = False
Workbooks(myworkbook).SaveAs Filename:="tempo_compatible.xls", FileFormat:=xlAddIn8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=True
Application.DisplayAlerts = True
Workbooks("tempo_compatible.xls").Close
Workbooks.Open Filename:="tempo_compatible.xls"
 
Back
Top