Excel Import/Link Problem 1

[Savil]

Hi All
Im having a problem linking or importing (I don't mind either way) from an excel spreadsheet. one column has mixed text and numbers but it always comes through as a number data type with the text values showing '#NUM'. I have set the column on the sheet to no avail. What can I do?

Thanks

 

[SkipVought]

Hi,

It's a no-no, mixing text and numbers in the same column!

SOLUTION: Make the Numbers into TEXT STRINGS.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Savil]

I hear you but i am assuming this has to be done on the spreadsheet prior to import? Do all the the values need encapsulated in quotes?

 

[SkipVought]

You could use a procedure like this...

Sub MakeNumericTextInColumn()
   dim r as range

'first select the column you want to convert

   With Selection
      for each r in range(Cells(2, .Column), Cells(2, .Column).End(xlDown))
        with r
          if isnumeric(.value) then
            .value = "'" & .value
          end if
        end with
      next
   End With
End Sub

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[Savil]

Thanks Skip that should do it!