Getting data from a closed wbook
in [Programming]
|
Prev: Counting records with ADO
Next: Microsoft.Office.Interop.Excel myexcel = new EXCEL.Application()
From: Geoff K on 2 Oct 2009 12:53 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: Jeff on 2 Oct 2009 13:00 Ron de Bruin covers how to do that. http://www.rondebruin.nl/copy7.htm "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 13:13 Hi Jeff Please read the text of my question. My aim is to find the last used row of the source wbook before I begin to extract data. Geoff "Jeff" wrote: > Ron de Bruin covers how to do that. > > http://www.rondebruin.nl/copy7.htm > > "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: Jeff on 2 Oct 2009 13:26 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 "Geoff K" wrote: > Hi Jeff > > Please read the text of my question. > > My aim is to find the last used row of the source wbook before I begin to > extract data. > > Geoff > > "Jeff" wrote: > > > Ron de Bruin covers how to do that. > > > > http://www.rondebruin.nl/copy7.htm > > > > "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 13:36
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 |