MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Microsoft Excel - Select All Computers and Specific Applicaiton||2||26|
|Create email hyperlinks from regular text||3||29|
|Email Excel WB by selecting name from a dropdown list to Outlook.||5||47|
|Excel Macro: Delete All Columns where Data is same (need to also add condition - IF columns are empty)||5||22|
Join the community of 500,000 technology professionals and ask your questions.