Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.
Private Sub ExportData(Factory As clsAccess) Dim rsEx As ADODB.Recordset ' query data from consolidate table Factory.strQueryText = "SELECT SS_Consolidate.[Security Code Primary], SS_Consolidate.Sedol, SS_Consolidate.Isin, " & _ "SS_Consolidate.[Alternate Security Code], SS_Consolidate.[SECURITY NAME SHORT], SS_Consolidate.[Currency Code Local], " & _ "SS_Consolidate.[SECURITY TYPE CODE], SS_Consolidate.[COUNTRY RISK], SS_Consolidate.[Transaction Number External], SS_Consolidate.[Process Date], " & _ "SS_Consolidate.[Trade Date], SS_Consolidate.[Settlement Date], SS_Consolidate.[Transaction Category Code], " & _ "SS_Consolidate.[Currency Code Settlement], SS_Consolidate.[Fx Rate Local To Firm], SS_Consolidate.Units, " & _ "SS_Consolidate.[Price Trade Local], SS_Consolidate.[Trading Units], SS_Consolidate.[Cost Local], " & _ "SS_Consolidate.[Proceeds Local], SS_Consolidate.[Interest Purchased Sold Local], SS_Consolidate.[Commission Local], " & _ "SS_Consolidate.[Fee Total Capitalized User Defined Local], SS_Consolidate.[Portfolio Code], " & _ "SS_Consolidate.[Client Executing Broker], SS_Consolidate.[Principal Or Agent Name], SS_Consolidate.[Fx Rate Local To Portfolio], " & _ "SS_Consolidate.[Currency Code Portfolio], SS_Consolidate.[Block Id External], SS_Consolidate.Type " & _ "FROM SS_Consolidate " & _ "ORDER BY SS_Consolidate.[Security Code Primary];" Set rsEx = Factory.SQLExecuteQuerySelect If rsEx.EOF = False Then ' export data to excel Dim wb As Workbook Dim ws As Worksheet Set wb = Application.ActiveWorkbook Set ws = wb.ActiveSheet ' populate header Dim i As Integer For i = 0 To rsEx.Fields.Count - 1 ws.Cells(1, i + 1) = rsEx.Fields(i).Name Next ws.Range("A5").CopyFromRecordset rsEx ws.Rows("1:1").Font.Bold = True ws.Cells.EntireColumn.AutoFit Else MsgBox "No data found in Consolidate table, please check the contents of the excels files, Transactions & Security_Reference", vbInformation, "No Data" End If Set rsEx = Nothing End Sub
Public Function SQLExecuteQuerySelect() As ADODB.Recordset ' Any SQL select query is run from here On Error Resume Next ' cannot open recordset if already open prs.Close If Err <> 0 Then On Error GoTo 0 prs.Open strQueryText, pcn, adOpenKeyset, adLockOptimistic Set SQLExecuteQuerySelect = Clone(prs) prs.Close End Function Private Function Clone(ByVal oRs As ADODB.Recordset) As ADODB.Recordset ' Function makes a clone of the private recordset. The ADO Recordset.Clone method doesn't actually clone the recordset. It doesn't create a new object _ in memory it just returns a reference to the private recordset. This function make a real clone without any dependencies. _ Without this function user cannot have more than one open recordset Dim rsClone As ADODB.Recordset 'save the recordset to the stream object Set pStream = New ADODB.Stream prs.Save pStream 'and now open the stream object into a new recordset Set rsClone = New ADODB.Recordset rsClone.Open pStream 'return the cloned recordset Set Clone = rsClone 'release the reference Set rsClone = Nothing End Function
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.