INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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:

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.

CODE

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:

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Skip,

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

Thanks again,
Vic

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close