Write Values from ADO Connection

brkamikasecps

New Member
Joined
Jul 12, 2009
Messages
24
Hi guys!

first, sorry about my english =D

i have a ADO Connection. My query is like this:
Code:
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:
Code:
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:
Code:
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... :confused:

Any suggestions are greatly appreciated. ;)

Regars from Brazil! =)
Allan Carvalho
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

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

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

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,791
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
You can also copy data from recordset object to array instead of to the temporary sheet, using MyArray = objRecordset.GetRows

Rich (BB code):
<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

New Member
Joined
Jul 12, 2009
Messages
24
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,791
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 6, 2009
Messages
10
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.

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
 

brkamikasecps

New Member
Joined
Jul 12, 2009
Messages
24

ADVERTISEMENT

(y)
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

New Member
Joined
Jul 12, 2009
Messages
24
A little and simple question:

Using this code:
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....
Code:
select nvl([Col1],'#N/D')...
but it doesnt work :(

Suggestions? ;)

thanks again!
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
It depend what Provider you are using for making your ADO connection. If you are targeting SQL server then you can use this:

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,118,575
Messages
5,573,017
Members
412,498
Latest member
Eddy08
Top