I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells.  I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value.  This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmand it had the line
   SELECT T1.*, 1 AS SheetSource FROM
   [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
   as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1.  If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed.  Ex:

Sub Imex1()
     DoCmd.TransferSpreadsheet acLink...

     Dim t As TableDef
     Dim s As String
     Dim i As Integer

     For Each t In CurrentDb.TableDefs
         s = t.Connect
         i = InStr(s, "IMEX=2")
         If i > 0 Then
             s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
             t.Connect = s
             t.RefreshLink
         End If
     Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column.  It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:





I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells.  I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value.  This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmandit had the line
   SELECT T1.*, 1 AS SheetSource FROM
   [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
   as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1.  If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed.  Ex:

Sub Imex1()
     DoCmd.TransferSpreadsheet acLink...

     Dim t As TableDef
     Dim s As String
     Dim i As Integer

     For Each t In CurrentDb.TableDefs
         s = t.Connect
         i = InStr(s, "IMEX=2")
         If i > 0 Then
             s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
             t.Connect = s
             t.RefreshLink
         End If
     Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column.  It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next- Hide quoted text -

- Show quoted text -

Roger wrote:
On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:

I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells.  I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value.  This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmandit had the line
  SELECT T1.*, 1 AS SheetSource FROM
  [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
  as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1.  If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed.  Ex:

Sub Imex1()
    DoCmd.TransferSpreadsheet acLink...

    Dim t As TableDef
    Dim s As String
    Dim i As Integer

    For Each t In CurrentDb.TableDefs
        s = t.Connect
        i = InStr(s, "IMEX=2")
        If i > 0 Then
            s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
            t.Connect = s
            t.RefreshLink
        End If
    Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column.  It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next

What version of Access?  What is the Connect property?  I am using
A2003.  I tested my Sample XLS file in A97 as well.

My TransferSpreadsheet line looks like
   DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ZZZ",
"C:\Sample", False
I don't want headers on my link so False is set.  Also, it's not an
import but a link.

My Connect property or table ZZZ after transer is
   Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
and
   Excel 8.0;HDR=NO;IMEX=1;DATABASE=C:Sample.xls
after I ran IMEXT1 sub.

I ran it on A97 and this is the Before/After Connect property
   Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
   Excel 5.0;HDR=NO;IMEX=1;DATABASE=C:\Sample.xls
and it removed the #Num!'s and presented the text value.- Hide quoted text -

- Show quoted text -

On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:

I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells. I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value. This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmand it had the line
SELECT T1.*, 1 AS SheetSource FROM
[Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1. If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed. Ex:

Sub Imex1()
DoCmd.TransferSpreadsheet acLink...

Dim t As TableDef
Dim s As String
Dim i As Integer

For Each t In CurrentDb.TableDefs
s = t.Connect
i = InStr(s, "IMEX=2")
If i > 0 Then
s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
t.Connect = s
t.RefreshLink
End If
Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column. It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.


I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next

On Nov 4, 10:00 am, Roger <lesperan... at (no spam) natpro.com> wrote:

On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:






I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells. I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value. This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmandit had the line
SELECT T1.*, 1 AS SheetSource FROM
[Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1. If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed. Ex:

Sub Imex1()
DoCmd.TransferSpreadsheet acLink...

Dim t As TableDef
Dim s As String
Dim i As Integer

For Each t In CurrentDb.TableDefs
s = t.Connect
i = InStr(s, "IMEX=2")
If i > 0 Then
s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
t.Connect = s
t.RefreshLink
End If
Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column. It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next- Hide quoted text -

- Show quoted text -


I read the article further, and noticed that if the first row of the
worksheet has alpha for all columns that the above tip will work if
you set HDR=NO

On Nov 4, 10:31 am, Salad <o... at (no spam) vinegar.com> wrote:

Roger wrote:

On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:

I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells. I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value. This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import.htmandit had the line
SELECT T1.*, 1 AS SheetSource FROM
[Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1. If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed. Ex:

Sub Imex1()
DoCmd.TransferSpreadsheet acLink...

Dim t As TableDef
Dim s As String
Dim i As Integer

For Each t In CurrentDb.TableDefs
s = t.Connect
i = InStr(s, "IMEX=2")
If i > 0 Then
s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
t.Connect = s
t.RefreshLink
End If
Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column. It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next

What version of Access? What is the Connect property? I am using
A2003. I tested my Sample XLS file in A97 as well.

My TransferSpreadsheet line looks like
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ZZZ",
"C:\Sample", False
I don't want headers on my link so False is set. Also, it's not an
import but a link.

My Connect property or table ZZZ after transer is
Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
and
Excel 8.0;HDR=NO;IMEX=1;DATABASE=C:Sample.xls
after I ran IMEXT1 sub.

I ran it on A97 and this is the Before/After Connect property
Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
Excel 5.0;HDR=NO;IMEX=1;DATABASE=C:\Sample.xls
and it removed the #Num!'s and presented the text value.- Hide quoted text -

- Show quoted text -


yes, once hdr=no was set, the imex=1 tip works fine

Roger wrote:
On Nov 4, 10:31 am, Salad <o... at (no spam) vinegar.com> wrote:

Roger wrote:

On Nov 3, 3:05 pm, Salad <o... at (no spam) vinegar.com> wrote:

I linked a speadsheet in an app and opened it and noticed I had some
#Num! cells.  I guess that called to my attention that there were number
and alphas in that column and so the text values became errors.

MS's solution was to format the worksheet or range as Text and then run
a macro that puts a space in front of every value.  This means I have to
assume the op that entered/fixed the Excel file is going to format the
spreadsheet as Text everytime and run a macro without fail.

Then I read something athttp://www.accessmvp.com/KDSnell/EXCEL_Import..htmandithad the line
 SELECT T1.*, 1 AS SheetSource FROM
 [Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyFile.xls].[WorksheetName...]
 as T1;
and it stated "IMEX=1 alllows "mixed formatting" within a column (alpha
characters and numbers, for example) so that errors will not be raised
when importing mixed formats."

So I wondered, what happenes if I change the IMEX value from 2 (which
creates the #Num! error) to 1 in the Connect string of the linked Excel
table.

I now do a DoCmd.TransferSpreadsheet acLink... command, then read the
Connect property of the linked table and see if IMEX is a 1.  If not, I
changed the IMEX to 1 and RefreshLink and now the Excel file is correct
and can be processed.  Ex:

Sub Imex1()
   DoCmd.TransferSpreadsheet acLink...

   Dim t As TableDef
   Dim s As String
   Dim i As Integer

   For Each t In CurrentDb.TableDefs
       s = t.Connect
       i = InStr(s, "IMEX=2")
       If i > 0 Then
           s = Left(s, i - 1) & "IMEX=1" & Mid(s, i + 6)
           t.Connect = s
           t.RefreshLink
       End If
   Next
End Sub

Maybe this will help somebody out in the future avoid #Num! errors with
mixed data types in an Excel file's column.  It's simpler than the MS
solution and doesn't put a burden on the user that might create the
Excel file.

I tried your tip, plus the original VBA in access97, and I still get
#num
I tried formatting the excel column as text and as numeric... no luck

I'll try automation next

What version of Access?  What is the Connect property?  I am using
A2003.  I tested my Sample XLS file in A97 as well.

My TransferSpreadsheet line looks like
  DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ZZZ",
"C:\Sample", False
I don't want headers on my link so False is set.  Also, it's not an
import but a link.

My Connect property or table ZZZ after transer is
  Excel 8.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
and
  Excel 8.0;HDR=NO;IMEX=1;DATABASE=C:Sample.xls
after I ran IMEXT1 sub.

I ran it on A97 and this is the Before/After Connect property
  Excel 5.0;HDR=NO;IMEX=2;DATABASE=C:\Sample.xls
  Excel 5.0;HDR=NO;IMEX=1;DATABASE=C:\Sample.xls
and it removed the #Num!'s and presented the text value.- Hide quoted text -

- Show quoted text -

yes, once hdr=no was set, the imex=1 tip works fine

Thanks for the validation.  It will be very useful for me in my current
project.  Don't need any "gotchas" down the road.