External Table isn’t in expected format… (97 SR-2)

Topic

New Reply

Hi All…

I’m having a heck of a time with this… I have an Excel spreadsheet that is emailed to my boss once a month… It’s got some crazy formatting… I have to remove all of the formatting (unlock sections and all that) in order to import the data into an Access database that creates journals (reports)… It’s been working fine for a long time but all of a sudden, the amounts aren’t coming in right… Since I had such a hard time bringing the data in at all, I was importing all of the fields as Text and converting the numeric ones in a query in the database… My boss noticed today that some of the amounts were different… It looks like they were being rounded or something… For example: 40,343.06 came in as 40,343.10 … and 38,671.64 came in as 38,671.60

Well I tried to get them to come in right and now when I try import the spreadsheet I get “Run Time Error 3274… External Table isn’t in the expected format… ”
I’ve tried everything that I can think of… Nothing much has changed from the original… Just some right justification… I don’t understand what is happening…

Also… Just curious… Our office is running all of Office 2000 except Access… We still have Access 97 for now… Is it possible that this has something to do with it?

I’m trying to import into an existing table shell called NewSheet… Here is the line of code that it’s breaking on…
DoCmd.TransferSpreadsheet acImport, , “NewSheet”, gstrPath & gstrMacroSheet, True

Any ideas?

Reply | Quote

Replies

I don’t think that the Office version is important here, for the Excel 2000 format is exactly the same as the Excel 97 format, so Access 97 should have no problems reading an Excel 2000 workbook.

You wrote “I tried to get them to come in right.” What did you change when you tried this? Only justification?

Would it be possible to create a small extract of the workbook that demonstrates the problem, and attach that to a reply? (Replace sensitive data by dummy data)

Reply | Quote

Hi Hans…

The attached zip file contains two .xls files…
The “eqfees.xls” is the one that we receive every month by email… That is what I really need to bring in…
The “RunEqFees.xls” contains a macro that gets rid of some formatting and copies the data from eqfees.xls to Sheet1 of this workbook…

“Sheet1” of RunEqFees.xls is what I’m trying to import into the “NewSheet” table in the Access database through code… I even tried to import it manually this morning and it didn’t work….

Let me know if you can see anything wrong…

Reply | Quote

There seems to be some kind of corruption in your workbook. If I copy the entire worksheet to a new workbook, the problem remains. If I just copy the “used range” to a new workbook, I can import it into Access.

Your worksheet has two other problems:

– Many of the empty-looking cells in the numeric columns (Loan Fees, Settlement, Debit Int and Credit Int) contain a space. This causes Access to import these columns as text instead of as numbers.

– The data doesn’t have the form of a database-like table. It is more like a report. There are empty rows, subtotals, etc. You should remove these. Also, the currency should be repeated in every row.

Reply | Quote

Thanks Hans…

Hmmm… Corruption huh?… You’re talking about the RunEqFees workbook right?
You’re right about the blank lines/subtotals and currency… I set it up to deal with those issues in the database itself (since I’m MUCH better in Access than Excel… I’m just about useless with Excel VBA… … I have GOT to find a book on that…) …I suppose I should try and fix them in Excel instead…
Thanks for the heads up on the spaces in the columns… I didn’t know that… That explains a lot… I’ve never had this amount of problems importing a sheet in before…

As always, you’re wonderful to have taken a look for me…

Reply | Quote

Thanks Hans…

Hmmm… Corruption huh?… You’re talking about the RunEqFees workbook right?
You’re right about the blank lines/subtotals and currency… I set it up to deal with those issues in the database itself (since I’m MUCH better in Access than Excel… I’m just about useless with Excel VBA… … I have GOT to find a book on that…) …I suppose I should try and fix them in Excel instead…
Thanks for the heads up on the spaces in the columns… I didn’t know that… That explains a lot… I’ve never had this amount of problems importing a sheet in before…

As always, you’re wonderful to have taken a look for me…

Reply | Quote

There seems to be some kind of corruption in your workbook. If I copy the entire worksheet to a new workbook, the problem remains. If I just copy the “used range” to a new workbook, I can import it into Access.

Your worksheet has two other problems:

– Many of the empty-looking cells in the numeric columns (Loan Fees, Settlement, Debit Int and Credit Int) contain a space. This causes Access to import these columns as text instead of as numbers.

– The data doesn’t have the form of a database-like table. It is more like a report. There are empty rows, subtotals, etc. You should remove these. Also, the currency should be repeated in every row.

Reply | Quote

Hi Hans…

The attached zip file contains two .xls files…
The “eqfees.xls” is the one that we receive every month by email… That is what I really need to bring in…
The “RunEqFees.xls” contains a macro that gets rid of some formatting and copies the data from eqfees.xls to Sheet1 of this workbook…

“Sheet1” of RunEqFees.xls is what I’m trying to import into the “NewSheet” table in the Access database through code… I even tried to import it manually this morning and it didn’t work….

Let me know if you can see anything wrong…

Reply | Quote

I don’t think that the Office version is important here, for the Excel 2000 format is exactly the same as the Excel 97 format, so Access 97 should have no problems reading an Excel 2000 workbook.

You wrote “I tried to get them to come in right.” What did you change when you tried this? Only justification?

Would it be possible to create a small extract of the workbook that demonstrates the problem, and attach that to a reply? (Replace sensitive data by dummy data)

Reply | Quote