Linking an Excel file in Access with mixed data types in columns and #Num!
Salad
#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 at
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm and 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.
Roger
> 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.
>
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
Roger
> On Nov 3, 3:05 pm, Salad <o...@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.
>
>
> - 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
Salad
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.
Salad
Yes. My HDR was set to No. Good catch.
Roger
> Roger wrote:
> > On Nov 3, 3:05 pm, Salad <o...@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.
>
>
> - Show quoted text -
yes, once hdr=no was set, the imex=1 tip works fine
Salad
Thanks for the validation. It will be very useful for me in my current
project. Don't need any "gotchas" down the road. :)
Chuck Grimsby
> Roger wrote:
> > On Nov 4, 10:31 am, Salad <o...@vinegar.com> wrote:
>
> >>Roger wrote:
>
> >>>On Nov 3, 3:05 pm, Salad <o...@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.
>
Interesting.... Usually, I only link to spreadsheets, or open them
via automation to do whatever the heck it is I need to do. Quite
often, it's updating the spreadsheet so someone else can continue
working on the data somewhere out in the field.
For what it's worth, I put a single quote (Chr$(39) in front of
numbers I don't want excel to think are numbers, and when the sheet is
opened in Excel, Excel hides the ' from the user, unless they look at
the text in the ... Address bar, formula bar, or whatever the heck
Excel calls that thing.
The other thing that I've noticed, is that when you export to Excel,
Access is quite happy to export any Nulls as Nulls, and Excel is OK
with that. Upon re-linking, Access will see those Nulls as #num!.
I've since updated my export routine so that Nulls are converted to
blanks ("") and that seems to fix the problem, even in cases where the
rest of the column is numbers.