Counting records with ADO

[Geoff K]

Hi Keiji
I already have an algorthm which works very well on open wbooks. It is very
quick. My post is about saving running time by NOT opening and closing
wbooks.
I will now experiment with the suggestion from michdenis.
Thank you for your contribution.
Geoff

 

[Geoff K]

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]

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" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
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

 

[Geoff K]

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

 

[keiji kounoike]

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

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

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" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
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

 

[Geoff K]

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

 

[michdenis]

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" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
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]

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]

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

 

[michdenis]

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" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
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

 

[Geoff K]

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