How can I display guids in a ListObject

I am trying to display the result of an SQL statement in a ListObject. The data that the SQL statement selects is in an MS-Access table and contains a column with the data type guid.

The ListObject object is created like so

set listObj = activeSheet.listObjects.add( _
    sourceType  := xlSrcExternal         , _
    source      := array(source)         , _
    destination := destCell)

source is a string with the value OLEDB;provider=Microsoft.ACE.OLEDB.12.0;data source=p:\ath\do\db.accdb.

I execute the select statement like so

with listObj ' {

    .displayName = "foo"

     with .queryTable ' {

         .commandType            = xlCmdSql
         .commandText            = array("select * from tab")

         .refreshOnFileOpen      = false
         .backgroundQuery        = true
         .refreshStyle           = xlInsertDeleteCells
         .saveData               = true
         .refreshPeriod          = 0
         .preserveColumnInfo     = true

         .refresh backgroundQuery := false

     end with ' }

end with ' }

This displays the result set of the query, yet without the column that has the guid.

So, is there a way to add the guid-column?

Solution

I don't think so, if you restrict yourself to ListObjects and OLEDB.

A GUID is represented as binary data in Access. Excel listobjects can't display that.

To cast a GUID to a string, it's usual to use the StringFromGUID function in Access, but that's not supported over OLEDB.

PowerQuery and Microsoft Query, however, natively understand GUIDs and will display these correctly. Working with PowerQuery through VBA unfortunately tends to pop up nasty warnings, so Microsoft Query tends to be the best option.

Alternatively, you can first execute a SELECT StringFromGUID(MyGuid) As MyGUIDString, * INTO TempTable FROM tab in Access first (by automating the Access application), then display that in Excel.