Importing spreadsheet data to Access [AS IS] 1

[ajetrumpet]

I am having a bit of trouble figuring out weather or not I can change the formatting of a linked Excel table in Access. I need to view all the Excel data linked to Access, but I have many columns that contain text combined with numbers (Access automatically links to it and defaults the corresponding field format to a number by reading the first record). Thus, I get #Num!

The cells in Excel that cause this contain mixed transaction identifying characters (most are numbers, some are a mix, EXAMPLE = cs0043). Can I somehow prevent this from happening? I am thinking the solution is pretty simple, but I haven't caught it yet. Thank you.

 

[SkipVought]

Hi,

You must make the change in Excel. Make all numeric fields, TEXT...

sub Num2Txt()
'paste into an excel module and run in excel
'change the range reference as required
  dim r as range
  for each r in range([B1], [B1].end(xldown))
    with r
      if isnumeric(.value) then "'" & .value
    end with
  next
end sub

Skip,

_[red][/red]

 

[ajetrumpet]

Thanks for the code =)
I'm getting a syntax error though....

for line 7 (if isnumeric...)

Also, if I want to specify an entire column as a range, should it be ($D), or just (D1)? I notice the XLDOWN command, does that eliminate the work of the $ symbol?

 

[SkipVought]

sorry...

Sub Num2Txt()
'paste into an excel module and run in excel
'change the range reference as required
  Dim r As Range
  For Each r In Range([D1], [D1].End(xlDown))
    With r
      If IsNumeric(.Value) Then [b].Value =[/b] "'" & .Value
    End With
  Next
End Sub

Skip,

_[red][/red]

 

[ajetrumpet]

I need to know about the XLcommand. Can I apply as many columns as I want with this using the [cell], [cell] section of this code?

Also, what's with the instructions on the 2nd and 3rd lines? =) Don't I need the 1st line of this code in the module!? Does the bold format make a difference?

 

[ajetrumpet]

Nevermind, those lines are actually part of the code, I didn't realize that! =) oops!

 

[MichaelRed]

I. on the other hand, always recommend importing ALL excel (and similar loosley typed 'information') as text. Then, doing extensive V&V to determine what needs to be fixed. Send that back to the originator with some stronly worded reminder a-la it is EXPECTED - REQUIRED to be enterd PROPERLY ...

There will be a backlash to this -at first. If there is any competent manager involved, the uproar will diminish with a simple explination and the plethora of samples you will be able to show.



MichaelRed

 

[ajetrumpet]

Michael,

I work with a large number of people and I do business with many firms. I couldn't possibly ask all of them to be universal as far as indentifying their transactions with our business. I simply take the numbers I get from them and create my own spreadsheets and databases. Its much easier for me to do that and then configure the data to comply with Access functionality, rather than put the burden on the customer. I'm not a programmer man, I'm more of a business consultant. I don't configure code, I configure business sense. =)

 

[ajetrumpet]

By the way, I'm not getting actual files from customers, I'm just getting data that I always have to enter. Maybe you misunderstood what I needed help with....


HEY SKIPVOUGHT! Thanks for the code, it works perfectly. Do you know where I can get a Visual Basic book that gives me a library of code "tidbits" that relate to different tasks and fuctions in both Excel and Access? I figure if I can get my hand on a book like that, I wouldn't have to bother people with small problems like this one. The code is exactly what I needed to fix the problem too!! I had to reconfigure Access a bit, but the bottom line is that the end user of the information can access it exactly the way the need to. THANKS AGAIN! =)