We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Sub Balance_Analysis() Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = Sheets("BalanceAnalysis").Cells(1, 1) 'Dim source As String Dim user As String Dim password As String Dim Company As String Dim rg As Range Source = Login.TextBox1.Value user = Login.TextBox2.Value password = Login.TextBox3.Value Company = Login.TextBox4.Value 'Toggle the worksheets Application.DisplayAlerts = True Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Sheets("BalanceAnalysis").Visible = True Sheets("BalanceAnalysis").Select Cells.Select Selection.ClearContents ' open the database Set cn = New ADODB.Connection cn.Open "Provider=OraOLEDB.Oracle;" & _ "Data Source=" & Source & ";" & _ "User Id=" & user & ";" & _ "Password=" & password & "" Set rs = New ADODB.Recordset With rs sSql = "select distinct company, accounting_year, account, account_desc, " & _ "sum(amount_balance) over ( PARTITION BY account order by account range unbounded preceding) as cumulative " & _ "from accounting_balance_auth " & _ "where company = 'M02' " & _ "and accounting_year = '2010' " & _ "and accounting_period <= '2' " & _ "order by 3" rs.Open sSql, cn, adOpenStatic, adLockReadOnly, adCmdText For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data End With rs.Close Set rs = Nothing cn.Close Set cn = Nothing 'Toggle the worksheets ActiveWindow.SelectedSheets.Visible = False Sheets("p&l").Select Range("A3").Select 'Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = False Application.ScreenUpdating = True End Sub
Join the community of 500,000 technology professionals and ask your questions.