TechTalk

Exporting gridview contents to excel is one of the most common requirements in many asp.net applications. I found a helper class in the following link to be very useful- http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html One limitation of the class is that it doesn't work if you have grid paging turned on - it only exports the rows that are visible on the page, not the whole grid. To workaround this call the disable Gridview paging as the first line of the Export() Sub and re-enable paging as the last line of the Export() Sub. Also to have this working with a Master Page you need to override a method called VerifyRenderingInServerForm as follows public override void VerifyRenderingInServerForm(Control control) { } If you want to remove any columns from being exported then add a int16[] parameter to the export function and call a new method (displayed below) instead of PrepareControlForExport in the export function.; code: private stat...

One often needs a Excel Column's alphabetic letter from the column number e.g Column No.3 corresponds to ColumnLetter - C      Column No.27 corresponds to ColumnLetter - AA I use the following function to achieve this Public Function ColumnLetter(ColumnNumber As Integer) As String   If ColumnNumber > 26 Then     ' 1st character:  Subtract 1 to map the characters to 0-25,     '                 but you don't have to remap back to 1-26     '                 after the 'Int' operation since columns     '                 1-26 have no prefix letter     ' 2nd character:  Subtract 1 to map the characters to 0-25,   ...

The following VBA procedure can be used to apply borders to a range of cells Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)      On Error Resume Next      With ToRange         With .Borders(xlDiagonalDown)             .LineStyle = xlNone         End With         With .Borders(xlDiagonalUp)             .LineStyle = xlNone         End With                 With .Borders(xlEdgeLeft)             .LineStyle = xlContinuous             .Weight = xlThin        ...

'The following function returns a unique list if passed an array of values Public Function UniqueValues(SourceValues As Variant) As Variant 'Returns a variant containing the unique values contained within SourceValues 'If called from a worksheet array formula, returns either a row or column array, as needed. Dim Items As New Collection Dim i As Long, j As Long, m As Long, nCols As Long, nRows As Long, Row As Long Dim rg As Range Dim cel As Variant, Result() As Variant On Error Resume Next Set rg = Application.Caller For Each cel In SourceValues    If cel <> "" Then Items.Add CStr(cel), CStr(cel) Next If rg Is Nothing Then Else     nCols = rg.Columns.Count     nRows = rg.Rows.Count     m = Application.Max(nCols, nRows) End If On Error GoTo 0 i = Items.Count ReDim Result(1 To i) For Row = 1 To i      j = 0     If rg Is Nothing Then         For Each cel In...

If you want to open a website and go through the results of a webpage using VBA you can achieve it by first including a reference to Microsoft HTML Object Library in your VBA editor. The following snippet of code should be a good starting point of how you can achieve the same Sub GoToWebSiteAndPlayAround() Dim appIE As Object ' InternetExplorer.Application Dim sURL As String Application.ScreenUpdating = False Set appIE = CreateObject("InternetExplorer.Application") 'URL with the search term 'Cancer' at Science sURL = "http://www.google.com?q=vba" 'this URL to be replaced by your target web page With appIE .navigate sURL ' uncomment the line below if you want to watch the code execute, or for debugging '.Visible = True End With ' loop until the page finishes loading Do While appIE.readyState <> 4 DoEvents Loop 'Get info from HTML by ID and Name Dim outerDiv, innerSpan, requiredtext, HTMLDoc Dim ...

While using dates in excel one may have come across a situation that after entering a date in a cell, it shifts to the left-hand side of the cell. What this means is that Excel has not recognized the input as a valid date; i.e., the date is considered invalid by the application. The expected format of the date depends on the machine’s regional settings. For example, if the regional options of your machine is set to English (United Kingdom), then you need to enter dates in dd/mm/yyyy format (or dd-mm-yyyy). When you enter dates in the expected format they are aligned to the right hand side by default. (Note – here, “by default” means formatting of the cell has not been altered to impose left or center alignment). In this case (U.K. settings), if you type a date in mm/dd/yyyy format (or mm-dd-yyyy), then the date will move to the left-hand side of the cell (Please note that for this to happen the day in the date should be greater than 12). OR some of the dates will be left-aligned a...