Need better ways to copy all data incl. fieldname from recordset to excel.

[bPrice]

I am using the following code right now to copy the data to an excel sheet:

rs.Open "SELECT * FROM " & sDBTableName, cn, adOpenKeyset, adLockOptimistic, adCmdText
sDataArray = rs.GetRows   
 
For Each f In rs.Fields
        i = i + 1
        oRange.cells(1, i).Value = f.Name
Next

For i = 0 To UBound(sDataArray, 2)    'iterates through all records
        For k = 0 To UBound(sDataArray, 1)    'iterates through each field
            oRange.cells(i + 2, k + 1).Value = sDataArray(k, i)
        Next
Next

I guess there might be better ( as in shorter, faster and more reliable) ways to do this?

[Schmidt]

Take a Look at the ArrFillFromTopLeft Helperfunctions here:
http://www.vbforums.com/showthread.p...=1#post4615193

Olaf

[bPrice]

Thank you Olaf, I will dig into that thread soon. Seems to be a long one to read ...

BTW. Today I'm really having troubles finding the equivalent to "Open File For Append" in vbRichclient ... So far, I can only see related methods:

WriteTextContent - Tested and it seems to rewrite the entire file.

Another problem related with cFSO is that, I can't find any equivalent to "fso.CopyFolder". There is only *copy file* functions in cFSO. And yes, I know I can use cDirList to get a list of filenames and then copy them wherever I like but please forgive me, I want some *shorter* approaches from vbRichClient...

[Zvoni]

Eh? Why not just use the "CopyFromRecordset"-Method?

https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

[Schmidt]

Eh? Why not just use the "CopyFromRecordset"-Method?

https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx

Yep, worth a try - though this method behaved a bit "funny" (on older Excel- and/or ADO-versions)...

Rs.GetRows allows a bit more influence (since you can go over certain columns again pretty fast -
with regards to formatting and stuff - and it is also slightly faster IIRC than CopyFromRecordset.

Olaf

[Schmidt]

Thank you Olaf, I will dig into that thread soon. Seems to be a long one to read ...

The Helperfunctions I mean, are in my post #13.

BTW. Today I'm really having troubles finding the equivalent to "Open File For Append" in vbRichclient ...

Well, there's the Stream-Object you can use for that (e.g. when used in conjunction with
a StringBuilder as intermediate InMemory-Buffer):

Private Sub AppendToFile(SB As cStringBuilder, FileName As String)
  With New_c.FSO.OpenFileStream(FileName, STRM_WRITE Or STRM_SHARE_DENY_NONE)
    .SetPosition 0, STRM_SeekFromEnd '<- moves the StreamPointer to the end 
    .WriteFromByteArr SB.ToUTF8 '<- appends the current content of the StringBuilder
  End With
End Sub

Here's the whole concept in context with some Parent-Child-XML-writing:

Option Explicit

Private Sub Form_Load()
  Dim SB As cStringBuilder
  Set SB = New_c.StringBuilder
  
  'gather stuff first in an intermediate InMemory-Buffer
  SerializeParent SB
  
  'from time to time, append the SB.Content to a File
  AppendToFile SB, "c:\temp\test.txt"
  
  SB.Clear 'clear the StringBuilder-Buffer (since the Data is now safe in a File)
  
  '... pass the StringBuilder around for more data-gathering
End Sub

Private Sub SerializeParent(SB As cStringBuilder)
Dim Children As cArrayList 'just some DemoData, the simulated Parent knows about
Set Children = New_c.ArrayList(vbString, "Child1", "Child2", "Child3")
  
  SB.Append "<Parent"
    SB.AppendXMLAttribute "ChildCount", Children.Count: SB.AppendNL ">"
      SerializeChildren SB, Children
  SB.AppendNL "</Parent>"
End Sub

Private Sub SerializeChildren(SB As cStringBuilder, Children As cArrayList)
  Dim i As Long
  For i = 0 To Children.Count - 1
    SB.Append "  <Child>"
      SB.AppendXMLNodeContent Children(i)
    SB.AppendNL "</Child>"
  Next i
End Sub

Private Sub AppendToFile(SB As cStringBuilder, FileName As String)
  With New_c.FSO.OpenFileStream(FileName, STRM_WRITE Or STRM_SHARE_DENY_NONE)
    .SetPosition 0, STRM_SeekFromEnd '<- moves the StreamPointer to the end 
    .WriteFromByteArr SB.ToUTF8 '<- appends the current content of the StringBuilder
  End With
End Sub

Another problem related with cFSO is that, I can't find any equivalent to "fso.CopyFolder". There is only *copy file* functions in cFSO. And yes, I know I can use cDirList to get a list of filenames and then copy them wherever I like but please forgive me, I want some *shorter* approaches from vbRichClient...

The cFSO.MultiFilexxx functions can do that job ... e.g. to copy all *.csv files from c:\temp1\ into c:\temp2\ you do:

  New_c.FSO.MultiFileCopy "c:\temp1\*.csv", "c:\temp2\"

Olaf