Formatting problem when importing from Excel

[Alc]

I have a sub procedure that imports data from an Excel spreadsheet. This works fine, apart from when account numbers beyond a certain length are encountered. In these cases, there is a comparison problem.

For example, if an account number is 1111-2222-3333-4444
I first remove the hyphens, leaving 1111222233334444.
I then compare it against a stored format (e.g. XXXXXXXXXXXXXXXX), using just the length of both strings. If the lengths match - as they should, in this example - the format is allowed and the rest of the procedure is carried out.

Where the problem occurs is that this only works for smaller numbers. The number shown above is being interpreted as 1.11122233344E+15, which obviously means that the comparison returns 'False'.

This occurs on the spreadsheet itself, as well. If I use Format - Cells to change it to 'Text', the same format change occurs.

Any ideas how I can stop a number becoming something else entirely when I change it to text?

 

[DCrake]

In your import spec go to advanced and change the data type to text and retry.

 

[Alc]

Thnks for the suggestion.

The data type for the variable in the VBA is already a string. Is there somewhere else I should be changing/setting it?

I've sort of pieced together this import script as needed, so I freely admit I may have missed out things due to being unaware of a need for them.

 

[DCrake]

I would remove the formatting of the field until after the data has been imported. The problem with imput masks and formatting it always expects the correct layout, if it is not in some cases it will throw everything else out.

David

 

[Alc]

I've gone through the spreadsheet and tried changing the format to every one available on the list (I couldn't see a way to remove the formatting altogether). None bar 'Number' allows me to view the account numbers in the right way and even those get changed to the 'E+15' thing once the VBA gets hold of them.

Before I've even assigned each to a variable, using the line

MsgBox WorkS.Cells(I, li_AccountNo_Column)

gives me the 'E+15' version.

I don't know if it's connected, but I've just found that if I try to alter one of the account numbers on the spreadsheet, I can change all bar the last digit. This always gets reverted to a zero, once I leave the cell (the number ending in 4444 was just used for example purposes). The cell isn't locked, as any other changes remain.

 

[Scooterbug]

Thnks for the suggestion.

The data type for the variable in the VBA is already a string. Is there somewhere else I should be changing/setting it?

I've sort of pieced together this import script as needed, so I freely admit I may have missed out things due to being unaware of a need for them.

What DCrake is suggesting is to create an import spec. The problem with importing an excel spreadsheet is that you cant specify an import spec for them (as far as I know...I just tried). If you save the spreadsheet as a .csv file, you can then create an import spec for it. This tells access to bring the data from that cell in as text instead of a number. To create an import spec, you will need to manually import the data. After you make your necessary changes, click on the Advanced button. From there you can save the import spec. Using the TransferText command, you can tell Access to use your import spec when importing.

 

[Alc]

Thanks, I'll give that a try. As I say, this has all been 'need to know' and hasn't come up before.

I do have some reservations, though, given that when I change the field in Excel itself it changes the format. Via experimentation, I've found that this only occurs for account numbers longer than 11 characters. Quite why that's the threshold, I have no idea.