Unable to resolve cell/range/column format in vba code

[VicM]

Hi All,

I receive spreadsheets from two different sources. I need to import both of them into an Access (2010) table. For the most part, the VBA code works as expected.

But I have one issue I'm at a loss to understand. In both spreadsheets there is a column of values, ostensibly numbers, but represented as text since they all have padding leading zeros to represent a total of six digits. I do not need to import the numbers as text, since the field they will eventually occupy is in a table defined as long number. (The created table is subsequently used to update a main demographics table in the DB.)

In VBA code using the spreadsheets from the first source, I include this line of code:

impfile.Application.ActiveWorkbook.ActiveSheet.Columns(3).NumberFormat = "0"

when the routine completes and I look at the table created, the values are indeed numbers with no leading zeros. Also, immediately after the above line of code I inserted a Debug.Print command to see what the NumberFormat returns. In the Immediate window I get a zero (0).

When I run the identical code on the spreadsheets from the second source, the created table shows the numbers as text with the leading zeros. And the Debug.Print shows six zeros (000000) in the Immediate window. However if I insert this next line of code after the one above, the numbers appear as numbers in the created table.

impfile.Application.ActiveWorkbook.ActiveSheet.Columns(5).Value = 
impfile.Application.ActiveWorkbook.ActiveSheet.Columns(5).Value

(PS The numbers I need are in differing columns from the two sources; hence one is column 3, the other column 5)

Once I've made the changes to the spreadsheets, I run this next line of code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblImportData", filepathXLWk, True

(filepathXLWk was set to the filepath of the spreadsheet.)

Now one more piece of info: When I open each of the spreadsheets themselves in Excel and check the column format, in the first, the one that works as expected, the format is "General". But in the second, the format for the column is the first line of the Chinese(PRC) format. (I don't know why since this info is coming from a US company.)

Any insights greatly welcome,
Vic

 

[SkipVought]

Hi,

Changing the NumberFormat changes NOTHING in the underlying data. The DATA is defined as TEXT and by changing the NumberFormat to General changes NOTHING: IT IS STILL TEXT.

In one case you have 6 text characters that happen to all be numeric. THAT value is totally different than a 6 digit number that appears to be identical.

You must actually CONVERT the 6 digit string to a number by, for instance, multiplying the 6 digit string by 1 in Excel or in your VBA process.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[VicM]

Skip
I appreciate your response.
Because I'm able to force the correction using the second code line, I'm able to continue with my import process.

But I'm still confused as to why, in the spreadsheet from the first source, where the column's format is listed as 'General', setting the column's NumberFormat to "0" the routine imported the values as numbers and not text, even tho some of the numbers had zeros padded in front. Yet in the spreadsheet from the second source, I have to utilize the second line of code to force the system to disregard the prepended zeros.

There's something here I'm just not seeing.

It's probably not worth the time to delve into this. But it took me a number of iterations to hit upon that second code line which basically solves my problem.

Thanks,
Vic

 

[SkipVought]

The number 1 and the character 1 have totally different binary values.

In order to combine the two in some meaningful way either the number must be converted to a character or the character must be converted to a number.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[VicM]

Skip,

I do recognize the ASCII or binary differences between a number 1 and a text 1.

Thanks again,
Vic