Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
Hi
I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
It works fine with me.
Could you mail me that workbook that gives you the wrong answer? RBS "Geoff K" wrote in message ... Hi I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the
UsedRange reflects the real data range. Excel4Macros don't work properly because the code just hangs. All the above fail to return correct results whenever a wbk has been saved with a UsedRange flaw. The only method which does work is the 2 recordset I mentioned earlier but that is very slow. Unfortunately I am not able to supply the 2 wbks with known UsedRange flaws because of Data Protection. If they did not contain details of names, jobs, addresses and telephone numbers you would be very welcome to have a look. And of course I cannot delete the data as that would reset the UsedRange. If you can think of a way to create a wbk with an incorrect UsedRange and employ any of the above methods then you would make the same observations, I am certain. Unfortunately I have no control over theses wbks which are supplied from outside sources. The standard of presentation is appalling - hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of line characters - some even without any field names - and of course some with a flawed UsedRange. Geoff "RB Smissaert" wrote: It works fine with me. Could you mail me that workbook that gives you the wrong answer? RBS "Geoff K" wrote in message ... Hi I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of
line characters - some even without any field names OK, I hadn't tested for all that. Did you try the latest ADO code I posted? Can't you produce a demo wb that has (all of) the above problems and make it fail with ADO code? RBS "Geoff K" wrote in message ... I agree, SELECT COUNT(*), RecordCount, GetRows all work fine when the UsedRange reflects the real data range. Excel4Macros don't work properly because the code just hangs. All the above fail to return correct results whenever a wbk has been saved with a UsedRange flaw. The only method which does work is the 2 recordset I mentioned earlier but that is very slow. Unfortunately I am not able to supply the 2 wbks with known UsedRange flaws because of Data Protection. If they did not contain details of names, jobs, addresses and telephone numbers you would be very welcome to have a look. And of course I cannot delete the data as that would reset the UsedRange. If you can think of a way to create a wbk with an incorrect UsedRange and employ any of the above methods then you would make the same observations, I am certain. Unfortunately I have no control over theses wbks which are supplied from outside sources. The standard of presentation is appalling - hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of line characters - some even without any field names - and of course some with a flawed UsedRange. Geoff "RB Smissaert" wrote: It works fine with me. Could you mail me that workbook that gives you the wrong answer? RBS "Geoff K" wrote in message ... Hi I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
The current project, which uses open wbks, solves all those issues
satisfactorily - even dealing with flawed UsedRanges. What I am trying to do is extend the current use of ADO and also work from closed wbks. It saves a great deal of time. And the beauty of using ADO (from a closed wbk) is that I can ignore having to undo hidden rows, autofilters etc etc. In one experiment I hid all data rows and columns, ran the process and it still produced the same final results as if the wbk had been open BUT as I said a great deal quicker. On average using ADO on a folder of closed wbks reduces processing time by half. It is a prize worth pursuing as this application is only part of a wider process. Can you produce a flawed UsedRange wbk? I can't. My only understanding of the phenomenon is they can be caused by "a frequent change of data area, cutting and pasting" but who knows? Geoff "RB Smissaert" wrote: hidden rows, hidden columns, autofilters, merged cells, wordwraps, end of line characters - some even without any field names OK, I hadn't tested for all that. Did you try the latest ADO code I posted? Can't you produce a demo wb that has (all of) the above problems and make it fail with ADO code? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
I agree, SELECT COUNT(*), RecordCount, GetRows all work as expected when the
UsedRange matches the real data. But all fail with a flawed UsedRange. Excel4Macros failed to run on the largest of the 2 wbks but not on the smallest. Unfortunately I have no quality control over these received wbks. Sometimes the quality is appalling, hidden rows, hidden columns, end of line characters, merged cells, cell errors, autofilters, some even without field names and of course some with flawed UsedRanges. I would be willing to supply the 2 wbks with known flaws were it not for data protection. They contain names, job titles, telephone numbers etc and it would be wrong of me to share those details. And of course if I deleted or overwrote the data the ensuing save would reset the UsedRange. But if you know of a way to create a UsedRange which is out of line then I am certain you would make the same observations. Geoff "RB Smissaert" wrote: It works fine with me. Could you mail me that workbook that gives you the wrong answer? RBS "Geoff K" wrote in message ... Hi I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from a closed wbook
I don't know whether this one would work or not on your data. But it
seems to be able to detect a flawed UsedRange in my case, ignoring the time of process. According data, it might be very slow. I assumed a flawed UsedRange to be data file that returns a wrong number when using Select count(*) in ADO. Sub CheckFlawedtest() Dim SsourceData As String Dim Table1 As String SsourceData = "c:\adodata.xls" Table1 = "[Sheet1$]" If CkFlawedURange(SsourceData, Table1) Then MsgBox "Flawed UsedRange" MsgBox "Corect LastRow Is " & _ GetLastRow(SsourceData, Table1) Else MsgBox "Not Flawed" End If End Sub Function CkFlawedURange(ByVal Fname As String, _ ByVal TableName As String) As Boolean 'Fname is a name of a file with a full path 'TableName is a name of Worksheet Dim oConn As ADODB.Connection Dim i As Long Set oConn = New ADODB.Connection oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Fname & ";" & _ "Extended Properties=""Excel 8.0;HDR=YES;""" Dim oRS As ADODB.Recordset Set oRS = New ADODB.Recordset oRS.CursorLocation = adUseClient oRS.Open TableName, oConn, adOpenStatic oRS.MoveLast CkFlawedURange = True For i = 0 To oRS.Fields.Count - 1 If Not IsNull(oRS.Fields(i).Value) Then CkFlawedURange = False Exit For End If Next oRS.Close oConn.Close Set oConn = Nothing Set oRS = Nothing End Function Function GetLastRow(ByVal Fname As String, _ ByVal TableName As String) As Long 'Fname is a name of a file with a full path 'TableName is a name of Worksheet Dim Flawed As Boolean Dim oConn As ADODB.Connection Dim i As Long Set oConn = New ADODB.Connection oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Fname & ";" & _ "Extended Properties=""Excel 8.0;HDR=YES;""" Dim oRS As ADODB.Recordset Set oRS = New ADODB.Recordset oRS.CursorLocation = adUseClient oRS.Open TableName, oConn, adOpenStatic oRS.MoveLast Flawed = True Do While (Flawed) For i = 0 To oRS.Fields.Count - 1 If Not IsNull(oRS.Fields(i).Value) Then Flawed = False Exit Do End If Next oRS.MovePrevious Loop GetLastRow = oRS.AbsolutePosition + 1 oRS.Close oConn.Close Set oConn = Nothing Set oRS = Nothing End Function Keiji Geoff K wrote: Hi I was just about to post the same thing when I spotted your reply. It was easy enough to transpose and add 1 for the zero base. However the ADO function returns me once more to the start position of mislaigned UsedRanges. On the bloated wbk it returned the last row as 50918 and not the real 98. I have been here before. MichDenis in another post some way back now supplied a link http://cjoint.com/?jDndv2hXXE which uses 2 recordsets. This does avoid the pitfalls of flawed UsedRanges but is slow. This is frustrating because the incidence of flawed UsedRanges is only about 2 wbks in 500. But because of the risk, I have to use the slow method on every wbook. It would be great if I could detect a flawed UsedRange and run the 2 recordset method on that wbk only. On the rest of the wbks I could use SELECT COUNT(*) etc. FWIW I don't believe SELECT COUNT(*) does any counting at all because it is so blisteringly quick. I think instead it probably uses the UsedRange last row or something like it. Unfortunately a null is a record to SQL so if the wbk has been saved with a flawed UsedRange that is what it uses. So I am right back to square 1. If only I could detect a flawed UsedRange in a closed wbk€¦€¦€¦ Geoff "RB Smissaert" wrote: That code wasn't tested and indeed it is no good at all, mainly because I didn't consider the fact that an array produced by rs.GetArray is transposed. Shortly after I posted better code (via a phone), but it didn't come through. Try this code instead: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get values from 15 sheets in a Wbook | Excel Programming | |||
updating wbook from other... | Excel Programming | |||
transfering data from 2 wbook | Excel Programming | |||
transfering data value from 2 Wbook... | Excel Programming | |||
Read And Write On A Closed Wbook | Excel Programming |