Excel VBA – Add apostrophe before a field for Access Import

Posted on | September 22, 2010 | No Comments

There are times, you try to import your data to Access but might not end successful due to some alpha numeric values in it. Access determines the type of field by accessing the first few rows of the column. If your data contains numeric values in the first few rows and alphanumeric in the rest of them, then access will try to import the column as a numeric field. But the import won’t be successful as the remaining part of the column contains the alpha numeric.

So, it is wise to make the column as text. This can be achieved by adding an apostrophe (‘) in front of each cell value. This process looks simple, but it turns out to be tedious when you have a large amount of data (say 1000 rows). To make your life simple, you can do a bit of VBA coding and complete the task in few seconds.

VBA Code:

Sub addApostrophe()
Dim rng As Range
Set rng = Selection
For Each c In rng.Cells
c.Value = "'" & c.Value
Next c
End Sub

How to use this macro:

1. Select the range where you want the apostrophe to be added
2. Copy this code and goto VBE by pressing ‘Alt + F11’
3. In the project Explorer – Select ‘ThisWorkbook’ and past the code on the code window
4. Press ‘F5’ and the apostrophe will be added to the selected cells.

Office 2007 – Adding Custom Colors to Color Picker

Posted on | September 16, 2010 | 1 Comment

The new color scheme introduced in Office 2007 looks impressive, but personally I feel,  it does lose a bit of important functionality when it comes to Charts. Excel Charts is an important tool in business areas to represent the facts graphically. Excel 2003 has a 56 color set, which allowed user to customize the color as needed, and it will be imparted to the Charts accordingly (i.e, coloring the series).

But in 2007, the new Chart engine doesn’t allow you to define the colors as needed, and it takes lighter tones of the primary 6 colors to be applied to the series. Let’s say, if you have 12 series in a Chart and you want 12 different colors, it’s not possible in 2007 as it applies the first 6 available colors from Themes to the Chart and rest of the 6 colors will be the lighter tones of the first 6 colors.  So, that’s where corporates look for an alternative/workaround to address this problem.

There is a provision to add custom color to the color picker, but it requires a little knowledge of understanding the structure of Office 2007 files. We are not going into the details, but will see the steps involved in getting the work done.

Steps for adding custom colors to the color picker:

Step 1: Editing the .THMX File

Make a copy of the .THMX file (Which is located in C:\Program Files\Microsoft Office\Document Themes 12).  Let’s say ‘Apex.THMX’.

Rename the ‘Apex.THMX’ to ‘Apex.THMX.ZIP’ and click yes when prompted to change the extension. Now we can open the file like any other ZIP file. Double-click the Apex. THMX.ZIP file and navigate to the theme folder.  You will see another theme folder and double click it to open.  (full path is \ Apex.THMX.ZIP\theme\theme).

Now you will see ‘theme1.xml’ file in the folder, and copy the file paste it on the root folder when you have the ‘Apex.THMX.ZIP’ file.

Step 2: Modifying the theme1. XML File

Open the ‘theme1.xml’ file in ‘Note Pad’ (you can open using any XML editor). Scroll through the end of the file and just above </a:theme> (which is the last line in the file),

Add the below code:

<a:custClrLst>
<a:custClr name=”Dark Grey”>
      <a:srgbClr val=”74716A” />
    </a:custClr>
    <a:custClr name=”Dark Teal”>
      <a:srgbClr val=”00685B” />
    </a:custClr>
    <a:custClr name=”Dark Green”>
      <a:srgbClr val=”728220″ />
    </a:custClr>
    <a:custClr name=”Dark Brown”>
      <a:srgbClr val=”846117″ />
    </a:custClr>
</a:custClrLst>

This adds a Custom Color section to the color picker and places a 4 rectangle chips. The names inside the double quotes will be displayed as a tool tip text in the color picker.

Note that the custom colors are represented in HEX values, so you need to have the HEX value for each color you needed.  You can find the HEX value using a small utilities like Pixie by Nattyware (http://www.nattyware.com).

Save the ‘theme1.xml’ file and close it.

Step 3: Replacing the Original theme1.XML File

You need to replace the ‘theme1.xml’ file located in \Apex.thmx.zip\theme\theme’. So, copy the modified ‘theme1.xml’ file and paste it inside ‘\Apex.thmx.zip\theme\theme’. Click ‘Yes’ when prompted to replace the file.

Close the zip file.

Step 4: Rename and Test the Modified Theme

Rename the file ‘Apex.thmx.zip’ to ‘Apex.thmx’ by removing the ‘.zip’. Click yes when prompted to change the file extension. 

Double-click the Apex.thmx file and it will open in PowerPoint. Add any  shape and choose Drawing Tools Format | Shape Fill to see the color picker. Added custom colors should be in the new “Custom Colors” section.

Office 2007 Compatibility Issues – ‘This is a pre-release version of the Compatibility Pack and can open pre-release Office 2007 files only. Do you want to check for a newer version of the Compatibility Pack?’

Posted on | September 6, 2010 | No Comments

Problem: When you try opening an Word 2007 file in 2003 using compatibility pack, you will be shown the error ‘This is a pre-release version of the Compatibility Pack and can open pre-release Office 2007 files only. Do you want to check for a newer version of the Compatibility Pack?’.

Cause: Files created other than Original verion of Office 2007 will cause this issue. (like the file created in ultimate edition, when opened throws this error).

Solution:  You need to recreate the file one of the Original version of MS Office.
Microsoft Link: Microsoft Article on this error

40 Deadly Shortcut Keys in Excel

Posted on | September 1, 2010 | No Comments

Key What it does
Home Beginning of the row
CTRL+Home Beginning of the region
CTRL+End End of the region
Shift + F11 Insert new worksheet
Ctrl+Enter Fill the selected cell with current entry
Ctrl+Delete Delete till end of line
Ctrl+Delete Fill down
Ctrl+R Fill right
Ctrl+F3 Define a name
Shift + F3 Paste a function
Ctrl+; Insert date
Ctrl+: Insert time
Ctrl+’ Copy a formula from above cell
Ctrl+` Toggle between formulas and values
Ctrl+Shift+” Copy a value from above
Alt+’ Display style
Ctrl+Shift+~ General style
Ctrl+Shift+$ Dollar style
Ctrl+Shift+% Percentage style
Ctrl+Shift+# Date style
Ctrl+Shift+@ Time
Ctrl+Shift+& Apply outline border
Ctrl+Shift+_ Remove border
Ctrl+9 Hide row
Ctrl+Shift+9 Unhide row
Ctrl+0 Hide colum
Ctrl+Shift+0 Unhide column
Ctrl+A Formular bar
Ctrl+- Delete selection
Ctrl+Shift++ Insert blank cells
Ctrl+. Move corners to selection
Ctrl+Shift+* Select region
Ctrl+Space Select colum
Shift + Space Select row
F8 and Shift f8 Extend selection
Ctrl+O Select all cells with comments
Ctrl+[ Trace reference
Ctrl+] Trace reference down
Ctrl+Shift+{ Trace all cells
Alt+; Select only visible cells

Office 2007 Compatibility Issues – _xlfn prefix error in Excel 2003

Posted on | September 1, 2010 | No Comments

Problem: When you try opening and Excel 2007 file in earlier version of Excel, you will notice an _xlfn prefix is displayed in front of a function in a formula. When the formula is calculated, it displays the #NAME? error value.

Cause: Microsoft Office Excel workbook contains a function that is not supported in the version of Excel that you are currently running. For Ex, you may have opened a workbook that contains the ‘IFERROR’ function, which is not supported in version of Excel earlier than Microsoft Excel 2007.

Solution: Remove the unsupported functions, or if possible, replace the unsupported functions with supported functions.

Office 2007 Compatibility Issues – Uninitialized ActiveX controls will not be opened in this version of Excel

Posted on | September 1, 2010 | No Comments

I have been working with Office migration from version 2003 to 2007/2010. I faced quite a few problems in my earlier stages and it took lot of time to figure out the exact solution for some simple problems. So, thought it would be useful for someone who also involved in the migration.

Problem: Macro enabled Excel 2007 workbook with ActiveX control throws error ‘Uninitialized ActiveX controls will not be opened in this version of Excel’ while opening in Excel 2003 with read-only access.

Solution: There might be some ActiveX controls present in the workbook. The workaround is to replace all ‘ActiveX controls’ with ‘Form Controls’. This will resolve the issue.

VLookup Function

Posted on | August 18, 2010 | No Comments

Function Description:

Vlookup stands for ‘Vertical’ lookup. This function searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

Syntax:

VLookup(lookup_value, table_array, column_index_number, range_lookup)
in simpler terms we can also say
VLookup(ItemtoFind, RangetoFind, ColumntoPickValueFrom, SortedorUnsorted)

Example:

A B C
1 Item Cost Discount
2 Brick 10 10%
3 Sand 25 15%
4 Cement 80 20%

Now we have the cost & discount table above and need to calculate the Total amount & the discount for the quantity listed in the below table. In this scenario, we can use ‘VLookup’ to calculate the Total amount & discount.

Lookup for Amount Calculation:

=G2*VLookup(F2,$A$1:$C$4,2,False)

This lookup function will look for the value ‘Brick’ within the range ‘A1:C4′ and returns the value ’10’, since we have provided column value ‘2’ in the formula. (I have used absolute reference to the range since the lookup range doesn’t change when we copy down the formula to other rows)

Lookup for Discount Calculation:

=H2-(H2*VLookup(F2,$A$1:$C$4,3,FALSE))

Final Result:

F G H I
1 Item Qty Amount Discounted Price
2 Brick 20 200 180
3 Sand 2 50 42.5
4 Cement 5 400 320

Welcome to MSOffice Gurus

Posted on | August 16, 2010 | No Comments

Hi All,

We are committed to share the knowledge that we have in the vastly used package, Microsoft Office.  The following posts will contain some extensive coverage of Tips from Word, Excel & PowerPoint and to some extent VBA. So, visit regularly to enhance your knowledge and drop in your comments. If you have any feedback then you can reach us at ‘reachguru@msofficegurus.in’.

« go back