Write Values from ADO Connection

[brkamikasecps]

Hi guys!

first, sorry about my english =D

i have a ADO Connection. My query is like this:

select [C1] from [values$]

It works, but my problem is to write this values.
Why?
Cause i work with my workbook open (not that from connection) and i have something like 55,000 rows to change value. A complex range:

D2,D11:D12,D14:D20,D32,D42,D52,D62,D72,D82,D92,D101:D102,D104:D112...

I have a loop for each cell in this range, but this process is very slow.

Imagine:

For Each rng In rng
rng.Value = objRecordset(0)
objRecordset.MoveNext
Next rng

55,000? So slow...

I saw something about QueryTable, but i didnt understand if can help me...

Any suggestions are greatly appreciated.

Regars from Brazil! =)
Allan Carvalho

 

[Richard Schollar]

Hi Allan

Why don't you return all the values in the recordset to a worksheet first? You can do that with the CopyFromRecordset method of the range object:

Sheets("SomeSheet").Range("A1").CopyFromRecordset objRecordset

That should be swift and then you can do your processing from there.

 

[ZVI]

You can also copy data from recordset object to array instead of to the temporary sheet, using MyArray = objRecordset.GetRows

<font face=Courier New>
  '...
  ' Check to make sure that data are received
  If Not objRecordset.EOF Then
    Dim MyArray, v
    MyArray = objRecordset.GetRows
    With Application
      ' Turn Excel brakes off
      .ScreenUpdating = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
      ' Here should be the code to copy data from MyArray to destination range
      For Each v In MyArray
        'Debug.Print v
        '...
      Next
      ' Restore Excel brakes
      .ScreenUpdating = True
      .EnableEvents = True
      .Calculation = xlCalculationAutomatic
    End With
  Else
    MsgBox "No records returned from : " & SourceFile, vbCritical
  End If</FONT>

Vladimir

 

[brkamikasecps]

Richard,
Perfect, but i should copy each cell to my range (D2,D11:D12,D14:D20,D32,D42...D55000)
This process is slow. Unfortunately, my range destination has hidden cells. Thats why i recorded visible rows on a Range (Dim rng as Range).
CopyFromRecordset is fastest (almost real time). Like It!
Could i copy to each row on my range using CopyFromRecordset? Dont know if it's possible...


ZVI,
Your suggest is great, but i have to write each cell... So slow...


Thanks, guys!

 

[ZVI]

Richard,
Perfect, but i should copy each cell to my range (D2,D11:D12,D14:D20,D32,D42...D55000)
This process is slow. Unfortunately, my range destination has hidden cells. Thats why i recorded visible rows on a Range (Dim rng as Range).
CopyFromRecordset is fastest (almost real time). Like It!
Could i copy to each row on my range using CopyFromRecordset? Dont know if it's possible...

ZVI,
Your suggest is great, but i have to write each cell... So slow...

Thanks, guys!

For regret, you can’t avoid the slow process of copying to noncontiguous destination range. So the only freezing of screen updating & events as well as using the manual calculation can help to this process.
If you've tried it what is the spending time?

Another possible way is in using of already set formulas in each cells of destination range, which are referenced to the temporary sheet (see Richard’s solution). But seems that it's not the flexible way for you.

By the way, how you managed to set so big destination range (55,000 rows) of non-adjacent cells? As there is 255 chars limit for the range assigned by the string addresses.

How many areas in that range?
If it isn't a lot of them then it is possible to speed up the copying process.

 

[s-h]

Could i copy to each row on my range using CopyFromRecordset? Dont know if it's possible...

You could do something like this, maybe also checking for objRecordset.EOF if you run out of records before the rows in the range run out.

Sub Answer()
    Dim rngMultiple As Range
    Dim rngArea As Range
    
    Set rngMultiple = Range("A2,A11:A12,A14:A20,A32,A42,A52,A62,A72,A82,A92,A101:A102,A104:A112")
    
    For Each rngArea In rngMultiple.Areas
        Debug.Print rngArea.Address, rngArea.Rows.Count
        rngArea.CopyFromRecordset objRecordset, rngArea.Rows.Count
    Next rngArea
    
End Sub

 

[brkamikasecps]

s-h, perfect!!!

Complex Area = fast. (~135 seconds)
Simple Area = faster it is. (~40 seconds)
Unique Area = fastest! (~3 seconds)

Thank you so much!

 

[brkamikasecps]

A little and simple question:

Using this code:

Sub Answer()
    Dim rngMultiple As Range
    Dim rngArea As Range
    
    Set rngMultiple = Range("A2,A11:A12,A14:A20,A32,A42,A52,A62,A72,A82,A92,A101:A102,A104:A112")
    
    For Each rngArea In rngMultiple.Areas
        Debug.Print rngArea.Address, rngArea.Rows.Count
        rngArea.CopyFromRecordset objRecordset, rngArea.Rows.Count
    Next rngArea
    
End Sub

How can i replace null values from recordset for a text (like '#N/D')?

Maybe on my query....

select nvl([Col1],'#N/D')...

but it doesnt work

Suggestions?

thanks again!

 

[Richard Schollar]

It depend what Provider you are using for making your ADO connection. If you are targeting SQL server then you can use this:

SELECT IsNull([Field1],'#N/D'), [Field2], ...

Or if Access (or if using the Access driver to target an Excel workbook) then:

SELECT Iif([Field1] Is Null,'#N/D',[Field1]), [Field2],...

 

[brkamikasecps]

Perfect! Thanks!