Hi Guys,

I have written a report in an Access DB that uses data from an Excel SS. To read the data, I have created an external link to the XL worksheet.

The problem is that one of the columns has numbers in the first few rows but later rows contain alpha charaters as well and this field should be a text field, however when ever I link to the worksheet, Access always treats this column as a number field and the records that contain the alpha characters display as errors.

Does anyone know how to force Access to treat this column as a text field?

If you import the data, the import wizard detects it as text, but the spreadsheet will change from time to time and I don't want to have to keep importing each time it changes.

Any ideas?

TIA

Alan

Hi,

   Any change you make to the spreadsheet (data types) will require you to unlink and relink the spreadsheet to see if it works.

   If you haven't already done this, try opening the spreadsheet, selecting the offending column (entire column) and formatting the cells to Text or General, something other than number. Then reconnect the spreadsheet and see if this works.

   I have a similar issue in that I want to link to a spreadsheet for OLAP, but what I do is transfer in the spreadsheet each time the form is loaded. This would resolve your problem based on your post since it imports the data.

   If you want, I can give you that code.

HTH


mmcdonal

Hi mmcdonal,

Thanks for your suggestions. I tried setting the format explicitly to text and linking again, but still no difference.

So I thought I'd try importing the data and that created all sorts of new problems.

I'm opening the XL SS as an application and reading in the data cell by cell. No problems there.

However, when my application has finished with the spread sheet I quit Excel, but in Processes tab of Task Manager, EXCEL.EXE continues to run. It is not until you shut down MA Access that this process is killed. What's wrong with that? I hear you ask.

Well, if you attempt to import more data without closing down the Access application, you get a VBA error:

Run-time error 1004
Method 'Cells' of object '_Application' failed

when the code starts reading the data from the XL SS.

Anyone encounted anything like this before?

Any suggestions?

TIA

Alan

P.S.

When you attempt to read the data a second time, a new instance of Excel is launched, then when you exit only one of the EXCEL processes is killed.

If you try this a few times (as I did while trying to find out what was going on) you end up will all the Excell processes running and all you can do is manually kill them from Task Manager.

Why don't you try something like this:

When I load a particular form, I delete the old table, recreate it from a second table that is structure only, then transfer in the spreadsheet:

'--------------------------------------
DoCmd.DeleteObject acTable, "tblPosition"
DoCmd.CopyObject , "tblPosition", acTable, "tblP"
DoCmd.TransferSpreadsheet acImport, , "tblPosition", "I:\Data\2005_04PERM.xls", True
'--------------------------------------

This gives me a local copy of the spreadsheet data without the API calls. It is removed at the beginning of every session, so OLAP problems are not a concern. It is always the freshest data.

Actually, I have a set of 26 buttons (they only appear when their respective spreadsheet is in the data folder) and when you press any one of them, they delete the old table, create a new one, and transfer the spreadsheet for the pay period in for reporting.

HTH

mmcdonal

A simple, non-hightech answer is to save your Excel file as as .txt (or .csv) file and then when you link to the text file, you can click on the Advanced button and it allows you to import any column in whatever format (numbers, text etc) that you want. Save the import specification and you will never need to worry about it again. It's another stage in the process, but makes life considerably easier.