Danny Yun

GL Account is blocked for posting

T-Code: FS00

Once the GL account is selected, click Block option (padlock icon in this case), then uncheck ‘Blocked for Posting’ and Save it. If you don’t see hierarchy tree, you can display it. Settings > hierarchy display

Without the hierarchy tree, ‘Block’ option should be found under ‘G/L Account’ menu on the top.

Python – MS SQL Access

You can directly link to SQL Server to get a query table using following code.

Using a query file (*.sql)

import pyodbc
import pandas as pd

server = "your server name" 
database = "your database name"

conn = pyodbc.connect(DRIVER='{SQL Server}', host = server, DATABASE = database, Trusted_connection='yes')

sql = open("./your query file.sql", "r")

df = pd.read_sql_query(sql.read(), conn)

print(df)

2. Using a query inside python codes

import pyodbc
import pandas as pd
   
conn_str = (
        r'Driver=SQL Server;'
        r'Server=your server name;'
        r'Database=your database name;'
        r'Trusted_Connection=yes;'
        )
cnxn = pyodbc.connect(conn_str)

query = """

    # Enter your query here like below

    # SELECT *
    # FROM your table name


    """

df = pd.read_sql(query, cnxn)

print(df)

Excel VBA – VBA performance Optimization

Optimizing VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Add ‘OptimizeCode_Begin’ to the first line inside sub, and add ‘OptimizeCode_End’ to the last line inside sub.


Sub OptimizeCode_Begin()
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

End Sub

Sub OptimizeCode_End()
    
    ActiveSheet.DisplayPageBreaks = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

Example of usage

Sub GroupBy()

    OptimizeCode_Begin

    'Enter your code between optimize code begin/end

    OptimizeCode_End

End Sub

Excel VBA – Pivot table/chart 5 – Chart Data label control

Turn on/off chart data label using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Turn on/off chart data label

Sub DataLabel()

    Dim cbValue As Object
    Set cbValue = ActiveSheet.CheckBoxes("Check Box 1")
    
    Dim chtObj As ChartObject
    Dim sr As Series
    
    With cbValue
        If .Value = 1 Then
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = True
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
            
        Else
            For Each chtObj In ActiveSheet.ChartObjects
                For Each sr In chtObj.Chart.SeriesCollection
                    sr.ApplyDataLabels
                        With sr.DataLabels
          '             .ShowSeriesName = True
                        .ShowValue = False
           '            .Position = xlLabelPositionInsideBase
          '             .Orientation = -90
          '             .Font.Size = 8
                        End With
                Next sr
            Next chtObj
        
        End If
    End With
    
End Sub

Excel VBA – Pivot table/chart 4 – Chart type control

Changing Chart Type using VBA code

Sample file download – https://github.com/dannyyun/pivot-vba.git

Changing Chart type (line chart, stacked column, 100% stacked column)

Sub ChtType()

    With ActiveSheet.Shapes("Drop Down 4").ControlFormat

        Select Case .ListIndex
            Case Is = 1
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlLine
                ActiveSheet.Range("A1").Select
                
            Case Is = 2
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlColumnStacked
                ActiveSheet.Range("A1").Select
                
            Case Is = 3
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.ChartType = xlColumnStacked100
                ActiveSheet.Range("A1").Select
                
        End Select
    End With

End Sub