Follow Us

Mom, X-Ray, and Nutrigenomics

It has, once again, been a long time since I have posted anything.  It's time.  In fact, it's long overdue.

Bloggers have personal lives.  My personal life has included a change of employment, a couple of new business ventures, and most recently, the loss of a parent.  It's been a very bitter-sweet year for me.

Last things first - Mom.  I recently bid farewell to the rock of my life, the one person who has always been there to support me, cheer me, lecture me, help me, and above all, love me through all the ups and downs in my very up and down life. Her example of perseverence is one that I will never forget and hope to emulate in every aspect of my life.

Which takes us to new business ventures. Mom was never afraid to learn something new and when she decided to learning something, she never went half-way. With that example in the forefront of my mind, I am delving into a new realm of experience, namely X-Ray. RMF X-Ray Services in the San Antonio, TX area is building a growing and profitable business and RMF (initials of the owner) has invited me to become a part of his venture as a technician, web developer, database developer/administrator, and all-around assistant.  It gives me a chance to grow and learn in a new arena and to associate with one of my favorite people in the world.

I am also expanding my business acumen into the Direct Sales arena with the new and exciting science of Nutrigenomics. My mom was a FIRM believer in this awesome science and she used it to extend her life and improve her quality of life while she was with us.  She often said that she felt better in her 80's than she did in her 70's, largely due to this awesome science. She was honored in front of thousands in Orlando recently for her dedication to and love of LifeVantage.  I will be sharing things in this blog about LifeVantage and the incredible opportunity it presents for better health and freedom.

Yes, I will continue to share code snippets, too. I strongly believe in the community that is built by sharing knowledge with others.  It's primarily the way I learned, and I hope to continue to learn new and innovative ways to accomplish erstwhile mundane or time-consuming tasks.  It is one of the primary reasons for Ent Web Musings.

Until next time...

 

 

Format Excel Data Using Access VBA

This post takes us to the next phase in the Integrating with Excel from Access series – the FormatReportGeneric() function.

This relatively simple function does the following:

  • Selects the current used range in the active spreadsheet.
  • Creates a table from the selected range and names it according to the optional TableName argument.  Note that this pre-supposes that the data to format begins at cell A1.  A few extra lines can be added to account for table data in a location other than A1, but I’m keeping it simple in this post as it works fine as-is for most applications.
  • Formats the table to one of the built-in table styles.  I use TableStyleMedium4.  You can pick the style that best suits your needs, or add some code to make it an optional argument.
  • Sorts the table based on the optional argument SortField
  • Aligns some fields.  There are some fields that appear over and over again in a given application that need to be centered or maybe right-justified.   Obviously, you’ll want to include your own fields, perhaps even create an array of common fields and loop through them.
  • Converts date fields.  Along the same lines as alignment, date fields do not always translate well to Excel, especially when the data derives from a linked SQL Server database.  Dates are in a text format and must be converted.  NumberFormat = "mm/dd/yyyy" takes care of that.  
  • Converts numbers stored as text.  Numbers are often stored as text (especially when working with SQL Server or Oracle data sources) and have to be converted.  This is a two step process:
    • Change the number format to "General"
    • Rewrite the value into the cell.  This second step actually completes the conversion to a number.  
    • Auto-fit columns to the appropriate minimum width to accommodate the entries
  • Reduce some columns – This is also totally optional.  Sometimes there are columns (like Description) that have very long entries.  You may define those here and reduce them to a manageable 50 px width.
  • Unwrap the text in the entire table.  Again, this is optional.  It makes the table look uniform, and since we have already set the autofit, nothing gets cut off except maybe the longer fields like Description.

That’s it! We’ve got a nicely formatted Excel table that used to be just bland data as it was pasted in from Access.  Obviously, there is a LOT more we could do (like setting up headers and footers, etc.,) but this is a quick and dirty overview on how to get started.  

Here is the code:

Sub FormatReportGeneric(objXL As Excel.Application, Optional SortField As String = "", Optional TableName As String = "Table1")

    On Error GoTo errFormatReport

    With objXL

        ' Select the data range – we are pre-supposing that the active
        ' sheet only contains data that we want to convert to a table

        .ActiveSheet.UsedRange.Select

        ' If you want to start from a specified range instead of 
        ' getting the entire sheet, try the following code to select
        ' just the table data beginning with the active cell:

        '.Range(.Selection, .Selection.End(xlToRight)).Select
        '.Range(.Selection, .Selection.End(xlDown)).Select

        If .Selection.Rows.Count = 1048574 Then

            ' Be sure to trap for this error somewhere – it means
            ' there was nothing on the sheet to format.

            Err.Raise vbObjectError + 1001

        End If

        .ActiveSheet.ListObjects.Add(xlSrcRange, .Selection, , xlYes).Name _

            = TableName

        .ActiveSheet.ListObjects(TableName).TableStyle = "TableStyleMedium4"

        If SortField <> "" Then

            .ActiveSheet.ListObjects(TableName).Sort. _

                SortFields.Clear

            .ActiveSheet.ListObjects(TableName).Sort. _

                SortFields.Add Key:=.range(TableName & "[[#All],[" & SortField & "]]"), SortOn:= _

                xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        End If

        ' Format columns (centering, date/time)
        ' Some columns may exist, others not.  It's generic…

        On Error Resume Next

        .range(TableName & "[Qty]").HorizontalAlignment = xlCenter

        .range(TableName & "[Qty]").NumberFormat = "General"

        .range(TableName & "[Qty]").Value  = .range(TableName & "[Qty]").Value  

        .range(TableName & "[Quantity]").HorizontalAlignment = xlCenter

        .range(TableName & "[Quantity]"). NumberFormat = "General"

        .range(TableName & "[TimeStamp]").NumberFormat = "mm/dd/yyyy"

        .range(TableName & "[ReceiptTime]").NumberFormat = " [$-F400]h:mm:ss AM/PM"

        .Cells.EntireColumn.AutoFit

        .range(TableName & "[Description]").ColumnWidth = 50

        .range(TableName).Select

        .Selection.WrapText = False

    End With

    Exit Sub

errFormatReport:

    ' Include your favorite error trapping routine.
    ' Common errors include 1004 (an undetermined Excel error) and
    ' 91 (a problem with the Excel object being properly set). Don't forget
    ' to include error trapping for the vbObjectError + 1001 error (no data).    

End Sub 

Default Values in a Primary Key Field

This post is a break from the recent code snippet entries to relate something I have too much experience to not know, but didn't know anyway.

I have been developing MS Access databases for nearly two decades, now, but I have rarely had occasion to have a Primary Key that consisted of multiple fields, one of which might consistently be null or blank.  My PKs have typically been very specific, so the blank field in a PK is uncommon, to say the least. Not unheard-of, but definitely uncommon.

First of all, SQL Server does not like Nulls in a Primary Key.  MS Access doesn't care, but when build tables in SQL Server that you will link to MS Access, and one of them needs to be able to hold a blank, you'll have to make it an empty string, instead of a Null. 

And now to the point of this blog:  DO NOT RELY ON DEFAULT VALUES IN A PRIMARY KEY!

I discovered that you cannot rely on default values in SQL Server to supply a value for the Primary Key, especially if you plan to link the table via ODBC to MS Access.

Weirdnesses begin to happen at that point.  Not good ones.  Bad.  Very bad.

Briefly - I had a table with a 5-field PK, and one of those fields is consistently blank.  I had a default value in the SQL Server definition of an empty string for the field in question and everything seemed peachy until the users complained that when they entered in a new row, the data changed - visibly changed to something completely different - when they committed the new row.  It fixed itself with a refresh, but it was seriously causing confusion.  If they edited the row to "fix" it - change it back to what they'd just entered - they discovered that it was actually changing some other row in the database!  Worse, if they deleted it because it was suddenly wrong, it would delete the other row!!

Holy sheep dip!!! This was NOT a good weirdness!  It was actually displaying data from some other seemingly random row in the database.  

After a significant amount of research, I discovered that entering in a value - either manually or programmatically - resolved the issue.  Allowing the default value to derive from the table definition is not good. 

In SQL Server, when entering data manually in edit mode WITHOUT entering a value in EVERY key field (relying on default values), you'll get an exclamation point in the record selector with a control tip that states the following:

This row was successfully committed to the database.  However, a problem occurred when attempting to retrieve the data back after the commit.  Because of this, the displayed data in this row is read-only.  To fix this problem, please re-run the query.

This does not seem like a significant issue until you try adding a new record in MS Access.  THAT is when the weirdness begins and data starts to go wiggy.  

It is imperative that you physically (or programmatically) supply a value for every element of the Primary Key.  

 

Write Table Fields to Excel from Access

Here is a scenario that plays out often in my experience:

Suppose you have a query that you would like to export to Excel, and let’s further suppose the query has, say, 150 columns.  You can use the CopyFromRecordset() function (https://msdn.microsoft.com/en-us/library/Aa165427(v=office.10).aspx  an awesome built-in function in Excel that takes your data and handily pastes it into a spreadsheet) but it does NOT paste the field names into the column headers, so the result is a table with no defined columns.  

You need a method of getting those column headers into the spreadsheet before you paste the data.  

It is a relatively simple matter to write a few lines of code to loop through the fields in the RecordSet and write those fields to the spreadsheet, and that will work just fine in most cases – but if it happens that your application performs this task on multiple different data sets?  Some applications I have developed have a variety of forms that the users want to be able to export to Excel so they can more easily sort and filter, delete, edit, etc. without affecting live data.  WriteFieldsToExcel() is a very simple reusable function that saves me the trouble of writing all that For…Each code every time.  

I have broken it down into two functions in my application to accommodate both ADO and DAO recordsets.  If you wish to have both, too, simply create a second function WriteFieldsToExcelADO() and reference the appropriate recordset type in the Declarations.

The function is super simple - it just loops through the field names in the recordset and writes them column by column as the loop variable i increases.  Enjoy, and happy coding!

' Writes field names from a given recordset to an Excel spreadsheet

Public Sub WriteFieldsToExcel(objXL As Object, rst As DAO.Recordset)

    Dim fld As DAO.Field

    Dim i As Long    

    For Each fld In rst.Fields

        objXL.Selection.Offset(0, i) = fld.Name

        i = i + 1

    Next fld    

End Sub

 

Integrating with Excel from Access

I’m not sure if it’s just me, or if it is a phenomenon common to Access programmers in general, but I find myself constantly exporting data to Excel, regardless of who the client might be.  I get hired to create a database so that they are not relying on clunky attempts at database emulation in Excel – yet when it’s all said and done, there is nearly always a justifiable requirement for an output to an Excel spreadsheet.

In the next few installments, I’ll show how I use some handy user-defined functions to easily place a button on just about ANY form with a RecordSource to export the form’s data to a nicely formatted Excel spreadsheet with only a couple of lines as demonstrated below (plus any required validation and/or standard error trap code you may want to include).

Set rst = Me.RecordsetClone

Call ExportRSCloneToExcel(rst)

The next few blogs will review the following functions:

• GetExcel() – covered in this blog

• WriteFieldsToExcel() – cycles through a recordset’s field list and adds it to a spreadsheet

• FormatReportGeneric() – does some basic, generic formatting of an Excel table

• ExportRSCloneToExcel() – see explanation above

It all starts with the GetExcel function, a simple function that allows me to create an Excel spreadsheet with a single line like the following:

Set objXL = GetExcel(FileName:=”C:\MyPath\MyFile.xlsx”,CreateNew:=True)

This simple line of code creates a new Excel object and adds a workbook to it.  If the optional CreateNew argument is False or omitted, the workbook named in the FileName argument is opened.  If the file doesn’t exist, it simply raises a 1004 error to the calling procedure.

I use this often to open Excel workbooks so that I can retrieve and edit data from them.  In this series, we’ll assume we are creating new workbooks in each instance, but the code would work equally as well with existing workbooks to which you want to add data.

Let’s begin with the GetExcel() function.  Note that you'll have to set a reference to the Microsoft Excel xx Object Library (xx being your installed version) under Tools==>References in your VBA Project window:

Function GetExcel(Optional FileName As String, Optional CreateNew As Boolean = False) As Object

    Dim objXL As Object    

    On Error GoTo errHandler

   ' Create an Excel Object

    Set objXL = CreateObject("Excel.Application")

    If FileName = "" Or CreateNew Then ' For new workbooks, add one to the collection

        objXL.Workbooks.Add

    Else ' For existing workbooks, open the one by the name you passed

        objXL.Workbooks.Open FileName

    End If

    Set GetExcel = objXL ' Return the new workbook

    Exit Function

errHandler:

    Err.Raise Err.Number ' Any issues get passed back up to the calling procedure

End Function

The next installment will review the WriteFieldsToExcel() function.  

Until then, happy coding!

Retriving an Element From a Delimited String

Arrays can be handy, and there are some excellent functions in VBA-Land to help us deal with arrays, but not so much with simple delimited lists. They must be first converted to arrays before they become useful in VBA.

A simple telephone number can be considered a delimited list. It consists of three parts delimited by a dash (-). Thus, the telephone number 800-555-1212 has the following parts:

  1. Area Code: 800
  2. Exchange: 555
  3. Number: 1212

If you want to get just the Exchange portion, you can use the Split() function to convert the phone number into a three-element array, then retrieve the second element. That’s not so hard – until you need to run some code to return only phone numbers from a list with a given Exchange. It’s a bit cumbersome to write the VBA code for such a situation, and next to impossible for a SQL query. For one thing, SQL doesn’t do arrays, making it supremely difficult to get a specific element without migraines and extra caffeine.

It can get even hairier if the format used includes multiple delimiters, such as 800/555-1212.

If you (like I do) need to perform such analyses on a regular basis, the GetElement() function may be of use to you. GetElement() essentially does nothing more than what was explained above – it converts the string to an array and returns a selected element. Here is the code:

Function GetElement(LineVal As String, WhatElement As Integer, Optional Delimiter = "|") As String

Dim astrParts As Variant

On Error GoTo errHandler

‘ Create an array from the input.
astrParts = Split(LineVal, Delimiter)

‘ Get the selected array element (zero-based)
GetElement = astrParts(WhatElement - 1)

Exit Function

errHandler:

[Put your favorite error handling code here]

End Function

With this simple function, you can write a query such as the following to retrieve the Exchange element from dash-delimited numbers (800-555-1212):

SELECT GetElement([Phone],2,"-") AS Exchange FROM Customers WHERE GetElement([Phone],2,"-");

Even with multiple delimiters, it is not particularly tough. Take 800/555-1212. The first element is the Area Code (800), delimited by a forward slash. The first element of the second element (555-1212) is the Exchange (555), delimited by a dash. So we modify the query as follows:

SELECT GetElement(GetElement([Phone],2,”/”),1,”-“) AS Exchange FROM Customers WHERE GetElement(GetElement([Phone],2,”/”),1,”-“) = 555

I use this in Excel, as well, in my Personal Macro Workbook.  

Happy coding!

Page 1 of 3

HostGator Web Hosting
RoboForm: Learn more...