How to call a parametric stored procedure from Microsoft Excel Query

Hi,
This is a very nice trick to call a stored procedure with parameters from excel.

If you type for example

exec model.GetPrices (?,?,?,?)

or

CALL model.GetPrices (?,?,?,?)




you will get this message

"Parameters are not allowed in queries that can't be displayed graphically"

while instead if you put the second Call within {} like that


{CALL model.GetPrices (?,?,?,?)}

it will work!!!

How to get the scripting dictionary enumerator to use in the for each loop in visual basic

A common practice is writing VB 6.0 or VBA code to wrap the Collection object in order to create strongly type Collections.

An alternative to the collection object is the scripting.Dictionary object which you can find adding a reference to the Microsoft Scripting Runtime.

The Dictionary Object is an Hash Table, so it is preferred to the Collection object when you need to access elements in the collection by key.
In addtion it has few properties and methods that the Collection object is lacking.

How to read write and save to a config file in C#

Here you can find some c# code to read/write/save to a config file in c#.
Please note that you need to add a reference to
References -> Add Reference -> System.Configuration

 and

using System.Configuration.
In Addtion you need to add an application configuration file

How to create custom collection in VBA tricks

This post has been update here


You can find attached here the code that shows you how to create a strongly typed collection in VBA that has both a default Item property and that can be iterated with the For Each Loop.

As you will see from this blog post, some vba attributes are needed to be assigned to specific Collection properties, however these are not visible in the VBA IDE, but you can see them using notepad.




This solution was taken from a forum entry I found on the web
In VBA You can create both a defaul property and a default enumerator, but the process is a bit more manual.
If you export a .cls file from one of your VB6 proceedures and view it in a Notepad, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s).

The two properties in question will look something like this:
Property Get Item(Index As Variant) As Parameter
     Attribute Item.VB_UserMemId = 0
     Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = Me.mCollection.[_NewEnum]
End Property


It is important to note that the Attribute directive are just below the Property Signatures.
If they are not there, the code will not work.

Now the above all looks "normal" except for the addition of the three "Attribute" Lines.

In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.

In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)

The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to (1) Make your Class, (2) SAVE, (3) Export the Class, (4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove") and then (5) Manually add the Attribute Lines as shown above, (6) Re-Import the edited Class.

As for one comment in this post another way is
1) Add those attributes in the VBA IDE. You will get  a syntax error. Ignore it
2) Click on the Class and Remove
3) Say Yes when you are asked to Export it
4) Import it again

Pay attention to not delete the class. Otherwise do: Export, Delete, Import.


(Btw, you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)

As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.)

The alternative is to create your class 100% within VB6 and then import it into your VBA Project.

Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

Mail Merge with multiple To, CC, distribution lists and changing Subject

Link to Advance Mail Merge.doc
Link to Advanced Mail Merge DB.xls



In this two files you will find a way to extend the MS World mail merge to send email to
1)  Have multiple mails and distribution list in the To field
2)  Have multiple mails and distribution list in the CC field
3) Have a chaning subject

Also nothe the the merged field in the .doc document can be formatted
1) To format a date, toggle the field and add \@"DD MMMM, YYY"
2) To format a number add \##,##

In the attached document you will find an example.

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

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

The Model View Controller and Presenter Pattern

In this post I will just give you some link I found that describes quite clearly how the Model View Presenter Pattern (MVP) and the Model View Controller pattern work.
As usual I will add my comment when I have a bit of time, in the mean time I hope you will find these link usefull.

How to Separate the DAL Layer and BLL Layer in a a C# application

Link to Class diagrma Layering
Link to Class Diagram Mail System.
NClass 2.0 or higher is needed to read the file.  NClass Website

In this article we are going to discuss how to separate the Data Access Layer (DAL) form the Business Logic Layer (BLL) using

1) The Abstract Factory Pattern
2) The Model Provider Pattern
3) Reflections


On the Class Diagram Mail System you will find many notes on how to separate the BLL from the DAL
I will first comment on the Class diagram you will find on the link. After I will develop an applicaton. This is a working in progress. As usual if you have any suggestion or improvement, just add a comment.

Parsing an XML file using C#

Link to the Code (VS 2008)


I will show you here two pieces of code in C# that will let you parse the Products.XML into a List object.
The first one makes use of the System.XML namespace, while the second use System.XML.Linq one.
The Linq code is pretty amazing, it is so simple I will not even comment it. I managed also to include a "where" clause to filter the output.

Developing COM exposed classes in C#

Press  here to download the template. CSharp_Com_Class.zip will be downloaded.
For some code example see this  post

Here you can find a series of notes I took as reminders to develpod a COM Class in C#.

I will put it here just as a reference, I hope I will have more time in the future to show you a full example.
You can also find here a C# template you can use to start develop C# COM exposed class. You can delete all the comments out of it. I just add them there for my reference.



To install the template in your VS2008 you need first to find out where they are stored.
To do this, go to File, Export Template. After a few click you should find out where your exported templates
are stored
On my PC for example, they are store here
C:\Users\PP\Documents\Visual Studio 2008\My Exported Templates\WFA01.zip

Once you know this path, just copy the CSharp_Com_Class.zip in the following directory.
You must copy the .zip file. Do not unzip them.

C:\Users\PP\Documents\Visual Studio 2008\Templates\ItemTemplates\Visual C#.

If things go well (and it took me sometime to figure out how to do it) you should have a new template in your
Add New Item, Visual C# Item

UDFs for Excel in VSTO

VSTO does not support yet the introduction of UDFs, which is a kind of crazy!
However there are few work around.

Windows 7 run command...

Where has the Run command gone in Windows 7?

You need to add it.
This are the steps

VSTO "Excel Disigner Could Not Be Activated" error

After Installing VSTO on my PC, and trying our my first "Code Behind" project, I could not have access to the Excel designer. No controls were displayed in the Control toolbox and I could not any button or any type of control on the excel worksheet I was working on
The error message was a pretty scary one

"Excel Designer Could Not Be Activated"

After a bit of diggin on google I found this Post on the msdn forum, which helped me out sort the problem.

Office 2003, PIA Installation guidelines for .NET platform

In case you have problem getting the Primary Interop Assembly (PIA) working fine with your .NET Visual Studio platform, just check this link.

Installing the PIA for Office 2003

Microsoft.Jet.OLEDB.4.0 provider driver on a 64bit System

Today, I tried to set a connection to an Access 2003 database using ADO.NET  whithin c# 2008 express platform.
After setting the option

Excel Tip of the Day: How to work with Lists

A List is a set of ordered labels. For example the days of the week is an example of list

Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday


Excel has some built-in list:

Excel Tip of the Day: My favourite shortcuts

In this post I will publish my favourite shortcuts. Shortcuts are overlooked by most users, but I can guarantee you that if you start to learn them you will be as much as 30% faster while using Excel. You will not need to use the mouse anymore, which is very time saving.

Let's get started

Back up software: Allway sync, Syncback pro, Paragon Hard Disk Manager Suite (Part II)

In this post we will have a look at Allway Sync.

HTML, CSS, Java script comments

While working on this blog, I have realized that there are at lest three different ways to insert a comment on a HTML web page.

1) HTML comments
<!--This is a comment. Comments are not displayed in the browser-->

The tricky part was to find out how to visualize this comment on the webpage, something that is not supposed to show-up! . The solutions comes down to this table