Leave a comment

A Message to my Loyal Readers

I would like to thank you for more than six (6) years of reading my posts. I have been using a different platform lately and writing on a number of different subjects – I would love to have you stop by and check it out.


Leave a comment

Word: Collect document information with VBA

microsoft-clip-art-computerI recently had a project containing a large number of Word documents of various sizes and the Project Manager wanted to know if the documents were “Letter size” or “Legal size” and whether they were “Portrait” or “Landscape”. I had a couple options – I could either open each of the documents and write down the information or I could write some VBA code to do it for me. I decided to write the code.

The code below will get all of the “docx” file names in the specified directory. It will then open each file, get the document name, document width and document height and store this information in variables before closing the document. To minimize distractions and speed up execution I turned off “Alerts” and “Screen updating” before doing anything else and the last thing I did was to turn them back on. I wanted to make a clean, easily readable document so I used tabs (vbTab) and set the tabs in the document to be right-aligned. I knew that I was dealing with only two (2) paper sizes – either 8 1/2 x 11 (Letter) or 8 1/2 x 14 (Legal) – so it simplified the code considerably. If there is sufficient interest in adding other sizes I will work out the code and write a follow-up – let me know.

The first thing I do is to have the code navigate to the desired folder using ChangeFileOpenDirectory and supply it with the variable holding the path. The next step is to get a list of files in the directory and assign them to a variable, docNamedocName = Dir("*.docx"). Using a “Do While….” loop I go through the directory list, opening each document as I go. Once I get the information I need I close the document. You will notice I am using a function called “PointsToInches” – that is a built-in Word function that I chose to use but feel free to leave it out. If you don’t use it you will get the width and height in “points” and you will have to adjust your numbers (there are 72 points to 1 inch.)

Once I have the name, height and width I compare the width and height – if the document is wider than high I know it is “Landscape”, otherwise it is “Portrait”. If the document is “Landscape” I evaluate the width to determine if the document size is “Letter” or “Legal”. I do the same with a “Portrait” document except I evaluate height to determine the page size. The last thing I do is to write the information to my master document using Selection.TypeText docName & ": " & vbTab & docOrient & vbTab & docSize & vbCrLf. I then move on to the next document by executing docName = Dir. If you forget this line of code you will get stuck in an infinite loop!

Public Sub getPageSize()
    Dim docName As String, docPath As String
    Dim docOrient As String, docSize As String
    Dim intWidth As Single, intHeight As Single
    Application.DisplayAlerts = wdAlertsNone
    Application.ScreenUpdating = False
    ChangeFileOpenDirectory docPath
    docName = Dir("*.docx")
    Do While docName <> vbNullString
        Documents.Open (docName)
        intWidth = PointsToInches(Selection.PageSetup.PageWidth)
        intHeight = PointsToInches(Selection.PageSetup.PageHeight)
        If intWidth > intHeight Then
            docOrient = "Landscape"
            If intWidth = 14 Then
                docSize = "Legal"
                docSize = "Letter"
            End If
            docOrient = "Portrait"
            If intHeight = 14 Then
                docSize = "Legal"
                docSize = "Letter"
            End If
        End If
        Selection.TypeText docName & ": " & vbTab & docOrient & vbTab & docSize & vbCrLf
        docName = Dir
    Application.ScreenUpdating = True
    Application.DisplayAlerts = wdAlertsAll
End Sub

That’s all you need! If you add this code to your Normal.dotx template it will always be available to you. To run the code you simply open a New document, update the path in the code and run from within the new document. It doesn’t get much easier. There are a lot more that can be done with this code, such as using a dialog box to get the path instead of hard-coding, adding more paper sizes (as mentioned above) and more statistics about the document like page count, paragraph count, word count, etc….
If you would like to know more about this project or have one you would like to discuss, please write to me at joevalencia32@gmail.com.

Leave a comment

Word: Insert Fields with VBA

microsoft-clip-art-computerI have written quite a bit about SQL, C#, Visual Basic and Excel but until now I haven’t covered other Microsoft products. Today I am writing about some Microsoft Word macros I wrote recently that ended up being huge time-savers.

I needed to create a couple mail-merge documents using Microsoft Word 2013, each with more than 100 fields; some of which were in tables of various sizes. I started out doing it the way we were all taught: Insert –> Quick Parts –> Field –> MergeField. This got old awfully quick and I couldn’t envision doing this more than 100 times so I wrote a macro. Who wouldn’t? The macro only took a few minutes and a couple lines of code, see the snippet below:
Sub InsertCustomField()
    Dim sFieldName As String
    sFieldName = InputBox("Please enter field name")
    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= "MERGEFIELD " & sFieldName, PreserveFormatting:=True
End Sub

I assigned the macro to the “Insert” key and went about my work. When I got to the first table I needed to insert I decided to write another macro, as follows:
Sub InsertTable_w_Header()
    Dim numRows As Integer, numCols As Integer, numNewTable As Integer
    Dim sCaption As String
    numRows = InputBox("How many rows?", "Rows", 3)
    numCols = InputBox("How many columns?", "Columns", 4)
    sCaption = InputBox("What is the 'caption' for this table?", "Caption")
    ActiveDocument.Tables.Add Range:=Selection.Range, numRows:=numRows, NumColumns:=numCols, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitFixed
    numNewTable = ActiveDocument.Tables.Count
    With ActiveDocument.Tables(numNewTable)
        If .Style "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    Selection.MoveRight Unit:=wdCharacter, Count:=numCols, Extend:=wdExtend
    Selection.Font.Bold = wdToggle
    Selection.TypeText Text:=sCaption
    Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    Selection.MoveDown Unit:=wdLine, Count:=1
    Selection.MoveRight Unit:=wdCharacter, Count:=numCols, Extend:=wdExtend
    Selection.Font.Bold = wdToggle
    Selection.Shading.Texture = wdTextureNone
    Selection.Shading.ForegroundPatternColor = wdColorAutomatic
    Selection.Shading.BackgroundPatternColor = -603930625
    With ActiveDocument.Tables(numNewTable)
        .TopPadding = InchesToPoints(0.02)
        .BottomPadding = InchesToPoints(0.02)
        .LeftPadding = InchesToPoints(0.08)
        .RightPadding = InchesToPoints(0.08)
        .Spacing = 0
        .AllowPageBreaks = True
        .AllowAutoFit = False
    End With
End Sub

The last macro I needed to write for this project was for inserting a checkbox. This macro is a little more involved than the “insert field” but not as much as “insert table”. Check it out:
Sub ApplicationInsertCheckbox()
    Dim sFieldName As String
    sFieldName = InputBox("Please enter field name")
    Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormCheckBox
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    With Selection.FormFields(1)
        If Len(sFieldName) >= 1 Then
            .Name = sFieldName
        End If
        .Enabled = True
        .OwnHelp = False
        .HelpText = ""
        .OwnStatus = False
        .StatusText = ""
        With .CheckBox
            .AutoSize = False
            .Size = 8
            .Default = False
        End With
    End With
    Selection.MoveRight Unit:=wdCharacter, Count:=1
End Sub

That’s it! The above code cut my development time by more than 50% and ensured consistency throughout the document. I put the code in a module that I named “mFieldAutomation” and saved my document as a TEMPLATE. I now had the basic layout for most of the documents I will have to create and the code to make it a much more efficient task. I also created another module named “mDocumentDoc” which contains a couple macros that create lists of fields and tables in a document. I will write about those macros in an upcoming post.

If you would like to know more about this project or have one you would like to discuss, please write to me at joevalencia32@gmail.com.

Leave a comment

A Year in Review: 2013

The year 2013 saw The Happy Coder publish 15 posts after taking some time off. The blog was somewhat re-tooled from something that was strictly code-based, this is how you do it, to something that is designed to give insight to the world of programming (at least MY part of it) and maybe give you a few ideas for projects. I have taken you into how I think and what I feel the role of the programmer is, as well as this is how you do it.

I have done a few multi-part posts about certain projects – Invoice Approval System – Part I, Invoice Approval System – Part II: The Web and Invoice Approval System – Part III: Multiple Approvers being the longest and most popular – but most consisted of a single post. I tried to let you in on my thought process (Creativity and Proactive Customer Service) and hopefully inspired you to look at projects in a different light. I think it is important to set aside personal feelings and ego in order to satisfy the customer. It is equally important to make sure the customer gets what they need even if it isn’t necessarily what they think they want. The hard part is to do this and still make them happy.

I intend to bring more projects and instruction your way in the upcoming year. I hope you find them, not only informational and inspirational, but also entertaining.

Thank you for a great 2013 and I look forward to a great 2014!

Leave a comment


Http://Welcome back! In the first part of this series I covered my initial involvement in a client’s website development; it was strictly HTML and a little CSS. Then the “Contact Us” page was written in PHP, followed by a PHP product specification sheet that got it’s data from a PostgreSQL database. The final step in the evolution was a total migration to ASP.NET and SQL Server. I had just finished the main product pages, database tables and stored procedures when I ended Part I. You can refresh your memory (or read it for the first time) here – HTML to PHP to ASP.NET: Part I. In Part II I will cover the “Contact Us”, “Careers” and “Login” pages along with pages that handle site and data administration.

Let’s start with the all-important “Contact Us” page. As I mentioned earlier, this page was written in PHP and later had CAPTCHA added. When a potential customer submitted the request they received a confirmation email and an email was sent to the client’s sales department for follow-up. The system worked well but there was no record other than an email and there was nowhere for the sales department to keep notes about the request. I built a table in the database and added code to write the data to the table prior to sending the email. There was then a page created that queried this data and wrote it to the screen. When I moved to ASP.NET I also added the ability to send email to the requestor directly from the web page and for the person sending the email to add comments to the database. They now had somewhere they could go to and see what people are inquiring about and maintain records of follow-up correspondence. This data could ultimately be tied into a customer database or order database with relative ease.

At this point I have completely migrated the existing website over to ASP.NET and SQL Server and it is time to add the back-end administrative functionality. One of the prime reasons for the site migration was to give the client the ability to maintain the data and add products without having to come to me to update the HTML code. With that goal in mind I set out to create the “admin” pages. The first consideration was security; we didn’t want just anyone to be able to change the data. The easiest form of security, and also the least secure, is to put up a page that doesn’t have a link to it anywhere. The thought is that you can’t get there if you don’t know where “there” is or that it even exists. This isn’t a very good choice. The site already had a “Login” page that contained links to internal sites that required user authentication so I decided to use that page and add a couple textboxes for the user to input a loginID and password. These would be the same as their email id and password; you’ll learn why later. I wanted to give the client the ability to restrict functionality to users based upon “access levels” and started with four basic levels – “sales”, “hr”, “admin” and “executive”. Sales would have access restricted to the contact request functions and hr would be restricted to the “Careers” page. Admin level would give access to the product data tables and executive would grant access to everything. I had a table, tLogin, that stored the userID, password, access level, date created and date deleted. If date deleted is NULL, then the access rights were active, otherwise the user either had their access rights changed or they no longer had rights. The reason I wanted to use the email password for the user is that this then allowed me to use their email account for sending the correspondence to the custom in response to a contact request.

The “Careers” page is something that I am sure everyone reading this post has seen; it is a page for advertising job openings within the company. I decided to have this page work for both internal and external job postings. If the user had a login id and password, they were allowed to view the internal postings otherwise they got only the external posts. There was a link to send and email to the person posting the job. I created everything with the intent of having the ability to track applicants and store resumes but that is beyond the scope of this project. I do envision adding this functionality in the future.

That is the project, in a nutshell. I hope you enjoyed reading about it and maybe getting some ideas for your next website.

If you would like to know more about this project or have one you would like to discuss, please write to me at joevalencia32@gmail.com.

1 Comment

HTML to PHP to ASP.NET: Part I

Http://Since 2008 I have been doing miscellaneous work for a client on an as-needed basis. My first job for him was a simple spreadsheet fix and it grew into other projects, the biggest of which was the company website. The site was originally just a plain white background with the company logo and a little background information along with contact information. I expanded the site to include pages for the individual product lines and added graphics. They wanted to make it more attractive but their design needs were beyond my expertise so they hired a company to build it, under my guidance and specifications. The resulting site is quite attractive but a little cumbersome to work with because the layout is based on tables and the menus are placed on each page making changes far more labor intensive than necessary. The host is a LINUX server and the site was purely HTML and CSS with no server-side coding at all.

In 2010 they wanted to add a feature that allows the user to click on an icon on a product page and have a new page open with a full specification sheet that they could then print. The sheet was to include a photo of the product, description and full specifications along with the companies contact information and logo. There were a couple ways to attack this request – one way would be to create a specification page for each of the items and hard-code a link which would have made a lot of money for me or we could do it the smart way and create one page using PHP and bind it to a database on the backend. This page would be built dynamically based upon the item the user clicked on. I suggested the latter and the client agreed so I went about learning PHP. I then discovered that the database available to me was PostgreSQL which I had never worked with. I was expecting MySQL, a database that I was familiar with. It didn’t take long for me to learn the necessary technologies, build the database and write the code; within a week of the initial request I had the work done. The company has added products since then and the update couldn’t be easier – or could it?

That brings me to the next step in the evolution of the site – ASP.NET. I have been wanting to bring the site over to .NET and SQL Server for a while but the clients host didn’t offer the service with the package they had and the advantages weren’t enough to warrant a move. It wasn’t until I added the ability to save customer contact requests to the database that we finally reached a point where the change was worthwhile. I added a couple tables to the database to store the contact request and comments from the sales department and created a PHP admin page where they could view all the requests and send e-mail to the customer. It is functional but it isn’t what I wanted to do. I spoke with my contact at the client, outlined my vision for what the site could be like and got the green light to migrate to .NET and SQL Server. They found a new host and had the existing site moved, all I had to do was build the database on the new server, add the data and update the connection strings. The host was capable of running both PHP and ASP.NET. The goal was to re-build the site so that it was completely data-driven and new items could be added simply by adding the information to the database. This means that when they add an 11×14 inch spiral bound notebook to the rest of the spiral bound notebooks (note: the client is not a stationary supply company, this product is for illustration purposes only) they only need to add one record to the database and the site will update automatically. If they want to add a completely new product line, I would still need to do some HTML work on the front-end. The new design would also allow them to sort the data on the contact request admin page, view all of the comments entered by the sales department and perform other routine maintenance of the data.

My first step, after deciding on C# as my development language, was to create a “master page” which will have all of the header, navigation and footer information on it. This will dramatically reduce the time required to add/remove a page, re-arrange the menu and update the copyright information. I now have one place to keep this information instead of more than a dozen. Once I had the master page it was just a matter of creating the home page, individual product pages, information request and administrative pages. I started by creating a template for the product pages to make the process more efficient. I was able to copy most of the content and much of the structure for each product and paste it into the new template with only minor adjustments. What I didn’t have to do was include the product specification tables under each product, those were now driven by the database. I simply had to set up an empty <asp:Table>, give it a name and it was done. I created a stored procedure to retrieve the product data from the database and a class module to handle the data. Each product line is different and not all of the fields in the database table with have data so the code looks for fields in the returned recordset and only puts that data in the product table to present to the user. This allows me to use a single stored procedure and the same code for every product, current and future. By using a stored procedure to query the database I can make changes without having to update the source code in the pages. I have found that it is (almost) always best to run the queries from stored procedures within the database rather than in your source code. There are exceptions, of course, but it is a good rule to live by.

I think this is a good place to stop. In Part II I will cover the “Contact Us” page along with pages that handle site and data administration.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

Leave a comment

Customer Call Listening Program

customer_support_1In my last post (Employee Recognition Program) I wrote about an application I built for the Corporate Communications Department. This article is about another job I did for them, this one was for a project requested by our CEO.

At one time, new employees in certain departments were required to spend time with customer support representatives and listen to calls. A company’s success depends upon happy customers and by hearing directly from the customer we were better prepared to give them a better experience. I don’t know why but the program was stopped for new hires shortly after I started with the company. Now he wanted the program restored, but with a twist. Everyone in the company had to spend time monitoring calls at least once per quarter. My job was to create a web-based scheduling application. I was told that the program had to be “live” in three months – “Can you do it?” “Sure!” I only had two other projects that I was working on and one of them had the same deadline as this one. Why would that be a problem? One of the projects was the second generation of the Invoice Distribution program and since I already had something in place I could put that on a side burner. The other application was for Human Resources.

You have heard the phrase “the devil is in the details”; little did I know the devil in this case was 8 feet tall and had just sharpened his pitchfork! It wasn’t as simple as letting someone sign up for a day and be done. The user had to select 3 sessions of 4 hours but there were a limited number of seats available each day and the program only ran 3 days a week. I needed to track attendance, generate reports and give the participants a section where they could record their observances and suggest improvements. The project also required two (2) admin screens – one for the Customer Support and one for HR. (I know I said this project was for Corporate Communications and it was. They requested the project and provided all of the specifications – the HR department was charged with administering the program and tracking compliance.)

My first task was to build the front-end for the user to request their sessions. The obvious choice here was to use the ASP.NET Calendar control. If the obvious choice was the best choice, I wouldn’t be writing this article…. The problem with the calendar control is that I couldn’t customize it to make it what I wanted. I needed to be able to show which days were available but I also wanted to use color-coding to indicate if the spots were filling up. I decided to use buttons and show 3 months on the screen at a time. The user would select the quarter they wanted to register for and the calendars would be generated and color-coded. I wanted each calendar to actually look like a calendar so I needed to calculate the day of the week the month started and fill in the rest behind it. I needed to have 42 buttons for each month, the button would have a number in it (1 thru 28, 29, 30 or 31) and a color representing it’s availability. I needed to loop through the buttons for each calendar and put the day on the button face and then color code the button. To do this I needed to know how many days were in the month. I could have had a lookup table but that would have been too much work, especially when you take into account leap year. The best answer was a function that returned the last day – that function is GetLastDayInMonth and is shown below.

Private Function GetLastDayInMonth(ByVal dDate As Date) As Date
	dDate = DateAdd(DateInterval.Month, 1, dDate)
	dDate = Convert.ToDateTime(Month(dDate).ToString() & "/" & "1/" & Year(dDate).ToString())
	dDate = DateAdd(DateInterval.Day, -1, dDate)
	Return dDate
End Function

I think I am going to leave it at that for now. This project is a bit too large to cover in one article if I am going to include code samples with it. Stay tuned for upcoming articles where I will cover the color-coding, administration functions and the user input.

If you would like to know more about this project or have one you would like to discuss, please write to me at joe@joevalencia.site90.com.

%d bloggers like this: