Excel how to show all value in a filtered table in vba

Hi,

If you want to show all value from a filter table, you need to check if there the table is filtered first, otherwise the Sheet1.ShowAllData will fail.
This is the solution

With  Sheet1
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    End If
End With

Formatting code for HTML display

Hi,

This is really a great link
http://www.manoli.net/csharpformat/


to format c# or VB.NET/VBA/VB code in HTML 4 format to display on the web

How to Create a task or appointment using VBA Code in outlook 2010

Hi,
I have changed the original code at this link to show you how to create either a task or an appointment from an e-mail item.
This is very handy when you want quickly create task/appointment without wasting your time to attach the original mail.
The only problem is that the item is attached at the end of the file. This is due to a bug in Outlook 2008/2010 for which the postion item does not work.
Just copy and paste the code below into an outlook module to make it work.

Sub CreateTaskFromMail()
  Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
   
    Set OE = Application.ActiveExplorer
   
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create a task" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
   
    Set MI = OE.Selection(1)
    Set TI = Application.CreateItem(olTaskItem)
    With TI
            .Subject = MI.Subject
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"

  
    Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
       vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
    Case vbYes
         TI.Body = "View Original Mail attacched at the bottom" & vbCrLf & TI.Body
         TI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
         TI.Display
    Case vbNo
         TI.Display
    Case vbCancel
         Exit Sub
    End Select
          
          
         
    End With
  
End Sub



Sub CreateAppointmentFromMail()
    Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
   
    Set OE = Application.ActiveExplorer
   
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create an appointment" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
   
    Set MI = OE.Selection(1)
    Set AI = Outlook.CreateItem(olAppointmentItem)
    With AI
            .Subject = MI.Subject
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"

  
    Select Case MsgBox("Do you want to attach the original mail?" & vbLf, _
       vbYesNoCancel + vbQuestion, "Add Mail as Attachment ...")
    Case vbYes
         AI.Body = "View Original Mail attacched at the bottom" & vbCrLf & AI.Body
         AI.Attachments.Add MI, , 1 'Position does not work. It is a bug in Outlook 2008/2010
         AI.Display
    Case vbNo
         AI.Display
    Case vbCancel
         Exit Sub
    End Select
          
          
         
    End With
  
End Sub




Sub NewTaskOrAppoitmentFromMail()
  Const mailItem_c As String = "MailItem"
    Dim OE As Outlook.Explorer
    Dim MI As Outlook.MailItem
    Dim AI As Outlook.AppointmentItem
    Dim TI As Outlook.TaskItem
   
    Set OE = Application.ActiveExplorer
   
     'Abort sub if no item selected:
    If OE.Selection.Count < 1 Then
        MsgBox "Please select an already saved message before" & vbCrLf & _
        "attempting to create an appointment or task" & vbCrLf & _
        "with this button ...", vbInformation, "No message selected ..."
        Exit Sub
         'Abort sub if item selected is not a MailItem.
    ElseIf TypeName(OE.Selection(1)) <> mailItem_c Then
        MsgBox "You must select a mail item...", vbInformation, "Invalid selection..."
        Exit Sub
    End If
   
    Set MI = OE.Selection(1)
    'Beep
    Select Case MsgBox("Do you want to create a Task?" & vbLf & _
        "To Add Task (Yes) / To Add Appointment (No) / To Quit (Cancel)" & _
        vbCrLf, vbYesNoCancel + vbQuestion, "Create a task or appointment ...")
    Case vbNo 'If No, create appointment
        Set AI = Outlook.CreateItem(olAppointmentItem)
        With AI
            .Subject = MI.Subject
            .Body = "View Original Mail attacched at the bottom"
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            .Attachments.Add MI, , 1
            .Display
        End With
    Case vbYes
         'If Yes, create task with no due or start date
        Set TI = Application.CreateItem(olTaskItem)
        With TI
            .Subject = MI.Subject
            .Body = "View Original Mail attacched at the bottom"
            .Body = .Body & vbCrLf & vbCrLf
            .Body = .Body & "-----Original Message-----" & vbCrLf
            .Body = .Body & "From: " & MI.Sender & " [mailto:" & MI.SenderEmailAddress & "]" & vbCrLf
            .Body = .Body & "Sent: " & Format(MI.SentOn, "DD MMMM YYYY HH:MM:SS") & vbCrLf
            .Body = .Body & "To: " & MI.To & vbCrLf
            .Body = .Body & "Cc: " & MI.CC & vbCrLf
            .Body = .Body & "Subject: " & MI.Subject & vbCrLf
            .Body = .Body & vbCrLf
            .Body = .Body & MI.Body
            .Attachments.Add MI, , 1
          
            '.StartDate = Date
            '.DueDate = Date + 1
            '.ReminderTime = .DueDate & " 10:00"
            '.Save
            .Display
          
                      
          
        End With
         'Case vbCancel
         '   Exit Sub
    End Select
End Sub

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 build a mail system framework in Excel using OOP: Example

I will explain how to use the framework to build a new mail object using the framework I have build

Let'us suppose we want to build a mail to trade a Total Return Swap to be send to a broker

 1) Interface Layer: We first create a ITrsMailDataProvider and define its interface

Option Explicit

Public Function RetrieveBody(irsId As String, fundId As String) As Parameters

'No Code Here
End Function

Public Function RetrieveHeader(irsId As String, fundId As String) As HeaderDTO
'No Code Here
End Function

 2) DAL:  We then create a TrsMailDataExcelProvider that implements teh ITrsMailDataProvider interface. You   can use some mock data for testing. If you are getting data from Access just go ahead and create your TRSMailDataAccessProvider that implements the same interface

2) Interface Layer: Then we need to Create a IProviderFactory. This will be the abstract factory that define the abstract methods that return our interface ITrsMailDataProvider or a  IIrsMailDataProvider. The idea is that the abstract providers will be produced by your abstract factory. The business layer will use only those interface and will know nothing about which concrete provider it is in use

   This class will look like
   Class IProviderFactory
       GetTrsMailDataProvider as ITrsMailDataProvider  //only method signature
       GetIrsMailDataProvider as  IIrsMailDataProvider  //only method signature
   End Class

3) DAL Layer: We then need a class that implement the IProviderFactory. For Example a ExcelProviderFactory will take care to create of the MailDataProvider that have Excel as source.

Option Explicit

Implements IProviderFactory

Private mTrsMailDataProvider As TrsMailDataExcelProvider
Private mIrsMailDataProvider As IrsMailDataExcelProvider

Private Function IProviderFactory_GetIrsMailDataProvider() As IIrsMailDataProvider
      If mIrsMailDataProvider Is Nothing Then
         Set mIrsMailDataProvider = New IrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      Set IProviderFactory_GetIrsMailDataProvider = mIrsMailDataProvider
      
End Function

Private Function IProviderFactory_GetTrsMailDataProvider() As ITrsMailDataProvider
     If mTrsMailDataProvider Is Nothing Then
         Set mTrsMailDataProvider = New TrsMailDataExcelProvider
      Else
         'Do Nothing
      End If
      
      Set IProviderFactory_GetTrsMailDataProvider = mTrsMailDataProvider
End Function

4) BLL: Finally we will have the client of the Abstract Factory, as for the Abstract factory Pattern. We will call this class the DataProvider (this can be a static class in c# ore moduel in VBA) and it will sit in the business Layer. This Class has the main objective to
use the IProviderFactory interface to produce our Trs and Irs MailDataProvider. The diffuclt part here is that this class should istantiate a Real istance of the abstract class IProviderFactory, but as we know we want the business Layer to be agnostic of the DAL. To get this result in C# we can use reflection. We can just write a method so that it loads a .dll specified in a config file as a string, and create an istance of a class from this dll.
This way to reference to the DAL Layer are necessary. In VB 6.0 this is can be done with CreateObject.

Option Explicit

Private mFactory As IProviderFactory

Public Function IrsMail() As IIrsMailDataProvider
  Set IrsMail = Factory.GetIrsMailDataProvider()
End Function

Public Function TrsMail() As ITrsMailDataProvider
  Set TrsMail = Factory.GetTrsMailDataProvider()
End Function

Private Function Factory() As IProviderFactory
'Insert Code here
'This cose should use a config file and reflection to choose
'which concrete factory instantiate. Createobject could be used in VB 6.0
'to make this class decouple with the DAL Layer
'We keep things ease here.

 If (mFactory Is Nothing) Then
     Set mFactory = New ExcelProviderFactory 'I really should be using CreateObject
                                             'to keep the class decoupled from the DAL
 End If
 Set Factory = mFactory
 
End Function

4) So fare we have done the following
    a) Business Layer -->  Interface Layer -->; DataBase Layer
    b) In the Interface Layer we have implemented the Model Provider Pattern and the Abstract Factory Pattern. Those class are just interface that define the methods that the business Layer can call to accesss the data
    c) In the DAL we have the real providers that just implement the provider interfaces and the real factory that just implement the factory methods.
    d) The transfer of the data between DAL and Business Layer is done using some object that are on the common layer such as MailDTO (data transfer object) or the Parameters collections (this is an utility object)
    e) The client of the abstract factory, the one we called DataProvider, is in the BLL and it stores in as a private field a reference to the IProviderFactory. This is where the magic happen: we can just switch the RealProvider withouth having to change any of the code that regards the BLL or Interface Layer.

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 send a mail message in C#

Here you can find a piece of code to send mail using c#.
Please not that you need to know your Smtp Host server name to be able to sent out any mail.
In addtion I added a couple of lines to get the current user name.
You can map the current user name to an email address so that you can send the mail out from the current user mail address

ex: mailAddress = abeti then use mrblue.abeti@companyname.com as mail address

using System.Net.Mail;
using System.Security.Principal;

        private void button1_Click(object sender, EventArgs e)

        {
            string mailAddress;
            mailAddress = WindowsIdentity.GetCurrent().Name;
            Console.Write(mailAddress);
            MailMessage message = new MailMessage();
            message.From = new MailAddress("mrblue.abeti@companyname.com");
            message.To.Add(new MailAddress("mrred.pini@companyname.com"));
            message.CC.Add(new MailAddress("mryellow.ciliegi@companyname.com"));
            message.Subject = "Message From .Net";
            message.Body = "This is the content";
            string pdfPath = "C:\\test\\";
            string pdfFileName = "prova.pdf";
            Attachment pdfFile = new Attachment(pdfPath + pdfFileName);
            SmtpClient client = new SmtpClient();
            client.Host="MAILSERVER";
            //client.Send(message);
        }

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

Exposing a .NET interface to COM as an interface

---

When you define a NET interface along with a ComVisible class with your interface set as the default one , VB6 hides this default interface entirely from you. Then, when you want to work with that interface in VB6, you use the name of the implementing class. 

The Default interface is set either with ComDefaultInterface attribute or if this is left out, C# will make the default interface the first Interface that the Class implements.

So if you want to see the interface in VB 6.0 you should not have any class implement directly the default interface, but an interface that derives from the default one. Remember that for COM all the interface members of the base interface should be redefined in the derived interface using the new keyword.

How to quick wrap a .NET Collection in a COM component

Here is the code on how to quickly wrap up a .NET Collection and exposed it to COM so that it has a default Item property and a working For Each Loop.

These are the main changes I did to the code compared to my previous post ( see point 9 )

2) The Class NetProva inherits directly form SortedList, while before I used encapsulation and delegation to mimic inheritance. This means that we just need to code the GetEnumerator(). This is because we need this method to return the most generalized interface Collections.IEnumerator. Note the use of the new keyword in the code. We could not ovverride otherwise we would have got a System.Collections.IDictionaryEnumerator return type. Also note that we are return the Enumerator of the Keys becasue COM does not support the DictionaryEntry type.

3) The other method that we might need to implement.ovverride is the indexer. We did not need to do anything here.


TIP
You might be tempted to omit the inheritance relationship when defining a COM
interface because the base methods need to be defined anyway and you don’t have to deal with the
mess of multiply defined members. However, don’t omit the relationship because it’s
still important for proper operation on both .NET and COM sides. Not only does it
provide the expected behavior in .NET clients using such interfaces (such as implicitly
converting an INetProva type to an IEnumerable type, but for COM as well because
a CCW makes QueryInterface calls on the derived interface succeed for any of its
base interfaces.

namespace Collections01
{
    //  To expose properties and methods to COM, you must declare them on the class 
    //  interface and mark them with a DispId attribute, and implement them in the class. 
    //  The order in which the members are declared in the interface is the 
    //  order used for the COM vtable.
    //  ex:
    //  [DispId(1)]
    //  void Init(string userid , string password);
    //  [DispId(2)]
    //    bool ExecuteSelectCommand(string selCommand);

    //Class Interface
    [Guid("2c280db5-99d0-4b5d-a014-13f6a3dfe271"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface INetProva : IEnumerable {
        [DispId(-4)] //Iterator
        new IEnumerator GetEnumerator();
        [DispId(2)]
        void Add(object key, object value);
        [DispId(3)]
        int Count { get; }
        [DispId(4)]
        void Remove(object key);
        [DispId(0)] //Default Property
        object this[object key] { get; set; }
   
    }



    // To expose events from your class, you must declare them on the events 
    // interface and mark them with a DispId attribute. 
    // The class should not implement this interface. 

    //Events Interface
    [Guid("23aaa0da-ba82-48a1-95fb-789e8e061be5"),
     ComVisible(true),
     InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface INetProvaEvents
    {
    }



    //The Class can also implement other interfaces. But only
    //the first one will be exposed to COM.
    //COM Class do not support inheritance beyond interface implementation
    //Class Employees : List<Employee> is not COM compatible

    //Class Implement the Class Interface
    [Guid("4a37e6a5-2efc-42d5-91db-52787a258d85"),
     ComVisible(true),
     ClassInterface(ClassInterfaceType.None),
     ComDefaultInterface(typeof(INetProva)),
     ComSourceInterfaces(typeof(INetProvaEvents)),
     ProgId("Collections01.NetProva")]
    public class NetProva : SortedList,  INetProva
    {
        public new IEnumerator GetEnumerator()
        {
            ICollection keys = this.Keys;
            return (IEnumerator)keys.GetEnumerator();
         
        }
    }
}