Hello Friends,

Like Microsoft Excel , These days Access also has a great place in organizations, where we need to handle large database by using only SQL or Access DB. So we have create a discussion forum for Ms Access where people can post queries and can get experts advice.

Group Link:

Access-SQL Group

This Group provide a way to share and get  the knowledge of this nice tool ,It’s a group to share query and solutions related to Microsoft Access database and SQL query. here people  can post their queries and can get quick solution.

So  join this group and start the discussion about  our topic

Rajan verma

Gauge Chart : [Dial Chart]

Hello world

Wanna make an Amazing Gauge Chart ?

Its Formally Known as Dial Chart,you can follow some simple steps to create.

  I have used some VBA Code to make the needle animated :

Here is the Code to Move Needle

Sub MoveNiddle()

    DoEvents

    If Range("rngNiddlePos").Value = Range("Sales").Value Then Exit Sub

    Range("rngNiddlePos").Value = Range("rngNiddlePos").Value + 1

    MoveNiddle  ' Recursive Function

End Sub

Sub Move()

    Range("rngNiddlePos").Value = 0

    MoveNiddle

End Sub  

Download Link for animated dial chart is :

http://www.2shared.com/file/VjAbZvKC/DialChart1.html



Download link for Steps - How to Create Dial Chart :
http://www.2shared.com/file/eOFOlhUY/DialChartSteps.html



Hope you will like it

Pie Chart :

Pie chart helps to visualize the percentage of data, its make a circle of 100%  by spliting other product/things percentage

here is a file you can make use for you of Pie chart


http://www.2shared.com/file/dytBu96u/PieChart.html

Thanks for reading

Waterfall chart :

A waterfall chart is an intuitive tool to visualize the cumulative effect of sequentially introduced positive or negative values. 

here is file you can download the waterfall template [Automated through VBA ] , you just need to give data and press button

http://www.2shared.com/file/1kSiTtg7/WaterFall.html

hope you  will enjoy.

Looping Through Folders and Files in VBA:

Hi Guys,
Many times we need to loop through folders , sub folders and files , i have created a code to get the folder structure of a given path


=======================================================================
Public ObjFolder As Object

Public objFso As Object

Public objFldLoop As Object

Public lngCounter As Long

Public  objFl As Object

   

'=================================================== 

               'A procedure to call the Function  LoopThroughEachFolder(objFolder)

'===================================================     

   

Sub GetFolderStructure()

'

    lngCounter = 0

    Set objFso = CreateObject("Scripting.FileSystemObject")

    With Application.FileDialog(msoFileDialogFolderPicker)

        .Show

        Set ObjFolder = objFso.GetFolder(.SelectedItems(1))

    End With

    Range("A1").Offset(lngCounter).Value = ObjFolder.Path

    LoopThroughEachFolder ObjFolder

   

End Sub

'=================================================== 

               'Function to Loop through each Sub Folders

'=================================================== 

Function LoopThroughEachFolder(fldFolder As Object)

    For Each objFldLoop In fldFolder.subFolders

    lngCounter = lngCounter + 1

    Range("A1").Offset(lngCounter).Value = objFldLoop.Path

    LoopThroughEachFolder objFldLoop

    Next

End Function

'=================================================== =========

           'In below function we have a nested loop to iterate each files also 

'=================================================== =========

Function LoopThroughEachFolder1(fldFolder As Object)

    For Each objFldLoop In fldFolder.subFolders

    lngCounter = lngCounter + 1

    Range("A1").Offset(lngCounter).Value = objFldLoop.Path

             For Each objFl In objFldLoop.Files

                Range("A1").Offset(lngCounter, 1).Value = objFl.Name

                lngCounter = lngCounter + 1

             Next

    LoopThroughEachFolder1 objFldLoop

    Next

End Function

Excel Solver :

As I study the solver, I feel this is elder brother of Goal Seek in Excel ,where we can change only one value with Goal seek , Using Solver we can provide many cell for adjusting value as per our scenario , Below is the Parameter required to solver ,

We can add many scenario to see different analysis,

Using Spreadsheets.  Spreadsheets such as Microsoft Excel provide a convenient way to build such a model.  Cells on a worksheet can hold numbers, labels, or formulas that calculate new values.

• The decision variables for a model are simply worksheet cells containing numbers that Solver can change.
• The objective is a cell containing a formula you want Solver to maximize (or minimize) by adjusting the values of the decision variable cells.
• Constraints are logical conditions on formula cells that must be satisfied (specified with <=, = or >= relations). Frontline's various Solver products provide powerful tools for solving, or optimizing, such models.

 Scree shot of Solver :

So, in the example Range A9 is an objective which  has a formula to do some calculation based on arguments provided as reference, Range A1:A8 are the static values which we are need to change by solver, By Pressing Add it will ask for range associated with some logical condition [<=] , after add the Constraints ,Press solve it will adjust the value of All cells to get the final result as you specified in ValueOf parameter.

Thanks for reading .. 

                                     

Dictionary Object :

when playing with data many times we needs to save some unique values somewhere, that time we can use dictionary object , it does not allow to store duplicate values in it, 

Dictionary object Takes two argument at the time of adding data 1) Key .2 ) Value and it returns the value by  ID, so when we need to retrieve any values just we need to give ID number , A very good example is given here to understand Dictionary object :

                      This procedure bifurcate data from one worksheet to multiple worksheets based on multiple values exist in Column "B" on Sheet1 , first it stores all the unique from B column in dictionary and then start bifurcating data.

Sub DistributeDataOnSheets()
    Set objDic = CreateObject("Scripting.Dictionary")
        If Not objDic.Exists(VarFilterData(lngLoop)) Then objDic.Add VarFilterData(lngLoop), VarFilterData(lngLoop)

    Dim VarFilterData()

    Dim objDic As Object

    Dim wksSheet As Worksheet

    Dim lngLoop As Long

    Dim rngRange As Range

    Dim wkSSheetNew As Worksheet

   

    Set wksSheet = ThisWorkbook.Worksheets("Sheet1")

    VarFilterData = Application.Transpose(Intersect(wksSheet.UsedRange, wksSheet.UsedRange.Columns(2).Offset(1)))

   

    For lngLoop = LBound(VarFilterData) To UBound(VarFilterData)

    Next lngLoop

    Application.ScreenUpdating = False

    For lngLoop = 1 To objDic.Count

        With wksSheet.UsedRange.Columns(2)

            .Replace VarFilterData(lngLoop), ""

            Set rngRange = .SpecialCells(xlCellTypeBlanks)

            rngRange.Value = VarFilterData(lngLoop)

        End With

        Application.DisplayAlerts = False

        On Error Resume Next

            ThisWorkbook.Worksheets(VarFilterData(lngLoop)).Delete

        On Error GoTo 0: On Error GoTo -1

        Application.DisplayAlerts = True

        Set wkSSheetNew = ThisWorkbook.Worksheets.Add

        wkSSheetNew.Name = VarFilterData(lngLoop)

        wksSheet.Rows(1).Copy wkSSheetNew.Range("A1")

        rngRange.EntireRow.Copy wkSSheetNew.Range("A2")

    Next lngLoop

   

    Application.ScreenUpdating = True

    MsgBox "Done"

End Sub

Hope you will like it