Counting records with ADO
in [Programming]
|
From: keiji kounoike "kounoike A | T on 2 Oct 2009 08:41 Hi Geoff But I wonder how you can get the last row using the method you introduced? According to your comments on my previous post, it's not important whether to open a file or not. I think my Sub Getlastrow_Workbook() in my previous post is fast enough to get a last row. you could change this sub to function with arguments if you need to, say, loop many files. Keiji Geoff K wrote: > 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: Geoff K on 2 Oct 2009 12:46 Hi Keiji I can only repeat, I do NOT want to open target wbooks. On your Find(*) method I have been using the same for a long time in many of my procedures. I know it works. But I do NOT want to open target wbooks - it wastes a lot of time. I am now researching another method please see my latest post "Getting data from a closed wbook". Geoff "keiji kounoike" <"kounoike A | T ma.Pik" wrote: > Hi Geoff > > But I wonder how you can get the last row using the method you > introduced? According to your comments on my previous post, it's not > important whether to open a file or not. I think my Sub > Getlastrow_Workbook() in my previous post is fast enough to get a last > row. you could change this sub to function with arguments if you need > to, say, loop many files. > > Keiji
From: michdenis on 2 Oct 2009 23:32 There are not many solutions. If you do not want to open your workbook And If you use ADO (activex data object) to erase data in your database you will necessary end up with empty rows. ADO can erase data but cannot delete any row. If you don't open your database you have one solution left...that one you found with ADO sooner this week. based on my example. "Geoff K" <GeoffK(a)discussions.microsoft.com> a écrit dans le message de groupe de discussion : 48436FCE-27E6-4EFC-B302-8166BA14B83A(a)microsoft.com... Hi Keiji I can only repeat, I do NOT want to open target wbooks. On your Find(*) method I have been using the same for a long time in many of my procedures. I know it works. But I do NOT want to open target wbooks - it wastes a lot of time. I am now researching another method please see my latest post "Getting data from a closed wbook". Geoff "keiji kounoike" <"kounoike A | T ma.Pik" wrote: > Hi Geoff > > But I wonder how you can get the last row using the method you > introduced? According to your comments on my previous post, it's not > important whether to open a file or not. I think my Sub > Getlastrow_Workbook() in my previous post is fast enough to get a last > row. you could change this sub to function with arguments if you need > to, say, loop many files. > > Keiji
From: Geoff K on 3 Oct 2009 07:21
Thanks for your continued interest. As I have said I use an Add-in + a temp wbk + a log wbk. The project interrogates multiple wbks in a folder. ADO is used to read the field names of all wbks to assess if each has the required field names (which may be in any order). At the same time I want to get the last row used to calculate the number of original records. If all wbooks are ok then I use something like SELECT fld1, fld2, fld10, fld22 etc FROM wsheet1 WHERE NOT ISNULL fld2 to extract data to the tmp wbk. Processing continues and results are appended to the log for all wbks in the folder. Unless there is an abnormality there is no need to ever return to a wbk once it has been processed. All that is required is the data within. The wbooks are supplied from outside sources. I have no control over the presentation or quality - and believe me when I say some are absolutely apalling - some arrive without field names! I am now examining the possibility of a wsheet formula which could be inserted into the hidden sheet of my Add-in. Interestingly =MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A) returns the last used row in col A if it is a numeric field. Using MTCH "ZZZ" does the same for a text field. All I need do then is loop through the known fields from my first SELECT statement to get the last used row of each wbk. But right now I have to construct the loop and test. However there is a snag to this - the wbk with the huge bloat goes into an infinite loop but curiously another wbk with a smaller misaligned UsedRange does not. If you have any views on this new approach they would be welcome and thank you again for the continued interest. Geoff "michdenis" wrote: > There are not many solutions. > > If you do not want to open your workbook > And > If you use ADO (activex data object) to erase data in your database > you will necessary end up with empty rows. ADO can erase data > but cannot delete any row. If you don't open your database > you have one solution left...that one you found with ADO sooner this week. > based on my example. > |