Counting records with ADO
in [Programming]
|
From: michdenis on 30 Sep 2009 09:30 There is an another approch to solve your problem. You can use a "Name" of the collection "Names" to store "the" last row of your sheet each time your workbook is Deactivated... or Closed (Sub Workbook_BeforeClose(Cancel As Boolean) Insert in the ThisWorkbook of your projectvba of your workbook this code : '----------------------------- Private Sub Workbook_Deactivate() Dim LastRow As String On Error Resume Next With Sheet1 LastRow = .Cells.Find(What:="*", _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row On Error GoTo 0 End With ThisWorkbook.Names.Add "LastRow", LastRow, False End Sub '----------------------------- Now, from any other workbook, you can easily read the value of this "NAME" using this type of code : '--------------------------------- Sub Test() Dim LastRow As Long LastRow = Application.ExecuteExcel4Macro("'C:\MyPath\MyWorkbook.xls'!LastRow") End Sub '--------------------------------- A fast and simple approach ! "Geoff K" <GeoffK(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion : AFCEC3EB-3FD5-455A-9B53-8957971FF3AC(a)microsoft.com... Hi Thanks for the example. I have got it to work on a single field, the error was caused simply by reading a text field not a numeric. :) I now need to expand it to obtain the last row of the whole table. However this wbook UsedRange is so bloated, AF50918 compared to S98, and the loop used on duplicates is very slow even on a single field. I fear it will undo all the run time advantage of not opening and closing the wbook. But it is at least one way and worth further experimentation. Geoff "michdenis" wrote: > There is an exemple in this file : http://cjoint.com/?jDndv2hXXE
From: Geoff K on 1 Oct 2009 04:34 Hi I was able to get the UsedRange code to loop through all fields for all wbooks in a folder but as I suspected the bloat caused it to run very slowly. But the method does work with a bit of adaptation. Excel4Macro seems to hold great promise: In my set up the parent is an Add-in. I use a temp wbook to dump data extracts into for further work. The target wbooks are never opened. If I put the "LastRow" code into the Add-in ThisWorkBook mod and put "Test" into a std module of the Add-in, save and reopen then run "Test" I get a GetOpenFilename dialog. If I navigate to a wbook and select a wbook it errors with Type Mismatch. If I substitute this in "Test" I don't get the dialog but it still errors with Type Mismatch. LastRow = Application.ExecuteExcel4Macro("'C:\Path\[Dels.xls]Sheet1'!LastRow"). I then changed Dim LastRow As String in "LastRow" to a Long but it did not make any difference because "Test" errored out. I'm not able to get beyond this error to test if LastRow will work. Not being familiar with Excel4Macro yet - can LastRow in the line above refer to another procedure rather than a range? Geoff "michdenis" wrote: > There is an another approch to solve your problem. > > You can use a "Name" of the collection "Names" to store > "the" last row of your sheet each time your workbook is > Deactivated... or Closed (Sub Workbook_BeforeClose(Cancel As Boolean) > > Insert in the ThisWorkbook of your projectvba of your workbook > this code : > '----------------------------- > Private Sub Workbook_Deactivate() > Dim LastRow As String > On Error Resume Next > With Sheet1 > LastRow = .Cells.Find(What:="*", _ > LookIn:=xlValues, _ > SearchOrder:=xlByRows, _ > SearchDirection:=xlPrevious).Row > On Error GoTo 0 > End With > > ThisWorkbook.Names.Add "LastRow", LastRow, False > End Sub > '----------------------------- > > Now, from any other workbook, you can easily read the value > of this "NAME" using this type of code : > '--------------------------------- > Sub Test() > Dim LastRow As Long > LastRow = Application.ExecuteExcel4Macro("'C:\MyPath\MyWorkbook.xls'!LastRow") > End Sub > '--------------------------------- > > A fast and simple approach ! > > > > "Geoff K" <GeoffK(a)discussions.microsoft.com> a écrit dans le message de groupe de > discussion : AFCEC3EB-3FD5-455A-9B53-8957971FF3AC(a)microsoft.com... > Hi > Thanks for the example. I have got it to work on a single field, the error > was caused simply by reading a text field not a numeric. :) > > I now need to expand it to obtain the last row of the whole table. However > this wbook UsedRange is so bloated, AF50918 compared to S98, and the loop > used on duplicates is very slow even on a single field. I fear it will undo > all the run time advantage of not opening and closing the wbook. > > But it is at least one way and worth further experimentation. > > Geoff > > "michdenis" wrote: > > > There is an exemple in this file : http://cjoint.com/?jDndv2hXXE >
From: keiji kounoike "kounoike A | T on 1 Oct 2009 07:52 I think that you need to write Sub Workbook_Deactivate() into your data book and the Sub Test() into the parent. In my thought, your data file must be opened when you use ExecuteExcel4Macro. so, I think this is not so efficient as you think. Keiji Geoff K wrote: > Hi > I was able to get the UsedRange code to loop through all fields for all > wbooks in a folder but as I suspected the bloat caused it to run very slowly. > But the method does work with a bit of adaptation. > > Excel4Macro seems to hold great promise: > In my set up the parent is an Add-in. I use a temp wbook to dump data > extracts into for further work. The target wbooks are never opened. > > If I put the "LastRow" code into the Add-in ThisWorkBook mod and put "Test" > into a std module of the Add-in, save and reopen then run "Test" I get a > GetOpenFilename dialog. If I navigate to a wbook and select a wbook it > errors with Type Mismatch. > > If I substitute this in "Test" I don't get the dialog but it still errors > with Type Mismatch. > LastRow = > Application.ExecuteExcel4Macro("'C:\Path\[Dels.xls]Sheet1'!LastRow"). > > I then changed Dim LastRow As String in "LastRow" to a Long but it did not > make any difference because "Test" errored out. > > I'm not able to get beyond this error to test if LastRow will work. Not > being familiar with Excel4Macro yet - can LastRow in the line above refer to > another procedure rather than a range? > > Geoff > > "michdenis" wrote: > >> There is an another approch to solve your problem. >> >> You can use a "Name" of the collection "Names" to store >> "the" last row of your sheet each time your workbook is >> Deactivated... or Closed (Sub Workbook_BeforeClose(Cancel As Boolean) >> >> Insert in the ThisWorkbook of your projectvba of your workbook >> this code : >> '----------------------------- >> Private Sub Workbook_Deactivate() >> Dim LastRow As String >> On Error Resume Next >> With Sheet1 >> LastRow = .Cells.Find(What:="*", _ >> LookIn:=xlValues, _ >> SearchOrder:=xlByRows, _ >> SearchDirection:=xlPrevious).Row >> On Error GoTo 0 >> End With >> >> ThisWorkbook.Names.Add "LastRow", LastRow, False >> End Sub >> '----------------------------- >> >> Now, from any other workbook, you can easily read the value >> of this "NAME" using this type of code : >> '--------------------------------- >> Sub Test() >> Dim LastRow As Long >> LastRow = Application.ExecuteExcel4Macro("'C:\MyPath\MyWorkbook.xls'!LastRow") >> End Sub >> '--------------------------------- >> >> A fast and simple approach ! >> >> >> >> "Geoff K" <GeoffK(a)discussions.microsoft.com> a écrit dans le message de groupe de >> discussion : AFCEC3EB-3FD5-455A-9B53-8957971FF3AC(a)microsoft.com... >> Hi >> Thanks for the example. I have got it to work on a single field, the error >> was caused simply by reading a text field not a numeric. :) >> >> I now need to expand it to obtain the last row of the whole table. However >> this wbook UsedRange is so bloated, AF50918 compared to S98, and the loop >> used on duplicates is very slow even on a single field. I fear it will undo >> all the run time advantage of not opening and closing the wbook. >> >> But it is at least one way and worth further experimentation. >> >> Geoff >> >> "michdenis" wrote: >> >>> There is an exemple in this file : http://cjoint.com/?jDndv2hXXE
From: Geoff K on 1 Oct 2009 10:42 Hi Keiji You are correct. It appears as if the method will only work if a name is created in the target wbook and it remains open. However I came across this link http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/ This works when both Function and calling sub are installed in a std mod in my Add-in and the target wbook is closed. Geoff "keiji kounoike" <"kounoike A | T ma.Pik" wrote: > I think that you need to write Sub Workbook_Deactivate() into your data > book and the Sub Test() into the parent. In my thought, your data file > must be opened when you use ExecuteExcel4Macro. so, I think this is not > so efficient as you think. > > Keiji
From: michdenis on 2 Oct 2009 06:50 This precedure can read the value of a name in a closed Workbook But you need to open the workbook to create a name ! '------------------------------------ Sub Test() Dim LastRow As Long LastRow = Application.ExecuteExcel4Macro("'C:\MyPath\MyWorkbook.xls'!LastRow") End Sub '------------------------------------ "Geoff K" <GeoffK(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion : 6DCF6886-639F-4138-84AF-7C8D805CBE28(a)microsoft.com... Hi Keiji You are correct. It appears as if the method will only work if a name is created in the target wbook and it remains open. However I came across this link http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/ This works when both Function and calling sub are installed in a std mod in my Add-in and the target wbook is closed. Geoff "keiji kounoike" <"kounoike A | T ma.Pik" wrote: > I think that you need to write Sub Workbook_Deactivate() into your data > book and the Sub Test() into the parent. In my thought, your data file > must be opened when you use ExecuteExcel4Macro. so, I think this is not > so efficient as you think. > > Keiji
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: PDF export add-in Next: Getting data from a closed wbook |