Select Range out of Filtered Data

Option Explicit
Dim rowcount As Long
Dim mycoll As Collection, myrng As Range, cell, i As Integer, logintime, logouttime
Sub createAttendance()
On Error Resume Next
Set mycoll = New Collection
Application.ScreenUpdating = False
    rowcount = ThisWorkbook.Sheets(1).Range("C7").End(xlDown).Row
    Set myrng = ThisWorkbook.Sheets(1).Range("C7:C" & rowcount)
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)
    Next
    i = 0
    For i = 1 To mycoll.Count

        ThisWorkbook.Sheets(2).Cells(i + 1, 1) = mycoll(i)
        ThisWorkbook.Sheets(1).Range("C6").AutoFilter field:=3, Criteria1:=mycoll(i)
        Set myrng = ThisWorkbook.Sheets(1).Range("A7:A" & rowcount).SpecialCells(xlCellTypeVisible)
        logouttime = Format(WorksheetFunction.Max(myrng), "hh:mm:ss AMPM")
        logintime = Format(WorksheetFunction.Min(myrng), "hh:mm:ss AMPM")
        ThisWorkbook.Sheets(2).Cells(i + 1, 1) = mycoll(i)
        ThisWorkbook.Sheets(2).Cells(i + 1, 2) = logintime
        ThisWorkbook.Sheets(2).Cells(i + 1, 3) = logouttime
        ThisWorkbook.Sheets(2).Cells(i + 1, 4).Formula = "=" & ThisWorkbook.Sheets(2).Cells(i + 1, 3).Address & "-" & ThisWorkbook.Sheets(2).Cells(i + 1, 2).Address
        ThisWorkbook.Sheets(2).Cells(i + 1, 4).NumberFormat = "hh:mm:ss"
    Next
End Sub

Download File

Create Pivot Chart Using VBA

Option Explicit
Dim pvttbl As PivotTable, mychart As Chart, chrtrng As Range

Sub createPivotchart()
On Error Resume Next

For Each pvttbl In ThisWorkbook.Sheets(1).PivotTables
    ThisWorkbook.Sheets(1).Range(pvttbl.TableRange2.Address).Delete
Next
    ThisWorkbook.Sheets(1).ChartObjects.Delete
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(2).Range("A1").CurrentRegion.Address).CreatePivotTable tabledestination:=ThisWorkbook.Sheets(1).Range("B6"), tablename:="Pivottable1"
    Set pvttbl = ThisWorkbook.Sheets(1).PivotTables("pivottable1")
    With pvttbl
            .PivotFields("Region").Orientation = xlRowField
            .PivotFields("Category").Orientation = xlRowField
            .PivotFields("product").Orientation = xlRowField
            .PivotFields("Quantity").Orientation = xlDataField
    End With
    'Set pvttbl = Nothing
    Set chrtrng = pvttbl.TableRange2
    Set mychart = Charts.Add
    Set mychart = mychart.Location(xlLocationAsObject, "Product")
 
        With mychart
                .SetSourceData chrtrng
                .Parent.Top = ThisWorkbook.Sheets(1).Range("B6").Top
                .Parent.Width = ThisWorkbook.Sheets(1).Range("B6:M6").Width
                .Parent.Height = ThisWorkbook.Sheets(1).Range("B7:B24").Height
                .Parent.Left = ThisWorkbook.Sheets(1).Range("B7").Left
                .PlotArea.Format.Fill.Visible = msoTrue
                .PlotArea.Format.Fill.TwoColorGradient msoGradientVertical, 1
                .PlotArea.Format.Fill.ForeColor.RGB = RGB(255, 51, 0)
                .PlotArea.Format.Fill.BackColor.RGB = RGB(72, 80, 255)
                .PlotArea.Format.Fill.GradientStops(1).Position = 0.1
                .PlotArea.Format.Fill.GradientStops(2).Position = 0.9
                .ChartArea.Format.Fill.Visible = msoTrue
                .ChartArea.Format.Fill.TwoColorGradient msoGradientVertical, 1
                .ChartArea.Format.Fill.ForeColor.RGB = RGB(255, 51, 0)
                .ChartArea.Format.Fill.BackColor.RGB = RGB(72, 80, 255)
                .ChartArea.Format.Fill.GradientStops(1).Position = 0.1
                .ChartArea.Format.Fill.GradientStops(2).Position = 0.9
                .HasTitle = True
                .ChartTitle.Caption = "Total Quantity"
                .HasLegend = False
                .SeriesCollection(1).Interior.Color = RGB(135, 220, 55)
        End With
End Sub


Download Chart

Create Hyperlinks for all Files in a Folder

Sub createHyperLink()
Dim I As Integer
Dim fl As File
Dim fldr As Folder
Dim sh As Worksheet
Dim fso As FileSystemObject
Dim path As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    path = .SelectedItems(1)
    Range("A1") = "File Name"
End With

I = 2

Set fso = New FileSystemObject
Set fldr = fso.GetFolder(path)
    For Each fl In fldr.Files
        ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name
            If ThisWorkbook.Sheets(1).Cells(I, 1) = fl.Name Then
                ThisWorkbook.Sheets(1).Cells(I, 1).Select
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fl.path
            End If
        I = I + 1
    Next fl

End Sub

Move files from One Folder to another through VBA

Option Explicit
Dim sourcepath, destinationpath As String
Dim fso As Object, fl As Object, i As Integer, j As Integer, k As Integer

Sub movefilesfromFolders()
On Error GoTo errorHandler
 
    'return type Application.getopenfile is variant
    sourcepath = Application.GetOpenFilename(MultiSelect:=True)
 
    If IsArray(sourcepath) = True Then
    Set fso = New Scripting.FileSystemObject
        Application.FileDialog(msoFileDialogFolderPicker).Show
        destinationpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
     
            For i = LBound(sourcepath) To UBound(sourcepath)
             
                fso.movefile sourcepath(i), destinationpath & fso.GetFileName(sourcepath(i))
 
            Next
    Else
        MsgBox "File not selected"
    End If
Exit Sub
errorHandler:
    MsgBox "Error" & Err.Description
 

End Sub

Create Comment Chart via VBA

Option Explicit
Dim cmt As Comment, myrng As Range, rowcount As Long, mychart As Chart
Dim pathname As String, i As Long, datarng As Range, axisrng As Range, captionrange As Range

Private Sub Workbook_Open()


On Error Resume Next
Application.ScreenUpdating = False
    i = 2
    rowcount = ThisWorkbook.Sheets(1).Range("A2").End(xlDown).Row
    pathname = "C:\temp\"
    For i = 2 To rowcount
        Set myrng = ThisWorkbook.Sheets(1).Range("G" & i)
        myrng.Comment.Delete

            Set cmt = myrng.AddComment
            Set mychart = Charts.Add
            Set mychart = mychart.Location(xlLocationAsObject, "Sheet1")
            Set datarng = ThisWorkbook.Sheets(1).Range("B" & i & ":F" & i)
            Set axisrng = ThisWorkbook.Sheets(1).Range("B1:F1")
            Set captionrange = ThisWorkbook.Sheets(1).Range("A" & i)
            mychart.SetSourceData Source:=datarng
            mychart.SeriesCollection(1).XValues = axisrng
            mychart.SetElement msoElementChartTitleAboveChart
            mychart.ChartTitle.Caption = captionrange
            mychart.HasLegend = False
         
            mychart.Export pathname & "temp" & i & ".jpg", "jpg"
            With cmt.Shape
                    .Fill.UserPicture ("C:\temp\temp" & i & ".jpg")
                    .ScaleHeight 2, msoFalse
                    .ScaleWidth 3, msoFalse
            End With
        ThisWorkbook.Sheets(1).ChartObjects.Delete
    Next
    Set myrng = Nothing
    Set mychart = Nothing
    Set datarng = Nothing
    Set axisrng = Nothing
    Set captionrange = Nothing



End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyUGRDLV9IbWMxSkk/edit?usp=sharing

Sparkline chart through VBA

Option Explicit
Dim myChart As Chart, rowcount As Long, i As Long, myrng As Range, datarng As Range
Sub createcellChart()
On Error Resume Next
i = 2
myrng.SparklineGroups.Clear
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set myrng = ThisWorkbook.Sheets(1).Range("F2:F" & rowcount)
Set datarng = ThisWorkbook.Sheets(1).Range("A2:E" & rowcount)
myrng.SparklineGroups.Add Type:=xlSparkColumn, SourceData:=datarng.Address
     With myrng.SparklineGroups.Item(1)
        .SeriesColor.Color = RGB(112, 48, 160)
        .LineWeight = 1.5
        With .Points
            .Highpoint.Visible = True
            .Highpoint.Color.Color = RGB(0, 176, 240)
            .Lowpoint.Visible = True
            .Lowpoint.Color.Color = RGB(255, 0, 0)
        End With
    End With

End Sub


https://drive.google.com/file/d/0B23eJ2xd9ODycUJydHM5MEllQTQ/edit?usp=sharing

Read & write *.txt file in VBA

Option Explicit
Dim pathname As String, fso As Object, tso As Object, tempfile As String
Dim linecounter As Integer, tempvar, i As Integer
Sub readtextFile()
    With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                .Title = "Select .txt File to read"
    pathname = .SelectedItems(1)
   
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    tempfile = pathname & "\data.txt"
Set tso = fso.getFile(tempfile).OpenAsTextStream(1, -2)
linecounter = 0
    While Not tso.AtEndofStream() <> False
        linecounter = linecounter + 1
        i = 0
        tempvar = tso.readline
            For Each cell In Split(tempvar, " ")
                i = i + 1
                    Cells(linecounter, 1) = cell
            Next
    Wend
    Set tso = Nothing
    Set fso = Nothing
End Sub



Dim pathname As String, fso As Object, tso As Object, tempfile As String
Dim linecounter As Integer, tempvar, i As Integer
Sub readtextFile()
    With Application.FileDialog(msoFileDialogFolderPicker)
                .Show
                .Title = "Select .txt File to read"
    pathname = .SelectedItems(1)
   
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    tempfile = pathname & "\data.txt"
Set tso = fso.getFile(tempfile).OpenAsTextStream(1, -2)
linecounter = 0
    While tso.AtEndofStream() <> True
        linecounter = linecounter + 1
        i = 0
        tempvar = tso.readline
            For Each cell In Split(tempvar, " ")
                i = i + 1
                    Cells(linecounter, 1) = cell
            Next
    Wend
    Set tso = Nothing
    Set fso = Nothing
End Sub

Generate Chart on Worksheet_Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$2" Then
        Select Case Target.Value
            Case "SOUTH"
                Call createGraph(Target.Value)
     
            Case "WEST"
                Call createGraph(Target.Value)
            Case "NORTH"
                Call createGraph(Target.Value)
            Case "MIDWEST"
                Call createGraph(Target.Value)
         
        End Select
    End If
End Sub


Dim myChart As Chart, rowcount As Long, datarng As Range, axisrng As Range
Public Sub createGraph(mycriteria As String)
'On Error Resume Next
Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Dashboard").Range(ThisWorkbook.Sheets("Dashboard").Range("D6").CurrentRegion.Address).Clear
    ThisWorkbook.Sheets(2).Range("A1").AutoFilter Field:=1, Criteria1:=mycriteria
    ThisWorkbook.Sheets(2).UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=ThisWorkbook.Sheets(1).Range("D6")
    ThisWorkbook.Sheets(1).ChartObjects.Delete
    rowcount = ThisWorkbook.Sheets(1).Range("D6").End(xlDown).Row
    Set datarng = ThisWorkbook.Sheets(1).Range("J7:J" & rowcount)
    Set axisrng = ThisWorkbook.Sheets(1).Range("D7:D" & rowcount)

    Set myChart = Charts.Add
    Set myChart = myChart.Location(xlLocationAsObject, "Dashboard")
 
        With myChart
            .Parent.Top = ThisWorkbook.Sheets(1).Range("D6").Top
            .Parent.Left = ThisWorkbook.Sheets(1).Range("D10").Left
            .Parent.Width = ThisWorkbook.Sheets(1).Range("D10:J10").Width
            .Parent.Height = ThisWorkbook.Sheets(1).Range("D6:D20").Height
            .SetSourceData Source:=datarng
            .SeriesCollection(1).XValues = axisrng
            .HasTitle = True
            .ChartType = xlColumnClustered
            .SetElement (msoElementChartTitleAboveChart)
            .ChartTitle.Caption = "Sales amount for " & mycriteria
        End With
    End Sub


https://drive.google.com/file/d/0B23eJ2xd9ODyZ2V1LUdYNXVmMFE/edit?usp=sharing

Change Pivottable Datafield Function

Option Explicit
Dim pvttable As PivotTable
Sub createPivottable()
On Error Resume Next
For Each pvttable In ThisWorkbook.Sheets("Soum").PivotTables
    ThisWorkbook.Sheets("Soum").Range(pvttable.TableRange2.Address).Delete
Next
    ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ThisWorkbook.Sheets(3).Range("a2").CurrentRegion.Address).createPivottable tabledestination:=ThisWorkbook.Sheets("Soum").Range("C6"), tablename:="Pivottable1"
    With ThisWorkbook.Sheets("Soum").PivotTables("Pivottable1")
            .PivotFields("Product Name").Orientation = xlRowField
            .PivotFields("HelpColumn").Orientation = xlColumnField
            .PivotFields("Base Amount").Orientation = xlDataField
           ThisWorkbook.Sheets("Soum").Range("D8").PivotField.Function = xlSum
    End With
 
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODycnptQzRMVElXVEk/edit?usp=sharing

Copy Range in a Faster Way

Option Explicit
Dim rawdata, myrng As Range
Sub copyRange()
rawdata = ThisWorkbook.Sheets(1).Range("a1:b1726")
ThisWorkbook.Sheets(2).Range("a1").Resize(UBound(rawdata, 1), UBound(rawdata, 2)) = rawdata
End Sub

Split File and send e-mail with Attachment

Task : split an excel file into separate files using particular customer
codes and then email these directly to a particular email account depending
on the  customer code(column A).



Option Explicit
Dim mycoll As Collection, rowcount As Long, myrng As Range, cell As Object, j As Long
Dim destinationwb As Workbook, outApp As Outlook.Application, outMail, newwb As Workbook

Sub createFiles()
Set mycoll = New Collection
On Error Resume Next
rowcount = ThisWorkbook.Sheets(1).Range("A3").End(xlDown).Row

Set myrng = ThisWorkbook.Sheets(1).Range("A3:A" & rowcount)
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)


    Next
    For j = 1 To mycoll.Count
    Set outApp = New Outlook.Application
    Set outMail = outApp.CreateItem(olMailItem)
    Set destinationwb = Workbooks.Add
    destinationwb.SaveAs Filename:=ThisWorkbook.Path & "\" & mycoll(j), FileFormat:=56
        ThisWorkbook.Sheets(1).Range("A2").AutoFilter field:=1, Criteria1:=mycoll(j)
        ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=destinationwb.Sheets(1).Range("A1")
         Set newwb = ActiveWorkbook
     
     
        destinationwb.Save
        With outMail
            .To = mycoll(j) & "@yahoo.com"
            .Subject = "Monthly Account Summary"
         
            .Body = "Hi," & vbNewLine & "     Please find the attachment" & vbNewLine & "Regards," & vbNewLine & "Soumyendu"
         
            .Attachments.Add (ThisWorkbook.Path & "\" & newwb.Name)
            .Display
         
        End With
        Application.DisplayAlerts = False
        destinationwb.Close

         Application.DisplayAlerts = False
         Application.Wait (Now + TimeValue("0:00:02"))
         Application.SendKeys "%s" '
        Set outMail = Nothing
     
        Set outApp = Nothing
     
    Next
End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyb2VmRm1vblJjWGM/edit?usp=sharing

Insert Data Through VBA

Option Explicit
Dim conn As New ADODB.Connection

Dim querystring As String
Dim id As Integer, empname As String, age As Integer, address As String, salary As Long, i As Integer, rowcount As Long
Sub insertData()
i = 2
On Error Resume Next
conn.ConnectionString = "Provider=SQL Native Client ;Integrated Security=SSPI; Data Source=New\SQLExpress;Initial Catalog=Somu"
rowcount = ThisWorkbook.Sheets(1).Range("a2").End(xlDown).Row

conn.Openq

    For i = 2 To (rowcount - 1)

            id = CInt(ThisWorkbook.Sheets(1).Range("A" & (i + 1)))
            empname = ThisqWorkbook.Sheets(1).Range("B" & (i + 1))
            age = CInt(ThisWorkbook.Sheets(1).Range("C" & (i + 1)))
            address = ThisWorkbook.Sheets(1).Range("D" & (i + 1))
            salary = CLng(ThisWorkbook.Sheets(1).Range("E" & (i + 1)))
            querystring = "Insert into Customers(Id, EmpName,Age,Address,Salary) values(" & id & ",'" & empname & "' ," & age & ",' " & address & "'," & salary & ")"
         
            conn.Execute querystring
 
    Next
conn.Close
Set conn = Nothing

End Sub

https://drive.google.com/file/d/0B23eJ2xd9ODyRUE5Q1luaGdnTUk/edit?usp=sharing