On Authentication Timeout - Save Web Form Contents

27. February 2008

I was working on a project recently where I had to figure out how to persist user data in a web form after their Forms Authentication session as expired.

So I was thinking of ways to do this as I searched the know all Google. Walla!  Someone's already had to do this, why invent the wheel.  Found a post by Peter Ravnholt, on the MSDN site. http://code.msdn.microsoft.com/formsaver 

He Implements the HttpModule and handles saving the form state on FormAuthentication events. Really neat idea.  I tried is code, now keep in mind I'm doing this for Visual Basic, so I manually converted it all, but I couldn't get it work. Maybe I was converting something wrong.  I was getting "The state information is invalid for this page and might be corrupted."

Well RichardDeeming in the discussions for this post had an updated version that worked a little better then the original. http://code.msdn.microsoft.com/formsaver/Thread/View.aspx?ThreadId=88  Thanks to his code, I converted it to VB.Net and walla!  My pages now handle web content.

I'll post the converted VB version below. 

Thanks Richard and Peter if you ever find your names on here via Google :p

This is my VB version o the FormSaverHttpModule with Richard's changes.  I've modified it a bit since but this works.

Imports Microsoft.VisualBasic
Imports System.Security.Principal
Imports System.IO
Imports System.Web.Configuration
Imports System.Security.Permissions
 
''' <summary>
''' FormSaverModule Version 1.0
''' -Mastro:  Not my orignal idea, found this in use out on MSDN site. I manually converted it to VB
''' as the orignaly is in C#. No comments, so I'll add my own.
''' 
''' This class will handle every new request coming in and PostMapRequest. 
''' If user has Forms Authentication has timed out, class will save the state of the form into cache
''' with a Unique ID of that state to the user's cookie. 
''' 
''' When user logs back in, this class will check if they have the cookie set, if so it will then 
''' load that state back to their page and load the webcontent by generating a runtime transit page that
''' post submits the data back to the destination page and pre-fills in the values.
''' </summary>
''' <remarks></remarks>
<AspNetHostingPermission(SecurityAction.LinkDemand, Level:=System.Web.AspNetHostingPermissionLevel.Minimal)> _
<AspNetHostingPermission(SecurityAction.InheritanceDemand, Level:=AspNetHostingPermissionLevel.Minimal)> _
Public Class FormSaverHTTPModule
    Implements IHttpModule
 
#Region "Private Variables"
 
    Private Const CookieName As String = "#FormStateSaverModule/FormRestoreId"
    Private Shared ReadOnly StateCacheDuration As TimeSpan = TimeSpan.FromMinutes(40)
    Private Shared ReadOnly _AnonymousUser As IPrincipal = New GenericPrincipal(New GenericIdentity(String.Empty), Nothing)
    Private _FormsCookieName As String
    Private _LoginUrl As String
 
#End Region
 
    ''' <summary>
    ''' This class is required from the IHttpModule contract
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Dispose() Implements System.Web.IHttpModule.Dispose
 
    End Sub
 
    ''' <summary>
    ''' This is the first method to run on application start
    ''' </summary>
    ''' <param name="Context"></param>
    ''' <remarks></remarks>
    Public Sub IHttpModuleInit(ByVal Context As HttpApplication) Implements System.Web.IHttpModule.Init
        Me.Init(Context) 'Lets call our Init and send the Application state along with it
    End Sub
 
    ''' <summary>
    ''' We call this Init Method from the IHttpModuleInit and send it the Application State
    ''' and then turn on Events to capture
    ''' </summary>
    ''' <param name="Context">Hold's the Application State</param>
    ''' <remarks></remarks>
    Protected Overridable Sub Init(ByVal Context As HttpApplication)
        If Context Is Nothing Then
            Throw New ArgumentNullException("context")
        End If
 
        _LoginUrl = FormsAuthentication.LoginUrl 'Store the Login page name
        _FormsCookieName = FormsAuthentication.FormsCookieName 'Store the Forms Authentication Cookie Name
 
        If Not String.IsNullOrEmpty(_LoginUrl) Then 'If LoginUrl Exists....
            Dim index As Integer = _LoginUrl.IndexOf("?"c) 'Search for a ? for query string
            If -1 <> index Then 'If ? was found....
                _LoginUrl = _LoginUrl.Substring(0, index) 'Grab the first part of the URL before the ? and set LoginUrl
            End If
        End If
 
        'Add Handlers that now raise events on every Application BeginRequest... and Application PostMapRequest
        AddHandler Context.BeginRequest, New EventHandler(AddressOf Application_BeginRequest)
        AddHandler Context.PostMapRequestHandler, New EventHandler(AddressOf Application_PostMapRequestHandler)
 
    End Sub
 
    ''' <summary>
    ''' A Delegate receiver method for the Application.BeginRequest Event
    ''' </summary>
    ''' <param name="sender">Object</param>
    ''' <param name="e">EventArgs</param>
    ''' <remarks></remarks>
    Protected Overridable Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
        Dim application As HttpApplication = DirectCast(sender, HttpApplication)
        Dim context As HttpContext = application.Context
 
        Dim cookie As HttpCookie = context.Request.Cookies(CookieName)
        If cookie IsNot Nothing AndAlso Not String.IsNullOrEmpty(cookie.Value) Then 'If Cookie exist and has value....
            Dim state As FormState = FormState.Load(cookie.Value) 'Lets try and load the state via the CookieID
 
            'Lets see if the State is valid now that it's loaded and it's originaly location matches where the user is trying to go
            If state IsNot Nothing AndAlso String.Equals(state.Path, context.Request.Path, StringComparison.OrdinalIgnoreCase) Then
                FormState.SetCurrent(context, state) 'State is valid and it's for this request so lets create the object of the state to be used later by Application_PostMapRequestHandler
                Return
            End If
        End If
 
        If IsPost(context) AndAlso Not IsAccessingLoginPage(context, _LoginUrl) Then 'If user is doing a Post back and it's not to the login page...
            cookie = context.Request.Cookies(_FormsCookieName) 'Load Authenication cookie
            If cookie IsNot Nothing Then
                Try
                    Dim ticket As FormsAuthenticationTicket = FormsAuthentication.Decrypt(cookie.Value)
                    If ticket IsNot Nothing AndAlso ticket.Expired AndAlso Not HasAnonymousAccess(context) Then 'If the user has expired.....
                        Dim state As FormState = FormState.Create(context) 'Store current state into cache
                        If state IsNot Nothing Then
                            cookie = New HttpCookie(CookieName, state.StateId) 'save cache ID into user's cookie
 
                            cookie.HttpOnly = True
                            context.Response.Cookies.Add(cookie)
                        End If
                    End If
                Catch generatedExceptionName As ArgumentException
                End Try
            End If
        End If
    End Sub
 
    Protected Overridable Sub Application_PostMapRequestHandler(ByVal sender As Object, ByVal e As EventArgs)
        Dim application As HttpApplication = DirectCast(sender, HttpApplication)
        Dim context As HttpContext = application.Context
 
        Dim state As FormState = FormState.GetCurrent(context) 'Get the context from the cache if it's there
        If state IsNot Nothing AndAlso state.Form IsNot Nothing AndAlso 0 <> state.Form.Count Then 'if the context was there....
            context.Handler = New FormStateSaverHandler(state) 'Then load the runtime transit page and post it back to destination page
        End If
    End Sub
 
    ''' <summary>
    ''' Returns Boolean if request is a Post request to page.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Shared Function IsPost(ByVal context As HttpContext) As Boolean
        Return String.Equals("POST", context.Request.HttpMethod, StringComparison.OrdinalIgnoreCase)
    End Function
 
    ''' <summary>
    ''' Return Boolean after it checks to see if content matches the LoginURL. Basically is the user 
    ''' going to the login page.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Shared Function IsAccessingLoginPage(ByVal context As HttpContext, ByVal loginUrl As String) As Boolean
        Dim result As Boolean = False
        If Not String.IsNullOrEmpty(loginUrl) Then
            If String.Equals(context.Request.Path, loginUrl, StringComparison.OrdinalIgnoreCase) Then
                result = True
            ElseIf -1 <> loginUrl.IndexOf("%"c) Then
                Dim temp As String = HttpUtility.UrlDecode(loginUrl)
                If String.Equals(context.Request.Path, temp, StringComparison.OrdinalIgnoreCase) Then
                    result = True
                Else
                    temp = HttpUtility.UrlDecode(loginUrl, context.Request.ContentEncoding)
                    If String.Equals(context.Request.Path, temp, StringComparison.OrdinalIgnoreCase) Then
                        result = True
                    End If
                End If
            End If
        End If
 
        Return result
    End Function
 
    ''' <summary>
    ''' Returns Boolean if the destination URL in question has anonymouse access.
    ''' </summary>
    ''' <param name="context"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Shared Function HasAnonymousAccess(ByVal context As HttpContext) As Boolean
        Return UrlAuthorizationModule.CheckUrlAccessForPrincipal(context.Request.Path, _AnonymousUser, context.Request.HttpMethod)
    End Function
 
    ''' <summary>
    ''' This class becomes the actual FormState the user was on and is Serialzied and then saved 
    ''' </summary>
    ''' <remarks></remarks>
    <Serializable()> _
    Protected NotInheritable Class FormState
        Private Shared ReadOnly FormRestoreKey As New Object()
 
        Private ReadOnly _id As String
        Private ReadOnly _path As String
        Private ReadOnly _form As NameValueCollection
 
        Private Sub New(ByVal id As String, ByVal path As String, ByVal form As NameValueCollection)
            _id = id
            _path = path
            _form = form
        End Sub
 
        Public ReadOnly Property StateId() As String
            Get
                Return _id
            End Get
        End Property
 
        Public ReadOnly Property Path() As String
            Get
                Return _path
            End Get
        End Property
 
        Public ReadOnly Property Form() As NameValueCollection
            Get
                Return _form
            End Get
        End Property
 
        ''' <summary>
        ''' Loads the state that was set by Setcurrent and tries to return it as a FormState object.
        ''' Returns nothing if it's invalid.
        ''' </summary>
        ''' <param name="context"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetCurrent(ByVal context As HttpContext) As FormState
            If context Is Nothing Then
                Return Nothing
            End If
            Return TryCast(context.Items(FormRestoreKey), FormState)
        End Function
 
        ''' <summary>
        ''' Takes the state sent and creates a valid formstate object
        ''' </summary>
        ''' <param name="context"></param>
        ''' <param name="state"></param>
        ''' <remarks></remarks>
        Public Shared Sub SetCurrent(ByVal context As HttpContext, ByVal state As FormState)
            If context IsNot Nothing Then
                context.Items(FormRestoreKey) = state
            End If
        End Sub
 
        ''' <summary>
        ''' Takes the Context of the current Request Form and stores it into Cache with a Unique ID
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function Create(ByVal context As HttpContext) As FormState
            Dim result As FormState = Nothing
            If context IsNot Nothing AndAlso context.Request.Form IsNot Nothing AndAlso 0 <> context.Request.Form.Count Then
                Dim id As String = Guid.NewGuid().ToString()
                result = New FormState(id, context.Request.Path, context.Request.Form)
                HttpRuntime.Cache.Add("FormStateSaver_" + id, result, Nothing, Cache.NoAbsoluteExpiration, StateCacheDuration, CacheItemPriority.Normal, _
                Nothing)
            End If
            Return result
        End Function
 
        ''' <summary>
        ''' Loads the Context stored in Cache using the Unique ID
        ''' </summary>
        ''' <param name="id">Unique GUID</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function Load(ByVal id As String) As FormState
            Dim result As FormState = Nothing
            If Not String.IsNullOrEmpty(id) Then
                result = TryCast(HttpRuntime.Cache("FormStateSaver_" + id), FormState)
            End If
 
            Return result
        End Function
 
        ''' <summary>
        ''' Deletes the stored Context from Cache
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub Delete()
            HttpRuntime.Cache.Remove("FormStateSaver_" + _id)
        End Sub
    End Class
 
    ''' <summary>
    ''' This class is responsible for generating the Transit page on the fly. 
    ''' Basically a page that loads all the values then submits them back to the destination
    ''' page with the previous viewstate settings so that the form reloads the controls to where
    ''' they were prior.
    ''' 
    ''' This page quickly shows when the user is logging back in and loading a previous web form.
    ''' 
    ''' </summary>
    ''' <remarks></remarks>
    Protected Class FormStateSaverHandler
        Implements IHttpHandler
 
        Private ReadOnly _state As FormState
 
        Public Sub New(ByVal state As FormState)
            _state = state
        End Sub
 
        Protected ReadOnly Property FormState() As FormState
            Get
                Return _state
            End Get
        End Property
 
        Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property
 
        Public Overridable Sub ProcessRequest(ByVal context As HttpContext) Implements System.Web.IHttpHandler.ProcessRequest
            Try
                Using writer As HtmlTextWriter = CreateHtmlTextWriter(context.Response.Output, context.Request.Browser)
                    Me.Render(writer)
                End Using
            Finally
                _state.Delete()
            End Try
        End Sub
 
        Protected Overridable Sub Render(ByVal writer As HtmlTextWriter)
            writer.RenderBeginTag(HtmlTextWriterTag.Html)
            writer.RenderBeginTag(HtmlTextWriterTag.Head)
            writer.RenderBeginTag(HtmlTextWriterTag.Title)
            writer.Write("Restoring form")
            writer.RenderEndTag()
            ' TITLE 
            writer.RenderEndTag()
            ' HEAD 
            writer.AddAttribute("onload", "document.forms[0].submit();")
            writer.RenderBeginTag(HtmlTextWriterTag.Body)
 
            writer.AddAttribute("method", "post")
            writer.RenderBeginTag(HtmlTextWriterTag.Form)
 
            Dim form As NameValueCollection = Me.FormState.Form
            For Each name As String In form.Keys
                RenderHiddenField(writer, name, form(name))
            Next
 
            'writer.AddAttribute(HtmlTextWriterAttribute.Align, "center")
            'writer.RenderBeginTag(HtmlTextWriterTag.P)
            'writer.Write("You should be redirected in a moment.")
            'writer.WriteFullBeginTag("br")
            'writer.Write("If nothing happens, please click ")
            'RenderSubmitButton(writer, "Submit")
            'writer.RenderEndTag()
            ' P 
            writer.RenderEndTag()
            ' FORM 
            writer.RenderEndTag()
            ' BODY 
            writer.RenderEndTag()
            ' HTML 
        End Sub
 
        Protected Shared Sub RenderHiddenField(ByVal writer As HtmlTextWriter, ByVal name As String, ByVal value As String)
            writer.AddAttribute(HtmlTextWriterAttribute.Type, "hidden")
            writer.AddAttribute(HtmlTextWriterAttribute.Name, name)
            writer.AddAttribute(HtmlTextWriterAttribute.Value, value)
            writer.RenderBeginTag(HtmlTextWriterTag.Input)
            writer.RenderEndTag()
            ' INPUT 
        End Sub
 
        Protected Shared Sub RenderSubmitButton(ByVal writer As HtmlTextWriter, ByVal text As String)
            writer.AddAttribute(HtmlTextWriterAttribute.Type, "submit")
            writer.AddAttribute(HtmlTextWriterAttribute.Value, text)
            writer.RenderBeginTag(HtmlTextWriterTag.Input)
            writer.RenderEndTag()
            ' INPUT 
        End Sub
 
        Protected Shared Function CreateHtmlTextWriter(ByVal writer As TextWriter, ByVal browser As HttpCapabilitiesBase) As HtmlTextWriter
            If browser Is Nothing Then
                Return New HtmlTextWriter(writer)
            End If
            Return browser.CreateHtmlTextWriter(writer)
        End Function
 
 
    End Class
 
 
End Class
 
 
 
 
 

.Net Framework, ASP.NET, Programming, VB.NET , , , , , , , ,

Embedding Server Side Values Sent to Client

20. February 2008

Something I want to try, that somebody sent me.

Will post more later.

protected void Page_Load(object sender, EventArgs e)
{
    wwScriptVariables scriptVars = new wwScriptVariables();
 
    // *** Add any values static or dynamic
    scriptVars.Add("name", "West Wind Technologies");
    scriptVars.Add("entered", DateTime.Now);
    scriptVars.Add("counter",12.22M);
 
    // *** A cleaner way to get ClientIDs into client code?
    scriptVars.Add("txtNameId", txtName.ClientID);
    scriptVars.Add("btnSubmitId", btnSubmit.ClientID);
 
    // *** Add a control's value
    scriptVars.AddDynamicValue("txtName", this.txtName, "Text");
 
    // *** Done
}
 
The class then renders a JavaScript object into the page with each of the key values as property names. 
<script type="text/javascript">
//<![CDATA[
var serverVars = {
    "name": "West Wind Technologies",
    "entered": new Date(1202809550345),
    "counter": 12.22,
    "txtNameId": "txtName",
    "btnSubmitId": "btnSubmit",
    "txtName": ""
}
//]]>
</script>
 
Now from anywhere you can access these variables via the static object instance:
var name = serverVars.name;
var entered = serverVars.entered;
var counter = serverVars.counter;
 
var txtName = document.getElementById(serverVars.txtNameId);

Code Snippets, JavaScript, Programming, VB.NET , , ,

The Blank Keyboard - Das Keyboard II

12. February 2008

For those of you who know me, I have this black natural keyboard I use where I've scratched off the labels on my keys. Well, I don't have to sratch any longer. Someone's made a label-less keyboard.  I don't know why I like it like that, helps me learn the keyboard even faster and I find it amusing when people come over and try and use my keyboard.

 

The Das Keyboard II for $80 USD.

http://www.daskeyboard.com/

Shopping, Gadgets & Cool Stuff , ,

Flash Player 9.0.115.0 on Vista Does Not Display Content

8. February 2008

You may have noticed, if you're running Vista, that with the latest update to Flash Player 9.0.115.0, you no longer are seeing pages with Flash Content. You'll go to YouTube or GrandCentral.Com and notice that it tells you that you need the latest version of Flash.  You'll install it for the 50th time and still doesn't work.

Solution? You need an update to your Flash Player for Vista, which you can find located here, after you've installed the Flash Plug-in/Player.

Here are the steps I finally found from the Adobe site.

    Windows Vista installation failure

    On some Windows Vista systems Flash Player may be installed, but will not display Flash content. In some cases updating to the latest Flash Player using the included utility can correct the User Account Control settings that cause this problem.

    To run the utility, find your installed Flash folder:

  • Right-click C:\Windows\System32\Macromed\Flash\FlashUtil9d.exe

    The letter changes with each Flash version, so may be "FlashUtilb.exe" or "FlashUtile.exe"

  • Select "Run as Administrator"

  • Follow the steps to complete the Flash Player installation

  • Restart your computer

 

Now, this only worked on my Vista 32bt OS, doesn't work so good on my 64bit.  Upon running the utility it doesn't seem to complete. So I took they Flash folder from my 32bit and over wrote the Flash folder on my 64bit. Located at C:\Windows\SysWOW64\Macromed\Flash.

Seemed to fixed the problem after a reboot.

Operating Systems, Troubleshooting , , , , ,

MCSD and MCAD Outdated? Is MCPD Widely Recognized?

7. February 2008

So I was tracing out my Certification Path from Microsoft to get my MCSD.  MCSD the mother ship of programming cert's, equivalent to MCSE right? Well that's what I thought.  However, as I started to dig down to what test were needed and the study material, I realized that it only covers .Net 1.0 and 1.1.  Hmm, strange, and MCAD? Same thing, .NET 1.0 and 1.1.  With .NET 2.0 out, that makes those cert's more outdated, but tell someone you are an MCSD and they recognize that certification as top of the line development. 

So then what? Well as I traced the paths of other cert's I realized that the new version is called MCPD - Enterprise Applications.  You actually need 5 test to get this certification which is the new top of the line in my eyes.  However, if you simply take 3 test you can get MCPD - Web Developer.  Notice the cert has the same name. Both are MCPD. So telling someone your an MCPD isn't enough.  There are three levels of MCPD. Web, Windows and Enterprise.  Each of these require a pre-req Cert called MCTS in Web, Windows or Distributed.

 

Anyway, you could still get your MCSD as that seems to be more widely known then MCPD.  Never even heard of it until I checked the site. So here's the tracks to take. 

 

  MS .NET Framework 2.0 - Application Development Foundation
Exam 70-536
 
MS .NET Framework 2.0 Web-Based Client Development
Exam 70-528
MS .NET Framework 2.0 Windows-Based Client Development
Exam 70-526
MS .NET Framework 2.0 Distributed Application Development
Exam 70-529
MCTS - Web Client Development Certification MCTS - Windows Client Development Certification MCTS - Distributed Application Development Certification
Designing and Developing Web-Based Applications Using MS .Net Framework Designing and Developing Windows-Based Applications Using MS .Net Framework Designing and Developing Enterprise Applications Using MS .Net Framework
MCPD - Web Application Certification MCPD - Windows Application Certification MCPD - Enterprise Application* Certification
*Note
- You can't get this certification until you've taken 70-528, 70-526, 70-529

 

After all this you could try for an MCA that's really the new mac-daddy certification.

 

Programming, Training & Learning, Microsoft , , ,

Consolidate Excel Sheets to one Sheet using VBA

4. February 2008

Been awhile since I used Visual Basic for Applications. I mean it's like VBScript on steroids.  So many things I'm used to now in Visual Studio 2008 that I take for granted.  Simple things, like collapsing my subs and functions. Or not getting a compile error just because I click off somewhere to copy a variable name.

Anyway, I had a friend of mine, ask me how to consolidate sheets in excel. At first I said, oh that's easy, however the more I looked into it, consolidating numbers works fine, but when it comes to text, not so much.

The idea was this....

Sheet1 is the "Master" sheet, which will hold all the information from the other Sheets.

Sheet2 looks sort of of like this...

Name Address City State Zip
Bob Joe 1234 Kingly Tampa FL 33647
Jane Doe 352 Thisway Clearwater FL 33607
Billy Bob 232 Highway Atlanta GA 33450

Sheet3 looks like this....

Name Address City State Zip
Fred Mastro 13324 Lane Honolulu HI 45612
Brain Chris 845 Thatway Miami FL 12345
Dan April 232 ByWay Washington DC 78464
Jane See 3222 Flower Howell NJ 99706

The idea was to combine Sheet2, and Sheet3 into Sheet1 so that Sheet1 is the combined rows of both sheets. Then later be able to add say Sheet4 with more data and show it as well on Sheet1. 

Sheet1 would look like this basically.....

Name Address City State Zip
Bob Joe 1234 Kingly Tampa FL 33647
Jane Doe 352 Thisway Clearwater FL 33607
Billy Bob 232 Highway Atlanta GA 33450
Fred Mastro 13324 Lane Honolulu HI 45612
Brain Chris 845 Thatway Miami FL 12345
Dan April 232 ByWay Washington DC 78464
Jane See 3222 Flower Howell NJ 99706

Easy way of doing this? At first glance you would suspect yes. Maybe I do not know all the Excel functions possible, I'm sure an Excel Guru maybe could. Upon searching the Internet I found many "add-ins" created by people to perform this function.

So I thought I'd save someone the trouble, here's a macro/vba code you could use in your excel sheet.  Most common problem when using this? Not having Macro's Enabled. Please make sure Macros are enabled for your workbook, and close and re-open excel after making the needed changes.

So in excel you'd have to find your VBA code. I'm using Excel 2007 so you have to show the developer tab first. Use help to find the Developer tab or View Code.

The event I used was Sheet1  Worksheet_Activate.  So on activate of Sheet1 the code runs.

Private Sub Worksheet_Activate()

End Sub

Then you can paste this function, this is the heart of it.  There a better way to write this? I'm sure there is, as I never work in creating VBA macros for Excel, this was my first run through. The last time I did VBA was with Access about 10 years ago.

 
Sub ConsolidateRows(ByVal SourceSheet As Worksheet, ByVal DestinationSheet As Worksheet, ByVal TotalColumns As Integer, Optional SkipFirstRow As Boolean = False)
    Dim SourceSheetRowCount As Integer
    Dim DestinationSheetRowCount As Integer
    Dim FoundDataInColumns As Boolean
    SourceSheetRowCount = 1
    If SkipFirstRow = True Then SourceSheetRowCount = 2
    DestinationSheetRowCount = 1
    FoundDataInColumns = False
    
    'Loop Through Every Source Sheet Row
    Do Until SourceSheetRowCount = SourceSheet.Rows.Count
        'Check Each Row for Data In First Cell
        If SourceSheet.Cells(SourceSheetRowCount, 1) = "" Then
            'If No Data In First Cell, Lets Check Rest of Cells In This Row
            For C = 2 To TotalColumns
                If SourceSheet.Cells(SourceSheetRowCount, C) <> "" Then
                    'Data Was Found In A Cell
                    FoundDataInColumns = True
                Else
                    FoundDataInColumns = False
                End If
                If FoundDataInColumns = True Then Exit For 'Data Found so Exit For, No Need To Check Rest of Cells
            Next
            If FoundDataInColumns = False Then Exit Do ' Data Was Not Found In Any of the Cells, so Exit Loop Completely, This Row is Empty
        Else
            FoundDataInColumns = True
        End If
        'If data was found, lets move on
        If FoundDataInColumns = True Then
            'Lets skip through rows that already have data on the DestinationSheet
            Do Until DestinationSheet.Cells(DestinationSheetRowCount, 1) = ""
                DestinationSheetRowCount = DestinationSheetRowCount + 1
            Loop
                'Now lets loop through the cells for this row and copy the data over
                For C = 1 To TotalColumns
                    DestinationSheet.Cells(DestinationSheetRowCount, C) = SourceSheet.Cells(SourceSheetRowCount, C)
                    'Could copy formatting here
                Next
            DestinationSheetRowCount = DestinationSheetRowCount + 1
        End If
        SourceSheetRowCount = SourceSheetRowCount + 1
        FoundDataInColumns = False
    Loop
End Sub

Now you could call this directly for each sheet you wanted to import into the master...

Call ConsolidateRows(Sheet2, Sheet1, 14)
Call ConsolidateRows(Sheet3, Sheet1, 14)

So after this, my friend was adding sheets, so I said, let me write a quick function to do all the sheets for you. So this function just loops through all the sheets and adds them to the destination one. Nothing fancy.

Sub ConsolidateAllSheetsTo(ByVal DestinationSheetName As String, ByVal DestinationSheet As Worksheet, ByVal TotalColumns As Integer)
    Dim Sheet As Worksheet
    Dim I As Integer
    I = 1
    For Each Sheet In ThisWorkbook.Worksheets
        If Sheet.Name <> DestinationSheetName Then
            If I = 1 Then
                Call ConsolidateRows(Sheet, DestinationSheet, TotalColumns)
            Else
                Call ConsolidateRows(Sheet, DestinationSheet, TotalColumns, True)
            End If
             I = I + 1
        End If
    Next
End Sub

And so then you can stop calling the ConsolidateRows directly and instead call ColsolidateAllSheetsTo instead.

Call ConsolidateAllSheetsTo("Master", Sheet1, 14)

Yes, yes I know "Call" is old school and you don't need it.  I just use it because it makes for faster skimming of code when I'm trying to debug.

So in the end, your activate event might look something like this...

Private Sub Worksheet_Activate()
Sheet1.Cells.Delete
Call ConsolidateAllSheetsTo("Master", Sheet1, 14)
End Sub

Complete Code from Top to Bottom:

   1: Private Sub Worksheet_Activate()
   2:     Sheet1.Cells.Delete
   3:     Call ConsolidateAllSheetsTo("Master", Sheet1, 14)
   4: End Sub
   5:  
   6: Sub ConsolidateAllSheetsTo(ByVal DestinationSheetName As String, ByVal DestinationSheet As Worksheet, ByVal TotalColumns As Integer)
   7:     Dim Sheet As Worksheet
   8:     Dim I As Integer
   9:     I = 1
  10:     For Each Sheet In ThisWorkbook.Worksheets
  11:         If Sheet.Name <> DestinationSheetName Then
  12:             If I = 1 Then
  13:                 Call ConsolidateRows(Sheet, DestinationSheet, TotalColumns)
  14:             Else
  15:                 Call ConsolidateRows(Sheet, DestinationSheet, TotalColumns, True)
  16:             End If
  17:              I = I + 1
  18:         End If
  19:     Next
  20: End Sub
  21:  
  22:  
  23: Sub ConsolidateRows(ByVal SourceSheet As Worksheet, ByVal DestinationSheet As Worksheet, ByVal TotalColumns As Integer, Optional SkipFirstRow As Boolean = False)
  24:     Dim SourceSheetRowCount As Integer
  25:     Dim DestinationSheetRowCount As Integer
  26:     Dim FoundDataInColumns As Boolean
  27:     SourceSheetRowCount = 1
  28:     If SkipFirstRow = True Then SourceSheetRowCount = 2
  29:     DestinationSheetRowCount = 1
  30:     FoundDataInColumns = False
  31:     
  32:     'Loop Through Every Source Sheet Row
  33:     Do Until SourceSheetRowCount = SourceSheet.Rows.Count
  34:         'Check Each Row for Data In First Cell
  35:         If SourceSheet.Cells(SourceSheetRowCount, 1) = "" Then
  36:             'If No Data In First Cell, Lets Check Rest of Cells In This Row
  37:             For C = 2 To TotalColumns
  38:                 If SourceSheet.Cells(SourceSheetRowCount, C) <> "" Then
  39:                     'Data Was Found In A Cell
  40:                     FoundDataInColumns = True
  41:                 Else
  42:                     FoundDataInColumns = False
  43:                 End If
  44:                 If FoundDataInColumns = True Then Exit For 'Data Found so Exit For, No Need To Check Rest of Cells
  45:             Next
  46:             If FoundDataInColumns = False Then Exit Do ' Data Was Not Found In Any of the Cells, so Exit Loop Completely, This Row is Empty
  47:         Else
  48:             FoundDataInColumns = True
  49:         End If
  50:         'If data was found, lets move on
  51:         If FoundDataInColumns = True Then
  52:             'Lets skip through rows that already have data on the DestinationSheet
  53:             Do Until DestinationSheet.Cells(DestinationSheetRowCount, 1) = ""
  54:                 DestinationSheetRowCount = DestinationSheetRowCount + 1
  55:             Loop
  56:                 'Now lets loop through the cells for this row and copy the data over
  57:                 For C = 1 To TotalColumns
  58:                     DestinationSheet.Cells(DestinationSheetRowCount, C) = SourceSheet.Cells(SourceSheetRowCount, C)
  59:                     'Could copy formatting here
  60:                 Next
  61:             DestinationSheetRowCount = DestinationSheetRowCount + 1
  62:         End If
  63:         SourceSheetRowCount = SourceSheetRowCount + 1
  64:         FoundDataInColumns = False
  65:     Loop
  66: End Sub

So there you have it, my quickly rigged consolidate function. Does it have bugs? Of course! It was free and quick.

Wexelblat's Scheduling Algorithm:

  •  
  • Choose Two:

  • Good

  • Fast

  • Cheap

So you can see by this.. it was Fast and Free, so = No Good. haha.

Known Issue, I don't think these are bug

FredMastro.Com Tags:

s, just things I didn't code for.

-Your Source Sheets can not have blank rows between data. Code will stop at first blank row and go to next sheet
-Your source sheets have to have data in the first column, otherwise it gets overwritten on the master

That's all I know of so far.

Good luck, hope this helps someone out there, 5 years from now.

Code Snippets, Programming, VBA , , , , , ,

New Post using Windows Live Writer

2. February 2008

So I am now using Windows Live Writer for my blog posts. This, I think, is a must have for blogging. It can connect to different blog systems like Community Server, which is what I use, and you get the benefit of using a client base application like word, for posting up your posts.  Now I can spell check! Hooray!  Many cool add-ons you can install with it, like code snippets, insert videos, Amazon links etc..

Anyway, just wanted to write my first post using Live Writer.

Update: I've since moved off of Community Server, which was just a bit to slow for me, and have now moved on to BlogEngine.Net. It's much better and I can still use Windows Live Writer!

Gadgets & Cool Stuff, Developer Tools , , ,