Access link to Excel showing an error on some records.

Hi Experts,
I have written a small application that is linked to Excel files, reads two spreadsheets and does some comparisons.
Those sheets contain SS numbers.
Now I see that some socials are aligned to the right side and some to the left side.
And the problem is that Access only reads the first set of socials, the rest are showing as error.
Since user gets those files from a third party software, no way to figure out why they're like this..
How can I overcome this issue?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Link the worksheet using a defined import specification.
In this, specify the SS field as Short Text.
0
Dale FyeCommented:
what version of Access?

Gustav, in what version of Access did MS finally allow creation of import specs for Excel files?

bFuchs,

Can you get that file as a tab or comma delimited text file?  If so, you can import it once and create that import specification that Gustav mentions above.  Then, if you are using the Transferspreadsheet method to link the file, you can use that import specification.

Otherwise, what I do if neither of the above is feasible, is to open the Excel file using automation and format the columns the way I want them, in this case as text.  I don't remember the name of the command, of the top of my head, but you can select a column and set it so that numbers are treated as text, which is what you want for SSN's.

But if you are storing SSNs in Excel spreadsheets, I hope your network is secure and you are using proper techniques to protect those files.
0
bfuchsAuthor Commented:
Hi Experts,
Actually I recall this issue happening when part of the data contained only numbers (just formatted as SS) while the rest contained text, and I have the following procedure to fix that issue.
Sub SSnumberToText()
Dim xlApp As Object
Dim wb As Object
Dim v As Variant
Dim rg As Object
Dim i As Long, n As Long
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set wb = xlApp.Workbooks.Open("C:\Application\Comparison.xls")   'change path to suit
With wb.Worksheets(1)
    Set rg = .Range("A1")       'First cell with social security number
    Set rg = xlApp.Range(rg, .Cells(.Rows.Count, rg.Column).End(-4162))     '-4162 is the value of xlUp
End With
v = rg.Value
n = rg.Rows.Count
For i = 1 To n
    If v(i, 1) <> "" Then v(i, 1) = Format(v(i, 1), "000-00-0000;;;@")
Next
rg.Value = v
wb.Save
With wb.Worksheets(2)
    Set rg = .Range("A1")       'First cell with social security number
    Set rg = xlApp.Range(rg, .Cells(.Rows.Count, rg.Column).End(-4162))     '-4162 is the value of xlUp
End With
v = rg.Value
n = rg.Rows.Count
For i = 1 To n
    If v(i, 1) <> "" Then v(i, 1) = Format(v(i, 1), "000-00-0000;;;@")
Next
rg.Value = v
wb.Save

wb.Close SaveChanges:=False
Set wb = Nothing
Set xlApp = Nothing
End Sub

Open in new window


Thanks,
Ben
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaun VermaakTechnical Specialist/DeveloperCommented:
Now I see that some socials are aligned to the right side and some to the left side.
Some are text and some are numbers. You need to either create it differently or read it differently
0
bfuchsAuthor Commented:
@Shaun,
The problem was that as mentioned, this data is coming from a third party software that I dont have a way to modify..
Just setup a link to that Excel sheet.
However with the code above the problem is gone.
Thanks,
Ben
0
bfuchsAuthor Commented:
Problem solved.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.