Linking an Excel file in Access with mixed data types in columns and #Num!
in [Help]
|
Prev: PopUp Date Picker Form
Next: Securing data
From: Salad on 4 Nov 2009 13:37 Roger wrote: > On Nov 4, 10:31 am, Salad <o...(a)vinegar.com> wrote: > >>Roger wrote: >> >>>On Nov 3, 3:05 pm, Salad <o...(a)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 Thanks for the validation. It will be very useful for me in my current project. Don't need any "gotchas" down the road. :)
From: Chuck Grimsby on 4 Nov 2009 21:34
On Nov 4, 12:37 pm, Salad <o...(a)vinegar.com> wrote: > Roger wrote: > > On Nov 4, 10:31 am, Salad <o...(a)vinegar.com> wrote: > > >>Roger wrote: > > >>>On Nov 3, 3:05 pm, Salad <o...(a)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. :) 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. |