Excel Linked Tables

[aziz rasul]

When I go into design view of an Excel linked table, it tells me that I can't change some properties. When I change the format of a field from Text to Number (and from Long Integer to Integer), a dialog box informs me that the property won't be changed but saves the changes anyway?

 

[reclusivemonkey]

That's because its a linked Excel table. If you want to change the properties, open the linked Excel spreadsheet and make/save the changes there.

 

[aziz rasul]

I have been told that I can't change the data type in the Excel file.

 

[reclusivemonkey]

This seems to me like an internal problem. Not a lot I can do to help. You only other alternative is to use a query to change the datatypes and maintain your own tables/queries.

 

[aziz rasul]

OK I've screamed and shouted in order to change the columns to Number format with 0 decimal places.

When I link the xls file I get #Num!. Any ideas anyone.

 

[reclusivemonkey]

Is the Excel file ok? Do you get #Num! in there? This generally means an error in a formula in Excel.

 

[neileg]

You can't define a datatype in Excel. Any cell can contain any data. You can change the formatting but nothing can stop you entering text in a column of figures.

So your question doesn't really make sense.

You are asking us to make your solution work. Please explain what the problem is and we may have a different solution.

 

[aziz rasul]

The Excel spreadsheet, which comes from an external source, has various columns of data where most cells are empty. Thoses cells that r not empty contain integer values and r in General Format.

When I link the xls file in Access, I get #Num! where there is equivalent data in the xls file.

That's the original problem.

I have now made the Excel data as Text and ran some code and relinked. It now works. All I need to do now is to create another table and transfer the text data into a numerical field.

Here's the code I used (thanks to someone else who helped out) -

Sub TextToNumbers()
Dim c As Range, addr As Range

Set addr = Selection
On Error Resume Next

For Each c In addr
c.Value = CLng(c.Value)
Next c

End Sub

 

[neileg]

I see. I think the problem is that Access expects to see a zero and not an empty cell when there is no number.

I would have thought that you could have used Clng in a query so that you would not have to create another table, and that if the data in the Excel sheet is updated, the query will present you with the numeric data without having to run the code again.

But the bottom line is that your problem seems to be fixed so that's great.

 

[aziz rasul]

Thanks to all who contributed.

neileg, I'll try the Clng route.