Automating creation of salary structure floating bar chart using excel vba

 Option Explicit

Function Difference(r1 As Range, r2 As Range) As Range

Dim s As String

Dim ws As Worksheet

 

If Not (r1.Parent Is r2.Parent) Then Exit Function

On Error Resume Next

 

Set ws = Worksheets.Add

ws.Range(r1.Address) = 0

ws.Range(r2.Address).Clear

s = ws.Range(r1.Address).SpecialCells(xlCellTypeConstants).Address

Application.DisplayAlerts = False

ws.Delete

Application.DisplayAlerts = True

If s <> "" Then Set Difference = r1.Parent.Range(s)

End Function

Sub floating_bar_chart()

'so first we have to delete the sheet named HR_Chart

With Application

        .ScreenUpdating = False

        .DisplayAlerts = False

        .EnableEvents = False

    End With

   Dim achart As Chart

   

 

 For Each achart In ActiveWorkbook.Charts

    achart.Delete

  Next

   

'    With ThisWorkbook

'        Set wks = .Sheets.Add(After:=.Sheets(.Sheets.Count))

'        wks.Name = "HR_Chart"

'    End With

   

    With Application

        .ScreenUpdating = True

        .DisplayAlerts = True

        .EnableEvents = True

    End With

'first delete all charts in this sheet

Dim Chrt As ChartObject

For Each Chrt In ActiveSheet.ChartObjects

Chrt.Delete

Next

'Declare some variables

Sheets("Sheet1").Select

Dim DataRng As Range

'Dim series1name As String

'Dim series2name As String

'Dim series3name As String

'series1name = ActiveSheet.Range("F3").Value

'series2name = ActiveSheet.Range("F4").Value

'series3.Name = ActiveSheet.Range("F5").Value

Dim seriesnames()

Dim series()

ReDim Preserve seriesnames(0)

ReDim Preserve series(0)

ActiveSheet.Range("F3").Select

While ActiveCell.Value <> ""

ReDim Preserve seriesnames(UBound(seriesnames) + 1)

ReDim Preserve series(UBound(series) + 1)

Set seriesnames(UBound(seriesnames) - 1) = ActiveCell

Set series(UBound(series) - 1) = Difference(Range(Selection, Selection.End(xlToRight)), Selection)

ActiveCell.Offset(1, 0).Select

Wend

Dim counter As Integer

'For counter = 0 To (UBound(series) - 1)

'MsgBox (series(counter))

'

'Next counter

'Add a chart object, this would be an empty shell

Set Chrt = ActiveSheet.ChartObjects.Add(Left:=200, _

                                        Width:=800, _

                                        Height:=800, _

                                        Top:=50)

                                        

                                        

'set the chart name

Dim chartname As String

   

       

  

chartname = "hrchart"

Chrt.Name = chartname

'Add the series of data in your chart

 For counter = 0 To (UBound(series) - 1)

Chrt.Chart.SeriesCollection.NewSeries

 

Chrt.Chart.SeriesCollection(counter + 1).Name = "='" & ActiveSheet.Name & "'!" & seriesnames(counter).Address(, , xlR1C1)

    

    

    

Chrt.Chart.FullSeriesCollection(counter + 1).Values = "='" & ActiveSheet.Name & "'!" & series(counter).Address(, , xlR1C1)

    

Next counter

    Sheets("Sheet1").Select

    'Set Chrt = ActiveSheet.ChartObjects("hrchart")

    'moving the chart to a sheet named "HR_Chart"

    

    'Chrt.Chart.Location Where:=xlLocationAsNewSheet, Name:="HR_Chart"

    

    'move chart to new sheet at the end

   

    

  

   

  Chrt.Chart.Location Where:=xlLocationAsNewSheet, Name:="HR_Chart"

 

ActiveChart.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

'Chrt.Chart.Location xlLocationAsObject, "HR_Chart"

ActiveChart.ChartArea.Select

    ActiveChart.FullSeriesCollection(1).Select

    ActiveChart.ChartGroups(1).Overlap = 100

   

    ActiveChart.ChartGroups(1).GapWidth = 66

    

    ActiveChart.ChartArea.Select

    ActiveChart.FullSeriesCollection(3).Select

  

    With Selection.Format.Fill

        .Visible = msoTrue

        .ForeColor.ObjectThemeColor = msoThemeColorBackground1

        .ForeColor.TintAndShade = 0

        .ForeColor.Brightness = 0

        .Transparency = 0

        .Solid

    End With

    

     ActiveChart.Axes(xlValue).MajorGridlines.Select

    Selection.Delete

  

    'ActiveSheet.ChartObjects("hrchart").Activate

    ActiveChart.FullSeriesCollection(1).Select

    ActiveChart.FullSeriesCollection(1).ApplyDataLabels

    ActiveChart.FullSeriesCollection(2).Select

    ActiveChart.FullSeriesCollection(2).ApplyDataLabels

    ActiveChart.FullSeriesCollection(3).Select

    ActiveChart.FullSeriesCollection(3).ApplyDataLabels

    

    ActiveChart.FullSeriesCollection(2).Select

    With Selection.Format.Fill

        .Visible = msoTrue

        .ForeColor.RGB = RGB(204, 229, 255)

        .Solid

    End With

'    ActiveChart.FullSeriesCollection(2).DataLabels.Select

'

'    With Selection.Format.TextFrame2.TextRange.Font.Fill

'        .Visible = msoTrue

'        .ForeColor.ObjectThemeColor = msoThemeColorBackground1

'        .ForeColor.TintAndShade = 0

'        .ForeColor.Brightness = 0

'        .Transparency = 0

'        .Solid

'    End With

    ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.Visible = msoTrue

     ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground1

      ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.TintAndShade = 0

       ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.Brightness = 0

        ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.Transparency = 0

         ActiveChart.FullSeriesCollection(2).DataLabels.Format.TextFrame2.TextRange.Font.Fill.Solid

         

         

         ActiveChart.Deselect

SendKeys "{ESC}"

DoEvents

         

End Sub

Plotting Time series using Python,Matplotlib and Seaborn

 # -*- coding: utf-8 -*-
"""Average_Plot_Assignment.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/1je2kMjqPUPUzawk2IernYMR5lkwZgny1
"""

!pip install odfpy

import numpy as np
import datetime
from google.colab import files
import pandas as pd
df=pd.read_excel("/content/sample_data/arable__NIST_A_B_C_C003747_daily_20200818.ods", engine="odf")
#df.drop([df.index[0]],inplace=True)
 
#df = df.astype({"NIST A": np.float64, "NIST B": np.float64,"NIST C":np.float64},errors='coerce')
cols = ['NIST A', 'NIST B', 'NIST C']
df[cols] = df[cols].apply(pd.to_numeric,errors='coerce',axis=1)

df.fillna(0, inplace=True)

df[['Timestamp']] = df[['Timestamp']].apply(pd.to_datetime,errors='coerce',axis=1)
df.sort_values(by=['Timestamp'], inplace=True, ascending=True)
df['mean'] = df.mean(axis=1)
df= df.set_index('Timestamp')
df

import matplotlib.pyplot as plt
import matplotlib.dates as mdates
# Display figures inline in Jupyter notebook
import seaborn as sns
# Use seaborn style defaults and set the default figure size
sns.set(rc={'figure.figsize':(15, 4)})

fig, ax = plt.subplots()
ax.plot(df['mean'], marker='o', linestyle='-')
ax.set_ylabel('Mean')
ax.set_xlabel('Timestamp')
ax.set_title('Mean Vs Timestamp')
# Set x-axis major ticks to weekly interval, on Mondays
ax.xaxis.set_major_locator(mdates.WeekdayLocator(byweekday=mdates.MONDAY))
# Format x-tick labels as 3-letter month name and day number
ax.xaxis.set_major_formatter(mdates.DateFormatter('%b %d'));

# Specify the data columns we want to include (i.e. exclude Year, Month, Weekday Name)
data_columns = ['mean']
# Resample to weekly frequency, aggregating with mean
df_weekly_mean = df[data_columns].resample('W').mean()

import matplotlib.pyplot as plt
import matplotlib.dates as mdates
# Display figures inline in Jupyter notebook
import seaborn as sns
# Use seaborn style defaults and set the default figure size
sns.set(rc={'figure.figsize':(15, 4)})

# Start and end of the date range to extract
start, end = df.index.min(), df.index.max()
# Plot daily and weekly resampled time series together
fig, ax = plt.subplots()
ax.plot(df.loc[start:end, 'mean'],marker='.', linestyle='-', linewidth=1.0, label='Daily Mean')
ax.plot(df_weekly_mean.loc[start:end, 'mean'],marker='o', markersize=8, linestyle='-', label='Weekly Mean Resample')
ax.set_ylabel('Mean')
ax.legend();
fig.savefig("Fid.jpg",dpi=500,format='jpg', bbox_inches='tight')
files.download("Fid.jpg")

 

 

Convert word document to pdf using VBA

Option Explicit

Sub vba_word_to_pdf()

Dim output_file As String

output_file = ActivePresentation.Path & "\" & Left(ActivePresentation.Name, Len(ActivePresentation.Name) - 5) & ".pdf"

'MsgBox ("hello")

ActivePresentation.ExportAsFixedFormat output_file, ppFixedFormatTypePDF, ppFixedFormatIntentPrint

End Sub

Convert all slides in a powerpoint presentation to a pdf using VBA

Option Explicit

Sub vba_powerpoint_to_pdf()

Dim output_file As String

output_file = ActivePresentation.Path & "\" & Left(ActivePresentation.Name, Len(ActivePresentation.Name) - 5) & ".pdf"

'MsgBox ("hello")

ActivePresentation.ExportAsFixedFormat output_file, ppFixedFormatTypePDF, ppFixedFormatIntentPrint

End Sub

Read Pdf without Acrobat

Enable Microsoft Scripting Runtime reference

Option Explicit

Const form_filename As String = "C:\Users\allso\Desktop\New Customer Registration Form.pdf"

Sub read_pdf_form_vals()

Dim fso As New FileSystemObject

Dim tStream As TextStream

Dim vLine As String, vKey As String, fieldlist() As Variant, arrIndx As Integer

Dim i As Integer

vKey = ") Tj"

Set tStream = fso.OpenTextFile(form_filename, ForReading, False)

Do While Not tStream.AtEndOfStream

vLine = tStream.ReadLine

If InStr(vLine, vKey) > 0 Then

vLine = Replace(Right(vLine, Len(vLine) - 1), vKey, "", 1)

ReDim Preserve fieldlist(0 To arrIndx)

fieldlist(arrIndx) = vLine

Debug.Print vLine

arrIndx = arrIndx + 1

End If

'Debug.Print vLine

Loop

For i = UBound(fieldlist) To LBound(fieldlist) Step -1

Debug.Print fieldlist(i)

Next i

Set tStream = Nothing

Set fso = Nothing

End Sub