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: Roger on 4 Nov 2009 12:00 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.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
From: Roger on 4 Nov 2009 12:06 On Nov 4, 10:00 am, Roger <lesperan...(a)natpro.com> 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- 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
From: Salad on 4 Nov 2009 12:31 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.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 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.
From: Salad on 4 Nov 2009 12:35 Roger wrote: > On Nov 4, 10:00 am, Roger <lesperan...(a)natpro.com> 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- 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 Yes. My HDR was set to No. Good catch.
From: Roger on 4 Nov 2009 12:53
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 |