ADOBE Connection to open Excel workbook
Hi All,
Windows 7 64 Bit, Office 2007 32 Bit, All updates for Windows & Office installed. I have am building a reasonably complex workbook in which an sql query is run on a sheet within the same workbook. I have used the connection string below and l am led to believe that this is the latest version which supports Excel 2007 & 2010 and removes the limitations of the 65536 rows / records. ADODB.Connection.Open "driver={Microsoft Excel Driver (*.xls, *.xlsx,*.xlsm,*.xlsb)};driverid=1046;dbq=MyExcelFi le.xlsm;" On my home computer where l developed the solution everything runs perfectly. On my colleagues laptop which has EXACTLY the same software installed (we have both checked the software version numbers several times) Excel crashes when the recordset exceeds 65536 rows! It would appear that the only difference in the 2 machines is the amount of ram available. The machine working has 16gb the machine not working only has 2gb. However l believe that Excel 32 bit is restricted to 2gb anyway. The data source files are xl2003 but to overcome the issue that Excel interprets this data as xl2003 data the whole workbook in which the qery exists is saved as a xl2010 file before executing the query. Anyway this doesn't answer why it should work on 1 machine but not another. Anybody got any idea's, this has really got me stumped! Regards Michael |
ADOBE Connection to open Excel workbook
Not sure this will help but...
I use the JET ADODB provider in XL11 and earlier versions of Excel, the ACE ADODB provider in XL12 and later versions. I don't know what a Query uses but I assume Excel 12 and later will substitute the provider engine in the earlier file with its ACE provider. In any case, there should not be a row limitation in the later versions, but I expect issues to appear trying to open a later version file in an early version of Excel where the rows limitation exists, AND the early version has no idea what the ACE provider is. AFAIK, early version queries should continue to work in the later versions BUT NOT the other way around! Regardless, if you have tables with that many records then you're using the wrong format for storing the data. While doable in Excel workbooks it's NOT practical since a proper database file is readily available in the same MSO suite. Also, you don't have to physically open workbooks being used as data tables using ADO. You can load a table (worksheet) into a recordset and work with it same as if it was in a db file. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
ADOBE Connection to open Excel workbook
Hi Gary,
I apprecaite the response as there is very little about this on the internet that is clearly explained! I thought the ACE ADODB provider was meant to be for both XL2007 & XL2010 (eg 11.0 & 14.0, 8.0 being XL2003)? I also thought that the ACE provider was brought in to replace the JET because of the row limitations in JET? So although JET will work quite happily in XL2007 & XL2010 it will bomb out once it reaches 65536 rows whereas the ACE provider should continue past this mark. Or have l got it totally wrong? The source data is provided by 60 or so files that are dumped from a mainframe each month. These are in XL2003 format and need summarising which takes them over the 65536 rows, that is why they are opened, copied, pasted into XL2007, and hence why the workbook being queried is open eg querying itself. It is the end users decision to use XL2007. l have banged on about efficiency, proper db applications etc, but they are accountants and understand XL so that is what they want! I am wondering whether it might be worth trying the querytable route? Any ideas welcome Regards Michael |
ADOBE Connection to open Excel workbook
on 12/21/2011, michael beckinsale supposed :
Hi Gary, I apprecaite the response as there is very little about this on the internet that is clearly explained! I thought the ACE ADODB provider was meant to be for both XL2007 & XL2010 (eg 11.0 & 14.0, 8.0 being XL2003)? I also thought that the ACE provider was brought in to replace the JET because of the row limitations in JET? So although JET will work quite happily in XL2007 & XL2010 it will bomb out once it reaches 65536 rows whereas the ACE provider should continue past this mark. Or have l got it totally wrong? Basically, this is the understanding I have of the ACE provider. I use a version-aware function to set up my connection string according to which provider is appropriate for the running instance of Excel. Otherwise, everything else is the same as far as building queries/recordsets goes. Example: Construct a connection string something like this: If Application.Version = 12 Then 'use ACE provider connection string Else 'use JET provider connection string End If This precludes that you'll have to construct 2 separate connection strings. You could use constants for this: Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;" Const sExtProps As String = "Excel 8.0;" Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;" Const sExtProps12 As String = "Excel 12.0 Xml;" If you know the data source beforehand, you could configure your code something like this: <aircode ' Use a var to hold data source sDataSource = "<FullPathAndFilename" If Application.Version = 12 Then 'use ACE provider connection string sConnect = "Provider=" & sProvider12 & _ "Data Source=" & sDataSource & _ "Extended Properties=" & sExtProps12 Else 'use JET provider connection string sConnect = "Provider=" & sProvider & _ "Data Source=" & sDataSource & _ "Extended Properties=" & sExtProps End If </aircode ' Construct your SQL statement sSQL = "SELECT * FROM..." ' Grab the data into a recordset Set rsData = New ADODB.Recordset rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText The source data is provided by 60 or so files that are dumped from a mainframe each month. These are in XL2003 format and need summarising which takes them over the 65536 rows, that is why they are opened, copied, pasted into XL2007, and hence why the workbook being queried is open eg querying itself. It is the end users decision to use XL2007. l have banged on about efficiency, proper db applications etc, but they are accountants and understand XL so that is what they want! I am wondering whether it might be worth trying the querytable route? Any ideas welcome Regards Michael Hi Michael, The format I'm familiar with as a DB 'dump' is plain text or CSV. There are several ways to handle the import into Excel. Doing this manually in XL12+ (2007 or 2010) should be no issue regard row limitations. I suspect when you say the mainframe is dumped into an early XLS that this is an actual workbook file rather than a CSV text file. It would make more sense to me to 'dump' the data into text/csv files, making them available to any DB provider (not just ADODB). I use the ADO methodology outlined by Rob Bovey and I'm happy to say that it serves me well for my needs thus far. You can download working examples here... http://www.appspro.com/conference/Da...rogramming.zip Another way to go (in the case of text/csv files) might be to use normal VB I/O to 'dump' the file contents into an array and 'dump' that into a spreadsheet. This is a memory-sensitive process and so you might have to read the file in blocks. Managing the row count is trivial since you can use a loop with a Step option to parse large files into separate worksheets. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com