Getting data from a closed wbook
in [Programming]
|
Prev: Counting records with ADO
Next: Microsoft.Office.Interop.Excel myexcel = new EXCEL.Application()
From: Jeff on 2 Oct 2009 14:18 Try an ADO solution... 'Requires reference to microsoft Active X Data Objects Lib 2.7 Public Sub QueryWorksheet() Dim Recordset As ADODB.Recordset Dim ConnectionString As String ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source= H:\Test3.xls;" & _ "Extended Properties=Excel 8.0;" Dim SQL As String ' Query based on the worksheet name. SQL = "SELECT * FROM [Sheet1$]" ' Query based on a sheet level range name. ' SQL = "SELECT * FROM [Sales$MyRange]" ' Query based on a specific range address. ' SQL = "SELECT * FROM [Sales$A1:E14]" ' Query based on a book level range name. ' SQL = "SELECT * FROM BookLevelName" Set Recordset = New ADODB.Recordset On Error GoTo Cleanup Call Recordset.Open(SQL, ConnectionString, _ CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _ CommandTypeEnum.adCmdText) Call Sheet1.Range("A1").CopyFromRecordset(Recordset) Cleanup: If (Err.Number <> 0) Then Debug.Print Err.Description End If If (Recordset.State = ObjectStateEnum.adStateOpen) Then Recordset.Close End If Set Recordset = Nothing End Sub "Geoff K" wrote: > Thank you. But again, please read my question. > > Yes it is simple but that is for an open wbook. I want to get the last row > from a CLOSED wbook. > > Geoff > > "Jeff" wrote: > > > Returning the last used row is pretty simple, Here are a Function and Sub > > procedure examples > > > > Public Sub Geoff_K() > > Dim lRow As Long > > > > lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row > > End Sub > > > > Public Function GetLastRow() As Long > > Dim lRow As Long > > > > lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row > > > > GetLastRow = lRow > > End Function >
From: john on 2 Oct 2009 14:23 Geoff, Use a helper cell in the closed workbook and add formula like this: =COUNTA(A:A) This should give you the total number of rows You can then use following procedure to copy all the data from required sheet / range in closed workbook using formula. If you use a hidden sheet to store this data your lookup formula can then reference the active workbook. May need some work but hope gives you some ideas. Sub GetData() Dim mydata As String Dim rcount As String Dim lr As Long 'helper cell rcount = "='C:\[MyTestBook.xls]Sheet1'!$C$1" 'link to worksheet With ThisWorkbook.Worksheets(1) With .Range("C1") .Formula = rcount 'convert formula to text .Value = .Value lr = .Value End With 'data location & range to copy mydata = "='C:\[MyTestBook.xls]Sheet1'!$A$1:$A$" & lr With .Range("A1:A" & lr) .Formula = mydata 'convert formula to text .Value = .Value End With End With End Sub -- jb "Geoff K" wrote: > Hi > Getting data from a closed wbook. > These wsheet formulae work fine on numerical fields but not on text. > > Gets from a closed wbook the value in the last used cell of a column. > =LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A) > > Gets from a closed wbook the last used row number of a column. > =MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A) > > But if either meet with a text field or even if 99^99 is substituted with > "ZZZ" then Excel goes into an infinite loop. > > How can the formulae be made universal to look for either numeric or text > fields? Or if that is not possible then how might it made to work in a text > field? > > T.I.A > > Geoff >
From: Geoff K on 2 Oct 2009 14:59 I appreciate you are trying to help. But again please read my question - it is very specific. I am already using ADO but I need the last used row before I begin to extract data. Using SQL to get a count with SELECT COUNT(*) does not work if a wbook has been saved with an out of line UsedRange. One of the wbooks I have come across had a UsedRange last cell of AF50918 whereas the real last cell was S98. When I did a record count on that wbook it returned 50917 instead of 97. Using 2 associated recordsets and looping through all the fields provided the correct last row / record count but it was painfully slow because it had to work its way through 50,918 rows on X number of fields. I had already been through the usual alternative methods until I came across the method detailed in my post. I thought this might be worth a shot. It works if the first field is numeric and doesn't throw its toys out of the cot if it encounters a text field subsequently it just returns N/A. But if seems if the first field of a wbook is text then it goes into an infinite loop. If I can get it right I can install formulae on the hidden wsheet in my Add-in and pull in the last used row number and then simply calculate the number of original records before I extract data from the closed wbook. Hope that clarifies. Geoff "Jeff" wrote: > Try an ADO solution... > 'Requires reference to microsoft Active X Data Objects Lib 2.7
From: Geoff K on 2 Oct 2009 15:17 Hi John I do not want to ever open source wbooks if I can possibly avoid it. They are used once only to extract data and are not used again unless there are anomalies in the final analysis. Opening and closing wbooks wastes time if you only need their data and there are so many of them. Somehow I have to get the real last row BEFORE I begin to extract data because I need to establish the original record count. I use ADO for extraction and it works fine. But when I use a SELECT COUNT (*) to get a record count it gets messed up sometimes because a wbook may have been saved with an out of line UsedRange. One wbook I came across showed the last UsedRange call as AF50918 instead of S98. That produced an original record count of 50917 instead of 97. I've been through a number of alternatives then came across the method which I posted. But it doesn't work consistently. It seems ok if the first field in a closed wbook is numeric - and it reurns N/A if it encounters a text field - but if the first field is a text field then it throws a wobbler. If I can get the thing to work correctly I can install formulae on the hidden wsheet of my Add-in and loop through all the wbooks in the folder and calculate the number of original records in each. Geoff "john" wrote: > Geoff, > > Use a helper cell in the closed workbook and add formula like this: > > =COUNTA(A:A) >
From: john on 2 Oct 2009 15:41
sorry if first suggestion along wrong lines. not tested but does doing this solve text / numeric problem? =COUNTA('C:\Path\[File.xls]Sheet1'!A:A) -- jb "Geoff K" wrote: > Hi John > I do not want to ever open source wbooks if I can possibly avoid it. > > They are used once only to extract data and are not used again unless there > are anomalies in the final analysis. Opening and closing wbooks wastes time > if you only need their data and there are so many of them. > > Somehow I have to get the real last row BEFORE I begin to extract data > because I need to establish the original record count. > > I use ADO for extraction and it works fine. But when I use a SELECT COUNT > (*) to get a record count it gets messed up sometimes because a wbook may > have been saved with an out of line UsedRange. > > One wbook I came across showed the last UsedRange call as AF50918 instead of > S98. That produced an original record count of 50917 instead of 97. > > I've been through a number of alternatives then came across the method which > I posted. But it doesn't work consistently. It seems ok if the first field > in a closed wbook is numeric - and it reurns N/A if it encounters a text > field - but if the first field is a text field then it throws a wobbler. > > If I can get the thing to work correctly I can install formulae on the > hidden wsheet of my Add-in and loop through all the wbooks in the folder and > calculate the number of original records in each. > > Geoff > > "john" wrote: > > > Geoff, > > > > Use a helper cell in the closed workbook and add formula like this: > > > > =COUNTA(A:A) > > > |