Hi Keiji
It is not easy to create a wbook where the UsedRange is out of line then
save it. I have the advantage (or not) in being able to work with 3 examples
sourced externally. It may be you are not able to replicate my problem
because you do not have a misaligned wbook to hand?
As to the issue I have with counting records - I have been able to count
both non-nulls and nulls in the wsheet. As I expected when the 2 counts are
summed the total is 50917 not 97 as I want.
First - Count(*). I don't beleive it actually counts anything. It gets
instead the UsedRange last row address and then makes an adjustment depending
on whether HDR is set as yes or no in the connection string.
Please see this MS response to a similar question
http://www.pcreview.co.uk/forums/thread-3284796.php
Second - RecordCount. In counting nulls or non-nulls it will will count to
EOF and that means the last record which in this case is at row 50918.
So at the moment I do not see any way of arriving at the correct record
count of 97 whern UsedRange is mis-aligned. Remember the objective is to
leave wbooks closed. It appears therefore that unless the UsedRange is
aligned with the real last cell there is no way a true count can ever be
obtained with these methods.
But I would love to be proved wrong.
Geoff
:
Hi Geoff
I set the reference to some library in my macro. but using your macros,
I can't replicate your problem. First i put data into IV63556, then i
run the macro main below, it returns like this
SELECT COUNT(*) FROM [Sheet1$A1:IU65536]; 65535 1
SELECT * FROM [Sheet1$A1:IU65536]; a 65535
after this, I delete data in IV63556 and run the macro main, i get the
result like this
SELECT COUNT(*) FROM [Sheet1$A1:IU65536]; 33 1
SELECT * FROM [Sheet1$A1:IU65536]; a 33
I think you can't get a correct number of data with using RecordsCount
after having executed SELECT COUNT(*) FROM...
It always returns 1.
I added some lines in your code and run main. the result was like above.
Sub main()
GetData "c:\adodata.xls", "Sheet1$A1:IU65536", Range("a1")
End Sub
Public Sub GetData(SourceFile As Variant, SourceRange As String,
TargetRange As Range)
Dim rsCon As Object
Dim rsData2 As Object
Dim szConnect As String
Dim szSQL2 As String
Dim rsCount2 As Variant
Dim rsCount3 As Long
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"
Set rsCon = CreateObject("ADODB.Connection")
Set rsData2 = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
szSQL2 = "SELECT COUNT(*) FROM [" & SourceRange$ & "];"
rsData2.Open szSQL2, rsCon, adOpenStatic
rsCount2 = rsData2(0)
rsCount3 = rsData2.RecordCount
Debug.Print szSQL2; vbTab; rsCount2; vbTab; rsCount3
'Added these 6 lines from here
rsData2.Close
szSQL2 = "SELECT * FROM [" & SourceRange$ & "];"
rsData2.Open szSQL2, rsCon, adOpenStatic
rsCount2 = rsData2(0)
rsCount3 = rsData2.RecordCount
Debug.Print szSQL2; vbTab; rsCount2; vbTab; rsCount3
If rsData2.State = adStateOpen Then rsData2.Close
Set rsData2 = Nothing
rsCon.Close
Set rsCon = Nothing
End Sub
Keiji
Geoff K wrote:
Hi Keiji
Thank you for your responses so far.
FWIW please note in my first post the example 3 field table came out wrongly
formatted when posted. It was meant to show 2 records beneath each of the 3
fields but turned out as 6 under Field1.
I note your code requires references to ADODB whereas I use CreateObject.
Can I ask what you are using?
The wbook in question shows:
UsedRange last cell = AF50918
Real last cell = S98
I use szSQL1 to read and download field names to a temp wsheet using
szSQL1 = "SELECT * FROM [" & SourceRange$ & "];"
In this initial query SourceRange is defined as A1:IU1. Note IU and not
IV1. This overcomes the error 'Too many fields defined' which I have also
come across in some wbooks. Whilst not perfect I have never known all fields
to be used in any wbook so I have 'got away' with it so far.
Therefore from szSQL1 I know the real last field is in column S and I also
know if the wbook contains the required fields. I can then redefine
SourceRange in szSQL2 for this wbook as A1:S65536
Turning now to your suggestion, Debug returns
szSQL2 = SELECT COUNT(*) FROM [A1:S65536]
rsCount2 = rsData2(0) returns 50917
rsCount3 = rsData2.RecordCount returns 1
It does not seem to matter whether I use Sheet1$ or the defined range.
Start code:
Public Sub GetData(SourceFile As Variant, SourceRange As String, TargetRange
As Range)
Dim rsCon As Object
Dim rsData2 As Object
Dim szConnect As String
Dim szSQL2 As String
Dim rsCount2 As Long
Dim rsCount3 As Long
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"
Set rsCon = CreateObject("ADODB.Connection")
Set rsData2 = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
szSQL2 = "SELECT COUNT(*) FROM [" & SourceRange$ & "];"
rsData2.Open szSQL2, rsCon, adOpenStatic
rsCount2 = rsData2(0)
rsCount3 = rsData2.RecordCount
Debug.Print szSQL2; vbTab; rsCount2; vbTab; rsCount3
If rsData2.State = adStateOpen Then rsData2.Close
Set rsData2 = Nothing
rsCon.Close
Set rsCon = Nothing
End Sub
End code
Geoff
:
Hi Geoff
Give this one a try. This would give you the last row of data in the
whole Worksheet. To be more accurate, the last row of data minus 1
because this assumes the first row is a header.
Sub ADOtest()
Dim oConn As ADODB.Connection
Dim Table1 As String
Dim Query2 As String
sSourceData = "c:\adodata.xls" '<<==Change to Your file's name
Table1 = "[Sheet1$]" '<<==Change to sheet's name plus "$"
Query2 = "Select * from " & Table1
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sSourceData & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
oRS.Open Query2, oConn, adOpenStatic
MsgBox oRS.RecordCount
End Sub
Keiji
Geoff K wrote:
Hi Keiji
Table1 is just an example name. In reality it represents the field name
range which varies from 1 wbook to another. Wbook1 could be "A1:T65536"
Wbook2 "A1:AM65536".
So SELECT [Fax] FROM [A1:AJ65536$]. "Fax" might be in "D1" or "Z1" or "N1".
I would like to find the last row of data in the whole wsheet but UsedRange
can sometimes stop me doing that accurately. My next thought was I can
easily count the data records in Fax with SELECT COUNT(Fax) and if I can then
count the blanks then that might just give me the true extent of the wsheet.
But I have my doubts because I think perhaps the 2 sums will still add up to
the same figure as the much exagerated UsedRange.
In my example I showed that 1 wbook I used had a UsedRange of AF50918 but
the real last cell was only S98. So lets say the real records in Fax were
72. What I was hoping to do was count the blanks in Fax and get 25. i.e.
header +72 +25 = 98. But what I fear will happen is header +72+50845 = 50918.
Hope this clarifies my problem.
Geoff.
:
It seems Table1 to be a named range. What's the definition of Table1? Do
you want to know the last row number where data reside or a total number
of non blank data in each fields?
Keiji
Geoff K wrote:
Hi
I'm using ADO to retrieve data from unopened wbooks. One of the first tasks
is to get the number of original records from the wsheet but I have hit a
UsedRange
problem.
I've used this for speed:
Query2 = "SELECT COUNT(*) FROM Table1;"
rsData2.Open Query2, rsCon, adOpenStatic
rsCount2 = rsData2(0)
It generally succeeds and returns an accurate count but occasionally it
fails because a wbook has been saved with a huge UsedRange which does not
match the real data. For example doing a Ctrl + End on one wbook showed the
last cell as AF50918 whereas the real last cell was only S98.
I have tried getting the max value using a loop with
Query2 = "SELECT COUNT( column ) FROM Table1;"
But this will not necessarily return the correct count. For example it will
return the count as 2 instead of 6 from this data.
Field1 Field2 Field3
xxx
xxx
xxx
xxx
xxx
xxx
So how do I get the real count when dealing with a UsedRange problem in an
unopened wbook?
T.I.A.
Geoff