Saturday, 27 November 2010

Skip Clipboard during VBA.. operation

Recently i came across an error in Excel 2007 as

"Picture is too large and will be truncated."

Upon looking into the issue, i found that leaving stuff on the clipboard, used during the copy and paste operations within VBA was the culprit. This error occurred when the file was about to be closed by the user.
                                After digging in to many portals and unsuccessful attempts to resolve the error by clearing the clipboard via VBA, I found one way to fix the code by redesigning my VBA codes to skip the use of copy and paste and stick with the range based value transfers. Which later i realised is more robust and better way of doing things.

The simplest of the solutions to completed the copy and paste task is

DestinationRange.Value = SourceRange.Value
'with the format code later.

And the solution for the elimination of formulas and paste as values could be gained by

SelectionRange.Formula = SelectionRange.Value

As the office clipboard is now provided in task panes section its quite unclear as of now which solutions can clear the clipboard in VBA.

And to add upon the article
Application.CutCopyMode = False 
doesnt work to clear the office clipboard.

Friday, 5 November 2010

Let outlook do the thinking: Time based header for outlook

              Myself, being lazy tend to respect the people with greeting appropriate to the time of the day, like Good Morning, Good Afternoon & Good Evening.

   But keeping track of code, time, life and every thing else takes a toll and some times i try to automate these bits as much as possbile to simplify my life.
                  To cut short i desgined a macro which could populate the outlook text for new email with correct headers based on the time of day with the shortcut "Ctrl+J"

To accomplish this

1. Start you outlook..
2. Open a new email interface, press Alt+F11 on you key board to power up your VBA editor.

Navigate to location "Normal" as shown in the image below:

Insert a module named "CustomTextMacro" (any thing you like as it personal choice
In that module Type(if you like)/CopyPaste in the following peice of code:

Sub InsertCustomText_Mail()
Selection.HomeKey Unit:=wdStory
Selection.Font.Color = wdColorDarkBlue
If TimeValue(Time()) <= TimeValue("12:00:00") Then
Selection.TypeText Text:="Good Morning,"
ElseIf TimeValue(Time()) <= TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Afternoon,"
ElseIf TimeValue(Time()) > TimeValue("16:00:00") Then
Selection.TypeText Text:="Good Evening,"
End If

Selection.TypeText Text:="Thank you"
Selection.TypeText Text:="Regards"
Selection.TypeText Text:="Amol Pandey"
End Sub

3. Then once completed, navigate to following location as shown in the image:

clicking on it will bring the following window:

then navigate to the Keyboard section of the window and click on it to bring on the following next window:

In this window we will find our macro listed and assigning the macro the required shortcut keys will accomplish the job and, there we have out complete automated solution for the custom outlook header.

Just press the magical shortcut, and there you go on your new email you have the custom header based correctly on the times of the day...

Hope it simplifies your lives as mine...

Cubed Formulas: Compute Data Throughout Workbook

           Recently i landed upon a brilliant techniques demonstrated by Better Solutions for illustrating the CUBED FORMULA, power of excel. This techniques thrust's excel to aggreagte same worksheet locations on diffrent sheets mentioned in the formula as workshet series.

 "Sheet1:Sheet4!A2:B5". This technique can be used in formula as   "=Sum(Sheet1:Sheet4!A2:B5)"

This technique would help aggreagate the certain ranges from each sheet specified adress and contained withing the worksheet series. Though being an advantage one has to be care ful to be aware that movement of order of sheets would alter the logic of formula performed and the intended result.

   For further information please check the refrence for more in detail description of the functionality.

Better Solutions : Link

Exploring Drop Down...Multicolumns

Many times the case where by one list of drop downs is not enough business request for multiple columns for the drop downs list for one chosen item.

Face with the issue heres my quick code for the same to overcome...

'Lets populate the and set the control parameters.
    obj.Clear 'Clear ne thing from the pre population of control if already done.
    obj.ColumnCount = 2 'Set the number of columns required to display.
    obj.ColumnWidths = "150;150" 'Set the size if columns
       For i = 1 To NoOfItems
                With obj
                    .AddItem "column1 item"
                    .List(custom_Counter, 1) = "column2 item"
                    custom_Counter = custom_Counter + 1
                End With
       Next i                         

'DropDown Change Event
      obj.List(obj.ListIndex , 0) 'Return you the selected 1st column value
      obj.List(obj.ListIndex , 1) 'Return you the selected 2nd column value

and there you go .. Thats out multi column drop down box....

Download Solution
Download solution

Wednesday, 3 November 2010

RefEdit Alternative: Let Textbox Handle it..

One of the most annoyances of VBA was the utility of RefEdit. Though working fine based on its mood of its own, with the trouble of focus and other petty issues.

Recently i stumbled upon a code illustrating the TextBox swiss army knife capabilties of providing a brilliant alternative to RefEdit control. the magic lies in the following code as follows when insetered during the form intiliazation process.

Me.txtRefChtData.DropButtonStyle = fmDropButtonStyleReduce 
Me.txtRefChtData.ShowDropButtonWhen = fmShowDropButtonWhenAlways

and the event handler for the same goes as follows:

Private Sub txtRefChtData_DropButtonClick()
 'do Somthing.. 
End Sub

and it does the jobs with no compatibiltiy and focus issues..

Download Solution
Download solution

Lets Talk : Unix to VBA

I had always wondered if i could some how skip the PUTTY screen for my basic task like runing some custom scripts at my work or if possbile i could bring them to a better world of windows push button technology. And more i wanted it in excel using VBA.

The solution to my problem was partly to a major extent aswered by PLink. This tool allowed me to act as a brigde to communicate to unix via batch script file. And with Excel VBA i can easily create them on fly. Hence forth i landed with this peice of solution i have provided as intiation of the project which could be developed as desired. This tool is ammendable as required to be used with .NET solutions as well.

A good help for the PLINK tools is avilable at Link.

A quick guide for the command list for PLINK:

'PuTTY Link: command-line connection utility
'Release 0.58
'Usage: plink [options] [user@]host [command]
'       ("host" can also be a PuTTY saved session name)
'  -V        print version information and exit
'  -pgpfp    print PGP key fingerprints and exit
'  -v        show verbose messages
'  -load sessname  Load settings from saved session
'  -ssh -telnet -rlogin -raw
'            force use of a particular protocol
'  -P port   connect to specified port
'  -l user   connect with specified username
'  -batch    disable all interactive prompts
'The following options only apply to SSH connections:
'  -pw passw login with specified password
'  -D [listen-IP:]listen-port
'            Dynamic SOCKS-based port forwarding
'  -L [listen-IP:]listen-port:host:port
'            Forward local port to remote address
'  -R [listen-IP:]listen-port:host:port
'            Forward remote port to local address
'  -X -x     enable / disable X11 forwarding
'  -A -a     enable / disable agent forwarding
'  -t -T     enable / disable pty allocation
'  -1 -2     force use of particular protocol version
'  -4 -6     force use of IPv4 or IPv6
'  -C        enable compression
'  -i key    private key file for authentication
'  -m file   read remote command(s) from file
'  -s        remote command is an SSH subsystem (SSH-2 only)
'  -N        don't start a shell/command (SSH-2 only)

Download Solution
Download solution

Tool Screenshots: