Example of VBA code formatting using manoli.net

if you go to this website http://www.manoli.net/csharpformat/ you will be able in a breeze to parse your VBA/C# code in HTML format. You can see at the bottom of this blog post an example.
To make it work in blogspot, you just need to go in Design -> Edit HTML and just after


<b:skin><![CDATA[/*

insert the following code. You can find http://www.manoli.net/csharpformat/format.aspx at the bottom of the page the link to the .css style sheet.

/* CSharp VB Formatting */

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, "Courier New", Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #006080; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

This is how the VBA code looks like formatte using the manoli.net application.

Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
  mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
    Dim DBQ As String
    Dim DefaultDir As String
    Dim Connection As String
        
    'Store the original connectin before overwriting
    mOldConnection = mQry.Connection
    
    'Build a DSN connectionless connection using OLEDB
    DBQ = ThisWorkbook.FullName
    DefaultDir = ThisWorkbook.Path
    Connection = "ODBC;DBQ=" & DBQ & ";"
    Connection = Connection & "DefaultDir=" & DefaultDir & ";"
    
    'For Excel 2003
    'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2007
    Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2003 Just change the Connecton for listed query
    'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
    '   mQry.Connection = Connection
    'End If
    
    'For Excel 2007 Just change the Connecton for listed query
    If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
       mQry.Connection = Connection
    End If
    
    
End Sub

Excel Tip of the Day: How to use Excel as a Relational Database Part IV

Here  you can find the Excel file JointTables II

This is the most complicated bit of this series, and before goind ahead let me just summarize what we have done so far.

We have a JoinTables II.xlsm Excel Workbook with 4 spreadsheets: Orders, Customers, ReportCustomers, ReportJoin

The Orders Spreadsheet Contains a Table TblOrders and a named Range Orders. The TblOrders use a DSN Connection NorthwindDSN to connect to the Northwind Database and download the data.

The Customers Spreadsheet contains an Excel Table TblCustomers and a named Range Customers.

The ReportCustomers and ReportOrdersAndCustomers (In the previous post I called it TblJoinOrdersAndCustormes) contain two Tables that query the JoinTable II.xlsm macro workbook.

If we need to move the position of the Access DB, all we need to do to have the worksheet work property is to go the the Control Panel and modify the property of the NorthwindDSN Connection so that it point to the new location.

However, if by any chance we move the JoinTable II.xlsm file to another location, it will not work either. This is because of the DSN Connectionless connection that we use to point to it.

I will show you now how with a bit of Advanced but simple VBA code we overcome this problem.

Of course, if we had used an Excel DSN connection to point to JoinTable II.xlsm, all we had to do, was to go to the control panel and modify the JoinTable II DSN Connection.

As you will see the solution I choose is to be preferred - you will only need to do anything in case you move the file to have it work properly.

This is the piece of code that you need to add in your ThisWorkbook class.

Option Explicit

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String


Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
  mQry.Connection = mOldConnection
End Sub

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
    Dim DBQ As String
    Dim DefaultDir As String
    Dim Connection As String
        
    'Store the original connectin before overwriting
    mOldConnection = mQry.Connection
    
    'Build a DSN connectionless connection using OLEDB
    DBQ = ThisWorkbook.FullName
    DefaultDir = ThisWorkbook.Path
    Connection = "ODBC;DBQ=" & DBQ & ";"
    Connection = Connection & "DefaultDir=" & DefaultDir & ";"
    
    'For Excel 2003
    'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2007
    Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2003 Just change the Connecton for listed query
    'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
    '   mQry.Connection = Connection
    'End If
    
    'For Excel 2007 Just change the Connecton for listed query
    If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
       mQry.Connection = Connection
    End If
    
    
End Sub


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim sel As Range
       
    Set sel = Selection
    On Error Resume Next
    'Excel 2003
    'Set mQry = sel.QueryTable
    
    'Excel 2007
     Set mQry = sel.ListObject.QueryTable
    
    On Error GoTo 0
    
End Sub

I will explain it step by step

This bit here declare at class level a QueryTable Object with Events. This is because we need to access the BeforeRefresh and AfterRefresh event of this table

Dim WithEvents mQry As QueryTable
Dim mOldConnection As String 

This piece of code just stores back the original connection string in the mQry.Connection property

Private Sub mQry_AfterRefresh(ByVal Success As Boolean)
  mQry.Connection = mOldConnection
End Sub

This event is raised each time we right click a spreadsheet. If we right click of a QueryTable object, Excel  will store the QueryTable in mQry (a class level variable) otherwise will do nothing.

 Please note a put two piece of code: one to work with Excel 2003, the other with Excel 2007

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim sel As Range
       
    Set sel = Selection
    On Error Resume Next
    'Excel 2003
    'Set mQry = sel.QueryTable
    
    'Excel 2007
     Set mQry = sel.ListObject.QueryTable
    
    On Error GoTo 0
    
End Sub

This is the last part. Before the Refresh of the Table takes place we do

1) Save the TableQueryConnection in a class variable mOldConnection

2) We build a DSN Connectionless connection on the fly, passing it the current file path and directory

3) We update the TableQuery.Connection property with the newly created connection. We do it only for a limited list of tables. The user will have to update this list manually.

Remember that after the refresh the original connection is saved back in the TableQuery.Connection property.

Private Sub mQry_BeforeRefresh(Cancel As Boolean)
    Dim DBQ As String
    Dim DefaultDir As String
    Dim Connection As String
        
    'Store the original connectin before overwriting
    mOldConnection = mQry.Connection
    
    'Build a DSN connectionless connection using OLEDB
    DBQ = ThisWorkbook.FullName
    DefaultDir = ThisWorkbook.Path
    Connection = "ODBC;DBQ=" & DBQ & ";"
    Connection = Connection & "DefaultDir=" & DefaultDir & ";"
    
    'For Excel 2003
    'Connection = Connection & "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2007
    Connection = Connection & "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"
    
    'For Excel 2003 Just change the Connecton for listed query
    'If mQry.Name = "ReportCustomers" Or mQry.Name = "ReportOrdersAndCustomers" Then
    '   mQry.Connection = Connection
    'End If
    
    'For Excel 2007 Just change the Connecton for listed query
    If mQry.ListObject.Name = "ReportCustomers" Or mQry.ListObject.Name = "ReportOrdersAndCustomers" Then
       mQry.Connection = Connection
    End If
    
    
End Sub

Excel Tip of the Day: How to use Excel as a Relational Database Part III

The Excel file can be found JoinTables II.xlsm

I will show you here how to join the Orders and Customers Table

1) Create a new Spreadsheet and call it ReportJoin

2) Data -> From Other Sources -> ExlJoinTablesNoDSN

3) Select just one field of the Customers Table

4) Go to the end of the wizard and Select "View Data in MS Query"

5) Go To Table Add...
6) Add the Orders Table
7) Remove the ID column
8) Drag a relationship line from Customers.ID to Orders.Customer ID.
9) Add individually all the fields you like form the two table
10) Click Return Data to MS Excel

Congratulation you are done!

You have just connected to MS Excel as it was a relational DB, and you have used SQL to join the two table!!!

As final step let me call this table TblJoinOrdersAndCustomers, by now you should now that you can do it by going to the Name Manager

A caveat of this approach comes from the connection to the Excel file. This connection, whether you decided to do it DSN or DSN connectionless, points directly to the location of the file, as you can see from this screen-shot in the Connectiong String text box.

So as you move your file to another location, this solution will not work anymore.

Just to be more clear:

If you have any Connection that point to a file path, as you move this file, the connection will break.

If it is a DSN connection, you just need to go to the Control Panel and change the property of this connection so that it points to the new file location.

If it is a DSN connectionless connection, you need to go the the Table External Data Property Definition Dialog box and change the connection path.

In the next post I will show you how to sort out this problem when you are connecting the Excel file without a DSN connection. I will assume that you use the current excel file as the external data source for you query, so that if you happen to move it, the file will keep working fine.

Excel Tip of the Day: How to use Excel as a Relational Database Part II

Code can be found here JoinTablesII.xlsm
We are going now to join the Orders and Customrs Tables using MSQuery.
When I try to connect to Excel as datasource, my preferred way is to use a DSN connnectionless connection.
This is how you can to do it.

1) Rename Sheet3 to ReportCustomers
2) Data -> From Other Sources -> From MS Query

 3) Choose
4) The Create New Data Source window will show up
5) Name the DataSource NorthwindNoDSN
6) Choose the MS Excel Driver

7) Click Connect...
8) Select Workbook...
9) Select the current workbook from the list
9) Click OK 4 times


ops... you will get this error

10) Click Ok, Cancel, No
to return to MS Excel?

What did go wrong?
We have two nice table in our Excel workbook but MS Query cannot see them.
Les go to Name manager and have a look at it

 As you can see the two names are table in the Name Manager Dialog box. I know that to have MS Query see the tables I need to have Named Range (not Named Tables). So, let's try this out


1) Click New
2) Enter Customers as Name
3) And =TlbCustomers in the formula bar

Now we have a name range that point to the TblCustomers. Let's try if it works.

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN

You Still Get the same Error!!!!
We are just out of luck. This is something that Microsoft should fix in one of their future release!
Let try another solution.
It seems that pointing directly or indirectly to TblCustomers does not fix the problem.
So let create a Name that points to the directly to the range.

1) Formulas -> Name Manager -> Customers
2) Edit..
3) Then Make sure that the formulas looks like =Customres!$A$5:$F$34 and not like =TblCustomers[#All]

With this new name range try

1) Data -> From Other Sources -> From MS Query
2) Select NorthwindNoDSN

We Finally got it!!!
3) Select all the fields
4) Go to the End of the Wizard, Select Cell A5 in the ReportCustomers worksheet.

You have finally managed to query an Excel Table using MS Query.
As you can see from this screen shot, you have just created a ODBC Connectionless  connection to the JointTablesII excel file.

Rename the new Query to ReportCustomers going to the Name Manager as described above.

The Customers name range have these properties:

1) Will change automatically in size as we add new Customers
2) Will change automatically in size as we add column to the left in the Table
3) Will not work properly if we add a column to the right at the end of the table.

This is why I wished that Customers = TblCustomres[#All] would have worked.
So just keep an eye not to add columns to the end of the range.

The Orders name range have these properties:

1) Will change automatically in size as we add new Orders


So any time we change the columns from the underlying query that retrieve the order we will need to redefine it.

The fact the the two named range are not sensitive to new column in not a major annoyance.
Usually when a table is set up, the thing that is most likely to change is the row count not the column numbers.

In the next post you will see how to join the two tables and how to sort out some other minor annoyance.

Excel Tip of the Day: How to use Excel as a Relational Database Part I

Here you can find the Excel Workbook for the tutorial JoinTable.xlms

I am going to show you how to use Excel as a Relational database. This is quite an advanced topic, so I will assume that a user is already familiar with Excel 2007 table, names as pointer to functions, VBA and how to access to external data source. I will cover each of this topic in details in my post, but I will assumer them as known in this one in order not to loose focus and keep the post shorter.
What you need: Excel 2008, Access 2007 and the Northwind database sample.
In this post I will show you how to connect to an Excel Spreadsheet and query it with MS Query, so I will show you how to deal with Excel as it was a relation data source.
The interesting part is that I will show you how to query an Excel Workbook form itself.

Create the Workbook and DSN Connnection

1) Create a new Workbook and name it JoinTables, save it as .xlms, a Macro enabled spreadsheet

2) Create a DSN connection to the Northwind data base (see here ) as name it NorthwindDSN


 Create the Orders Query

1) Rename Sheet1 to Orders

2) Go to Data -> From Other Sources -> From MS Query

3) Select the NorthwindDSN Source

4) Go ahead and import the Orders Table in Orders Sheet cell A5.
    Select the following fields: Order ID, Custormer ID, Order Date, Shipped Date, Ship Name, Ship Address, Ship City, Ship State/Provicne, Ship Zip/Postal Code, Ship Country/Region, Payment Type



This is what you should end up with in your Orders Spreadsheet

We need now to change this Table Name "Table_Query_From_NorthwindDSN" in a more meaningful name.
1) Go to Formula -> Name Manager
2) Select Table_Query_From_NorthwindDSN
3) Click Edit
4) Change it name to TblOrders
5) Click Ok, Close

You have now a Name called TblOrders that we can use to refer to the Table Orders.


Create the Customer Query


1) Rename Sheet2 to Customer

4) Go to Data -> From Other Sources -> From MS Query

5) Select the NorthwindDSN Source

6) Go ahead and import the Customer Table in Customers Sheet cell A5.
    Select the following fields:ID, Company, Last Name, First Name, Email Address, Business Phone

We have now two table connected with each other with the custormer ID field.
The customer ID field act as primary key in the customer table, and as foreign key in the Orders Table.
Our target is to join the two table together using MS query.
Before going ahead I would like to point out tha we can have 3 different case

1) Orders and Customers Table come form a Data Base

2) Order il a Spreadsheet Table, Customers is a DB Table (and viceversa)

3) Orders and Customer are both Excel Table

To see if there is any difference in the solution I suggest we are going to trasform the Customer Table in a local SpreadSheet Table.

1) Right click on the Customers  Table
2) Table -> Convert To Range -> OK

This way we do not have any more the Customer Table connected to the Northwind DB.

3) Click on the Table
4) Insert ->  Table -> Ok

We have name a spread sheet Table that will be called Table3. Again let's change it to a better name

1) Go to Formula -> Name Manager
2) Select Table3

3) Click Edit
4) Change it name to TblCustomers
5) Click Ok, Close

 If you go to the Names Combo box, you will see that we have tow Names: TblCustomers, TblOrders with workbook scope.
If you are using Excel 2003, it is worth point out that
1) The table Orders will have a scope limited to the worksheet, so to make it global you will have to create another name that points to it
2) The equivalent in Excel 2003 of a Table is called "List", so you need to create a List, rather than insert a table

I will cover both of this two topics in more details in my later post.
It is time now to move to part two.

Excel Tip of the Day: How to connect to an external Data source Part II

In this blog post I will show you how to create an DSN Connectionless connection to Access.
First of All you need to go to
Data -> From Other Sources -> From MS Query

On the Choose Data Source Dialog Box, choose

On the Create New Data Source
Insert a Name for the connection and select the appropriate driver as shown in the picture.
Then Click Connect and Select the Northwind MS Access file.
Clik OK twice.
Select a Table with few fields, go to the end of the wizard, select a cell and you are done.

If you right click on this Table, go to
Table -> External Data Properties -> Click the Property button close to the name text box and click definition
you will see this window

As you see from the Connection String Text Box, there is no "DSN=" string.
Congratulation, you have just created an DSN connectionless connection.
Some of you might have noticed that Excel created a "Query form Northwind1" connection.
The Northwind1 stems from the fact that I had already a Northwind DSN connection with the same same, in the same Excel file.
I would suggest to name NorthwindDSN the connection with a DSN datasource to distinguish it from the one that connects to the same data base without DSN.

Excel Tip of the Day: How to connect to an external Data source Part I

You can connect Excel to an external data source using a DSN Connection or using a DSN connectionless string.

I will first show you how to connect to a DSN Connection for an Access database

First of all you need to create a DSN Connection. To do that go to

Control Panel  -> Administrative tools,-> Data Sources (ODBC)

The ODBC Data Source Administator Window will pop up.

Then click the Add.. button and the following window will show up (for W7 64bit see here )

Select the drive you need. For this example I am going to connect to the North wind Database.

Scroll down and select the  Microsoft Access Driver. I am picking the second one in the list because I need to connect to an Access 2007 DB.

Click Finish.
This window will show up

Insert the ODBC Data source name and a description for it.
Click Select... and browse to the .accdb/.mdb file that you need to connect to.
You are done with the ODBC connection.

Go to Excel and click and go to

Data -> From Other Sources -> From MS Query,

This action will display the Choose Data Source window.
Select Northwind and click Ok.
Select the Employee Table and few ot its fields
Click Next 3 times
Click Finish
Click OK

You are done!

if you right click on the Table and Select Table -> External Data Properties,

The External Data Property Window will show up.

If you click the property button, which is on the righ side of the Name Text box you will see the Connection Property screen.

Click on the Definition Tab.

If you have a look at the Connection String Text Box, there is "DNS=Northwind", this means that the Query is using a DSN Connection to connect to Northwind

In the Next Tutorial, I will show you how to create DSN Connection less connections.

Personally I normally use DSN Connection when I am connecting to External Data Source (Access, SQL Server) from Excel, DSN Connectionless when I am connecting to Excel as an external data source (This is an advanced topic I will cover in a future post) and the JET.OLDE.DB provider when connecting using VBA (This will be part of another post).

As usual please leave a message if you like the post or have any comment.

ODBC driver in Windows 7 64bit

If you are working with W7 64bit, and go to

Control Panel  -> Administrative tools,-> Data Sources (ODBC)
The ODBC Data Source Administator Window will pop up.
This is actually pointing to this .exe file "C:\Windows\System32\odbcad32.exe"

 If you try to Add a new ODBC data source

only the SQL Server driver will show up.

To sort this problem you need to load up the 32bit version of this Window that can be found at the following path.

C:\Windows\SysWOW64\odbcad32.exe

 This way you can create DSN connection other than SQL Server on a 64bit OS.

For futher details just go here