Help!!! Excel column is invalid when spreadsheet is linked as a ta Balex 06.07.2006 14:41:02 Hi, I'm having a very rare, but deadly problem with Excel and Access. Every day, I get data in an application-generated Excel sheet with always the same structure, and I link it into an Access database by using the command: DoCmd.TransferSpreadsheet acLink, , "tbInput", strPathAndFileName, False I process that linked table with VBA code and store some of its columns and data in another, permanent table in Access. This works perfectly for about 95% of the cases. However, at unpredictable times, suddenly a column in Excel which was full of numbers (but for the first 3 rows because these contain headers) ends up, after the linking, showing only "#Num!" where the numbers were. That crashes my code (with numeric overflow error) which is reading the content of the spreadsheet, because I'm expecting a number in that column, not an "#Num!". I can't make out when this is happening, i.e. what's the cause. Moreover, this happens on my users PC's, but NOT on my PC if I copy the DB and the Excel sheet ! It's only one column, and when it happens, it's always affecting the same column. Has any of you had this problem before, if yes what is it due to ? How can I avoid it ? I'd be VERY thankful for any help and hints on that one. Regards Balex

Re: Help!!! Excel column is invalid when spreadsheet is linked as a ta John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 06.07.2006 20:36:47 Hi Balex, I'm surprised it works 95% of the time. AFAIK this DoCmd.TransferSpreadsheet acLink, , "tbInput", _ strPathAndFileName, False tells Jet to link to the first sheet of the workbook, starting at cell A1 and continuing to the bottom right cell of the UsedRange, even though - you tell us - rows 1 to 3 contain heading information. I suspect that the heading rows are confusing the algorithm that Jet uses to work out field types. The reason it behaves differently on different computers may reflect different versions or service packs of Access or Jet, or perhaps different registry settings (see http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/ for more about these). A good workround might be to use Automation to open the workbook and delete the first three rows of the sheet, then save and close before importing. On Thu, 6 Jul 2006 07:41:02 -0700, Balex <Balex[ at ]discussions.microsoft.com> wrote: [Quoted Text] >Hi, > >I'm having a very rare, but deadly problem with Excel and Access. Every >day, I get data in an application-generated Excel sheet with always the same >structure, and I link it into an Access database by using the command: > >DoCmd.TransferSpreadsheet acLink, , "tbInput", strPathAndFileName, False > >I process that linked table with VBA code and store some of its columns and >data in another, permanent table in Access. > >This works perfectly for about 95% of the cases. However, at unpredictable >times, suddenly a column in Excel which was full of numbers (but for the >first 3 rows because these contain headers) ends up, after the linking, >showing only "#Num!" where the numbers were. That crashes my code (with >numeric overflow error) which is reading the content of the spreadsheet, >because I'm expecting a number in that column, not an "#Num!". I can't make >out when this is happening, i.e. what's the cause. Moreover, this happens on >my users PC's, but NOT on my PC if I copy the DB and the Excel sheet ! It's >only one column, and when it happens, it's always affecting the same column. > >Has any of you had this problem before, if yes what is it due to ? How can I >avoid it ? > >I'd be VERY thankful for any help and hints on that one. > >Regards >Balex -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: Help!!! Excel column is invalid when spreadsheet is linked as Balex 07.07.2006 11:52:01 Thanks a lot, John, I saw the link you indicate also in another mail and I guessed the problems were lying in the registry entries, which they were (Text instead of Majority Type), so I changed my settings and tried on my PC. Unfortunately, I cannot make the same change in the registry of the users' PCs, because they do not have admin rights on their PC (big org...), I had to fool Excel there by adding numbers in the header rows, but that's a boring and confusing thing to do for the users on a daily basis, so, next to your idea of stripping the header rows, I'm looking at other alternatives like having the spredsheet format changed, or getting right at the source of data, which is a Sybase DB. Balex "John Nurick" wrote: [Quoted Text] > Hi Balex, > > I'm surprised it works 95% of the time. AFAIK this > > DoCmd.TransferSpreadsheet acLink, , "tbInput", _ > strPathAndFileName, False > > tells Jet to link to the first sheet of the workbook, starting at cell > A1 and continuing to the bottom right cell of the UsedRange, even though > - you tell us - rows 1 to 3 contain heading information. > > I suspect that the heading rows are confusing the algorithm that Jet > uses to work out field types. The reason it behaves differently on > different computers may reflect different versions or service packs of > Access or Jet, or perhaps different registry settings (see > http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/ > for more about these). > > A good workround might be to use Automation to open the workbook and > delete the first three rows of the sheet, then save and close before > importing. > > > On Thu, 6 Jul 2006 07:41:02 -0700, Balex > <Balex[ at ]discussions.microsoft.com> wrote: > > >Hi, > > > >I'm having a very rare, but deadly problem with Excel and Access. Every > >day, I get data in an application-generated Excel sheet with always the same > >structure, and I link it into an Access database by using the command: > > > >DoCmd.TransferSpreadsheet acLink, , "tbInput", strPathAndFileName, False > > > >I process that linked table with VBA code and store some of its columns and > >data in another, permanent table in Access. > > > >This works perfectly for about 95% of the cases. However, at unpredictable > >times, suddenly a column in Excel which was full of numbers (but for the > >first 3 rows because these contain headers) ends up, after the linking, > >showing only "#Num!" where the numbers were. That crashes my code (with > >numeric overflow error) which is reading the content of the spreadsheet, > >because I'm expecting a number in that column, not an "#Num!". I can't make > >out when this is happening, i.e. what's the cause. Moreover, this happens on > >my users PC's, but NOT on my PC if I copy the DB and the Excel sheet ! It's > >only one column, and when it happens, it's always affecting the same column. > > > >Has any of you had this problem before, if yes what is it due to ? How can I > >avoid it ? > > > >I'd be VERY thankful for any help and hints on that one. > > > >Regards > >Balex > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email. >