Opening a new window and Login In using Internet Explore Using MS Excel VBA

Using excel VBA you can open IE Window and login in it. Following are the details for this operation.

Opening Internet Explore through excel VBA

1)      Some references should be added as follows:- These are already available in excel

a.      Microsoft HTML Object Library

b.      Microsoft Internet Controls

c.      Microsoft Forms 2.0 Object Library

2)      Code is as follow:-

             

               Dim sURL As String

              

' set url for opening here

               sURL = " http://infoenginesol.blogspot.in/"

               Set oBrowser = New InternetExplorer

               IEBrowser.Silent = True ‘if you want to open browser in silent mode

               IEBrowser.timeout = 60 ‘optionally set timeout for loading site

               IEBrowser.navigate sURL

               IEBrowser.Visible = True

              

               ' Following code will wait till Browser is loaded

              

                              Do

                              Loop Until oBrowser.readyState = READYSTATE_COMPLETE

               ‘wait included to wait till application is completed

               Application.Wait (Now + TimeValue("0:00:20"))

              

‘ for automatically login into site. On this blog you may not login, but for other site, it can be done ;-)

‘For searching proper control use “Right Click” and then “View Source”.

‘Search for word “login” or “password”. This is the way I found it; you have to search through browser source.

               Set HTMLDoc = IEBrowser.document

               HTMLDoc.all.LOGINCtrl<Use your control>.Value = “My Login”

               HTMLDoc.all.PasswordCtrl<Use your control>.Value =  “My Password”

               HTMLDoc.all("Submit_Button").Click

Application.Wait (Now + TimeValue("0:00:50"))

‘ if you want to quit after completing operation then use following or comment it

IEBrowser.Quit

Get Column heading/name of selected cell through VBA (Excel)

In VBA, For getting the column heading/name of a selected cell  i.e. Column "A" or Column "B" use below code in macro.

CurCol = Split(ActiveCell.Address, "$")(1)

SharePoint 2010: Display Image in List on MouseOver

In SharePoint 2010, Sometimes Picture is shown in lists using "Picture" Column , but it does not look good if picture size is large; instead we can have image preview on the hyperlink in the list by hovering the mouse,

Click on below link to see awesome solution by Alexander Bautz .

https://www.nothingbutsharepoint.com/sites/eusp/Pages/sharepoint-image-preview-on-hover-over-a-hyperlink-field.aspx

Removing unprintable, unreadable character (Replace ASCII Characters from Excel WorkSheet)

Removing unwanted, unprintable characters in a Cell in Excel!!

We copy and paste data from web source into excel and use it for further detailing, analysis using excel. Data of teams, districts, states when copied from web, it could have some unprintable, non-viewable ascii characters within Cell. Due to such character presence excels functions such as vlookup, hlookup, search, find, countif fails to give accurate and reliable result. We can consider following approaches to solve our problem:-

1. Enable Macros 
2. Open VBE using Alt + F11
3. Insert Module 
4. Paste Below Code

---------------------------------------------------------------------------------

Option Explicit

Dim Currow, LastRow, LastCol As Integer

Dim Curcol As String

Sub Remove_NonPrintable_Characters()

Application.ScreenUpdating = False

 If Range("A1").Value <> "" Then

        LastCol = Range("IV1").End(xlToLeft).Column

        LastRow = Cells(Rows.Count, "A").End(xlUp).Row

        

        Range("A1").Select

     Do

        Curcol = Split(ActiveCell.Address, "$")(1)

        Do

            Currow = Selection.Cells.Row

            Range(Curcol & Currow).Value = removeall(Range(Curcol & Currow).Value)

            ActiveCell.Offset(1, 0).Select

        Loop Until Currow >= LastRow

            ActiveCell.Offset((0 - Currow), 1).Select

     Loop Until Selection.Cells.Column > LastCol

    End If

    

Application.ScreenUpdating = True

End Sub

Public Function removeall(CellValue As String) As String

Dim ErrChar As Integer

Dim NewVal As String

Dim i As Integer

For i = 1 To Len(CellValue)

    

    ErrChar = Asc(Mid(CellValue, i, 1))

    

    Select Case ErrChar

        Case Is < 91 And ErrChar > 64

            NewVal = NewVal & Chr(ErrChar)

        Case Is < 123 And ErrChar > 96

            NewVal = NewVal & Chr(ErrChar)

        Case Is = 32

            NewVal = NewVal & Chr(ErrChar)

        Case Is < 59 And ErrChar > 44

            NewVal = NewVal & Chr(ErrChar)

  

    End Select

Next i

removeall = NewVal

End Function

---------------------------------------------------------------------------------

(Click Here for ASCII Table)

Sorting Pivot Table by Last Column in Row Field (Row Label)

In a Pivot Table, if there are 5 or more than 5 columns as Row fields (Row label) and if we want to sort entire pivot table by values available in column 5 then it is not possible to sort Column 5 values only using Sort options (Except manually dragging rows in a pivot table) as data is grouped based on previous columns and it will be sorted within groups of previous columns. Sorting in Pivot is based on primary(First) column and then subsequent columns.

For more Info - http://answers.microsoft.com/en-us/office/forum/office_2007-excel/sorting-in-a-pivot-table/b83ef899-6c49-4a98-afb2-65ac2fc43f97

there is a workaround as below; 

In your source data, Add a new column with same values with column that you want to sort at position 5; refresh current pivot or create a new pivot; create pivot layout as per your requirement; Place newly created column as first column ; do sorting on first column and hide first column of pivot table ; if you have "Report Filters" then report filter headings will also become invisible due to hiding column hence write "report filter " headings in the cells next to the "Report filter" selection drop downs" or Else instead of hiding column just format the first row field in  such a way that one can not see any values there (like applying sheet colour to fonts)

Unhide Sheets using Single Click

In MS Excel, hide multiple sheets using a single click is available i.e. Select multiple sheets by pressing control button, and then right click and select Hide Sheets. But this is not available for unhidden sheets.

Below is VBA add-in to unhide sheets using single click

1. Open a New Excel File

2. Make Sure Macros are enabled (Click Here to Know How to Enable Macros)

3. Open Visual Basic Editor (VBE) using Alt + F11

4. Insert Module

5. Add below code and Close VBE

 ===============================================================

 ' Add-In Unhide Sheets using a Single Click

Option Explicit

Sub UnHideSheets()

Dim i, cnt as integer

    cnt = ActiveWorkbook.Worksheets.Count
    i = 1
  Do
     Sheets(i).Visible = True
     i = i + 1
  Loop Until i > cnt

End Sub

===============================================================

 6. Click on "Save As" file

 7. Select "Excel Add-In" in the "Save-As Type" Dropdown and Save file as "Showsheets"

7. Save File as "Add-In" File

8. Close Excel File

Now, It should be available for display sheets using single click

Manage Add-In

1. Open MS Excel

2. Click on File -> Options  For Excel 2010  (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")

3. Select Add-Ins and then from "Manage" Drop Down (At Bottom) - Select "Excel Add-In" And then Click on "Go"

4. Select "ShowSheets" and Click on OK

Map Add-In to Button

1. Click on File -> Options (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")

2. Select "Quick Access Toolbar"  For Excel 2010 (For Excel 2007 - Click On "Customize")

3. Select “Macros” from “Choose Commands from”

4. Select "UnHideSheets" and Add

5. Then Under “Customize Quick Access Toolbar”

     a. Select "For all Documents (Default)"

     b. From Below List Select "UnHideSheets" and Click on Modify

6. Select any sign & give any display name that you like to view then Click Ok

7.  Click “Ok” , Save & Close file

8. New icon will be created at top

9. When you want to unhide sheets of any workbook click on the newly created button; it will show all hidden sheets from that workbook.

Scheduling To run Macro using VBScript

To Execute Excel Macros through VBScript
Here is the link