BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Export all DataRecordsets to Excel

Started by Thomas Winkel, February 14, 2017, 04:19:03 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Thomas Winkel


if you have external data in your document, the following code exports each DataRecordset to Excel.


Sub exportDataRecordSets()
    Dim drs As Visio.DataRecordset
    Dim dataRowIDs() As Long
    Dim xlsApp As Excel.Application
    Dim xlsWB As Excel.Workbook
    Dim xlsWS As Excel.Worksheet
    Dim arr() As String
    Dim txt As String
    Dim i As Integer
    If ActiveDocument.DataRecordsets.Count = 0 Then Exit Sub
    Set xlsApp = New Excel.Application
    xlsApp.Visible = False
    Set xlsWB = xlsApp.Workbooks.Add
    For Each drs In ActiveDocument.DataRecordsets
        Set xlsWS = xlsWB.Worksheets.Add(After:=xlsWB.Worksheets(xlsWB.Worksheets.Count)) =
        txt = ""
        For i = 1 To drs.DataColumns.Count
            txt = txt & drs.DataColumns.item(i).name & ";"
        Next i
        txt = Left(txt, Len(txt) - 1)
        arr = Split(txt, ";")
        xlsWS.Range(xlsWS.Cells(1, 1), xlsWS.Cells(1, UBound(arr) + 1)).Value = arr
        dataRowIDs = drs.GetDataRowIDs("")
        For i = LBound(dataRowIDs) To UBound(dataRowIDs)
            xlsWS.Range(xlsWS.Cells(i + 2, 1), xlsWS.Cells(i + 2, UBound(arr) + 1)).Value = drs.GetRowData(dataRowIDs(i))
        Next i
        xlsWS.ListObjects.Add(xlSrcRange, xlsWS.Range(xlsWS.Cells(1, 1), xlsWS.Cells(i + 1, UBound(arr) + 1)), , xlYes).name =
    Next drs
    xlsApp.Visible = True
End Sub

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 130 (show)
Files included: 32 - 1207KB. (show)
Memory used: 1002KB.
Tokens: post-login.
Cache hits: 14: 0.00126s for 26,766 bytes (show)
Cache misses: 3: (show)
Queries used: 16.

[Show Queries]