The Simple Machine

Have you noticed that in Excel 2002 or later even if you replace the source data for the pivot table, the pivot table retains items that do not exist anylonger (in the dropdowns)?

For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names.

To prevent missing items from appearing, or clear items that have appeared, you can used the following VBA code:

Sub DeleteMissingItems2002All()
‘prevents unused items in non-OLAP PivotTables
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

‘change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

‘refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub

If you have excel file with numerous worksheets and you want to get provide a index worksheet with links to each worksheet, then you can utilize the following VBA code.

The goal is to make it much easier to navigate through the excel file with so many worksheets.

To

1) Create a new worksheet

2) List of the name of the worksheets in the excel in in column A with the hyper link.

Sub DebugPrintAllTabs()

Dim sht As Worksheet, i As Integer

ActiveWorkbook.Sheets.Add

i = 1

For Each sht In ActiveWorkbook.Sheets

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=”", SubAddress:= _

“‘” & sht.Name & “‘!A1″, TextToDisplay:=sht.Name

i = i + 1

Next

End Sub

To

1) Create a new worksheet

2) List of the name of the worksheets in the excel in in column A.

3) provide a hyper link to each worksheet in column B

Sub IndexAllTabs()

Dim sht As Worksheet, i As Integer

ActiveWorkbook.Sheets.Add

i = 1

For Each sht In ActiveWorkbook.Sheets

ActiveSheet.Cells(i, 1) = sht.Name

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 2), Address:=”", SubAddress:= _

“‘” & sht.Name & “‘!A1″, TextToDisplay:=”Hyperlink”

i = i + 1

Next

End Sub

Say you have multiple excel files (for example daily feeds) in the same format and want to import the data into MS Access. Instead of opening each file and copy pasting the data into Access, the easier way is to use the VBA script below.

Just make sure you create a folder where you keep your excel files and specify it in the code blow by replacing the following line 9.

Also, make sure if you are going to use the folder in the future that you remove the old files.

1. Private Sub Command3_Click()

2. Dim strFile As String ‘Filename

3. Dim strFileList() As String ‘File Array

4. Dim intFile As Integer ‘File Number

5. Dim filename As String

6. Dim path As String

7.

8. DoCmd.SetWarnings False

9. path = “C:\Test\”

10.

11. ‘Loop through the folder & build file list

12. strFile = Dir(path & “*.xls”)

13.

14. While strFile <> “”

15. ‘add files to the list

16. intFile = intFile + 1

17. ReDim Preserve strFileList(1 To intFile)

18. strFileList(intFile) = strFile

19. strFile = Dir()

20. Wend

21.

22. ‘see if any files were found

23. If intFile = 0 Then

24. MsgBox “No files found”

25. End If

26.

27. ‘cycle through the list of files

28. For intFile = 1 To UBound(strFileList)

29. filename = path & strFileList(intFile)

30. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, “tblClientMail”, filename, True

31. Next intFile

32.

33. DoCmd.SetWarnings True

34.

35. End Sub

Unhiding worksheets is a different story, however. Excel only allows you to unhide one sheet at a time. Naturally if you have multiple worksheets you want to unhide, this can be a very tedious task.

Sub UnhideAllSheets()

Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets

wsSheet.Visible = xlSheetVisible

Next wsSheet

End Sub

Sub UnhideSomeSheets()

Dim sSheetName As String

Dim sMessage As String

Dim Msgres As VbMsgBoxResult

For Each wsSheet In ActiveWorkbook.Worksheets

If wsSheet.Visible = xlSheetHidden Then

sSheetName = wsSheet.Name

sMessage = “Unhide the following sheet?” _

& vbNewLine & sSheetName

Msgres = MsgBox(sMessage, vbYesNo)

If Msgres = vbYes Then wsSheet.Visible = xlSheetVisible

End If

Next wsSheet

End Sub

To remove the “No Comments” link, edit index.php and archive.php (if you want to remove the link from categories and search results pages also) from your template folder, and remove this line:

Code:
<span><?php comments_popup_link(__(‘No comments’, ‘inove’), __(’1 comment’, ‘inove’), __(‘% comments’, ‘inove’), ”, __(‘Comments off’, ‘inove’)); ?></span>

To remove the date from the post pages, edit single.php and remove this line:

Code:
<span><?php the_time(__(‘F jS, Y’, ‘inove’)) ?></span>

The Snow Leopard Preview has some great features, including editing and rearranging pages in a PDF file. However, what you might not know is that you can take some number of images and create a single PDF out of those images.

To create a single PDF from multiple TIFF, JPG, or other supported file types, do this:

1. Open any PDF — single or multi page, it doesn’t matter — in Preview. You’ll remove it later, so any image will do.
2. Show the sidebar (either Shift-Command-D, or via the menu or toolbar button).
3. Drag the single page images you want to use to the sidebar. They will be formated one per page.
4. Select the PDF you opened in step one — use Shift-Click to select multiple pages, then choose Edit » Delete. Don’t worry, this image is not actually erased from your hard drive, just ‘deleted’ from the sidebar.
5. Selected File » Save As » PDF, and you’re done.

If you have a WiFi-equipped Mac connected to the Internet by a wired Ethernet connection or even a dialup modem (blackberry, iPhone etc), you can turn that Mac into a WiFi access point and share the connection with other WiFi-equipped Macs, PCs, smart-phones and other wifi enabled devices.

To make your Mac a WiFi access point, follow these steps on the computer that has the Internet connection:

Select System Preferences from the Apple menu, click the Sharing icon, and then click the Internet button. From the Share Your Connection From popup menu, select the way this computer is connected to the Internet: typically Ethernet, Modem, or Bluetooth.

In the To Computers Using section, select the AirPort check box. Click the AirPort Options button and assign your new network a name. Turn on encryption if you like, enter a password, and click OK. Back in the Sharing pane, click the Start button in the Internet Sharing section.

Your other computers should now see your new WiFi network. When you no longer want your Mac to serve as an access point, go back to the System Preferences Sharing pane and click the Stop button in the Internet Sharing section.

The iNove 404 error page does not fit with the rest of the theme; but it really easy to create an iNove-themed 404 error page.

You will learn how to change your default iNove 404 page (below left) to an iNove-themed 404 page (below right).

Start by locating the wp-content/themes/inove/ directory of your WordPress installation. Rename–rather than delete–the current 404.php file in case you later want to return to the default error page.

We’re going to base the iNove-themed 404 error page on the main index.php file. This way, we get the themed layout of the header, the sidebars, and the footer for free. Make a copy of the index.php file and rename the copy, 404.php.

The main section of the code in iNove’s index.php file loops through each post and displays its title and summary. This code begins with these lines:

<?php if (have_posts()) : while (have_posts()) : the_post();

update_post_caches($posts); ?>

<div class=”post” id=”post-<?php the_ID(); ?>”>

<h2><a class=”title” href=”<?php the_permalink() ?>” rel=”bookmark”><?php

the_title(); ?></a></h2>

and ends with these lines:

<?php _e(‘Sorry, no posts matched your criteria.’, ‘inove’); ?>

</div>

<?php endif; ?>

Locate this code in your new 404.php file and replace it with code that displays your 404 error message. You’ll need to format your 404 error message like a regular post using the following code.

<div class=”post”>

<h2>Oops!</h2>

<div class=”content”>

<p>You might have stumbled here by accident or the post you are looking for is

no longer here.</p>

<p>Please try one of the following:</p>

<ul>

<li>Hit the “back” button on your browser.</li>

<li>Return to the <strong><?php bloginfo(‘title’); ?></strong> <a

href=”<?php bloginfo(‘url’); ?>”>homepage</a>.</li>

<li>Use the navigation menu at the top of the page.</li>

</ul>

</div>

</div>

I’ve highlighted the code I use for my 404 error page in blue. My code displays the error message as the post title between the <h2> tags, and displays an unordered list of suggestions to recover from the error between the <ul> tags.

After substituting your own error message code, test your new 404 error page by visiting a post or page on your WordPress website that doesn’t exist.

There are tons of plugins on the web that let you add Google Adsense ads to your wordpress blog. But the catch is that you have to keep trying every plugin to see if they will do exactly what you want.

If you are simply trying to add Google Adsense to the side bar like problogger, you can do so very easily within minutes.

All you need to add Adsense to your sidebar is the default text widget available in wordpress.

Step 1: Login to your blog as administrator

Step 2: Click on Appearance

Step 3: Select Widgets

Step 4: Select the sidebar you want the Adsense to appear in.

Step 5: Add the Text widget to the sidebar

Step 6: Click on Edit widget

Step 7: Do not enter a caption

Step 8: Paste your Google Adsense code in the text area.

Step 9: Click done & Save changes.

You should now be able to see Ads from Google showing on your sidebar.

The iNove WordPress theme has the option to replace the WordPress search bar with a Custom Google Search which includes ‘Google Adsense For Serach’ support. However, if you want the search results to appear within your website rather than on googles page, you need to manually configure the theme. Here is how its done.

1. Login to your admin page and click Current Theme Options on Appearance tab.

Check Using google custom search engine tickbox and type in you CX name of your Google code inside CX textfield. For example: “partner-pub-1234567890123456:f69j39-flrj” without quotes and Save Changes.

2. Go to Editor in the Appearance tab

2.a. Edit header.php inside “templates” folder of current theme right after “<?php if($options['google_cse'] && $options['google_cse_cx']) : ?>”, change “yours” to your value.

<form action=”yours” method=”get” id=”cse-search-box”>

<div class=”content”>

<input type=”text” class=”textfield” name=”q” size=”24″ />

<input type=”hidden” name=”cof” value=”yours” />

<input type=”submit” class=”button” name=”sa” value=”" />

<input type=”hidden” name=”cx” value=”<?php echo $options['google_cse_cx']; ?>” />

<input type=”hidden” name=”ie” value=”yours” />

</div>

</form>

2.b. Edit index.php located inside root folder of current theme by placing this snippet after “<?php endif; ?>” at line 31, change “yours” to your value :

<?php if (“is_search()”) {

echo “<div id=\”cse-search-results\”></div>

<script type=\”text/javascript\”>

var googleSearchIframeName = \”cse-search-results\”;

var googleSearchFormName = \”cse-search-box\”;

var googleSearchFrameWidth = yours;

var googleSearchDomain = \”www.google.com\”;

var googleSearchPath = \”/cse\”;

</script>

<script type=\”text/javascript\” src=\”http://www.google.com/afsonline/show_afs_search.js\”></script>”;

}

?>

Test your search feature after these changes, and shoot me a note if you need further help.

With SQL, an alias name can be given to a table or to a column. This can be a good thing to do if you have very long or complex table names or column names.

An alias name could be anything, but usually it is short.

SYNTAX FOR TABLES

SELECT column_name(s)

FROM table_name

AS alias_name

SYNTAX FOR COLUMNS

SELECT column_name AS alias_name

FROM table_name

EXAMPLE

Assume we have a table called “Persons” and another table called “Product_Orders”. We will give the table aliases of “p” and “po” respectively.

Say we want to list all the orders that “Ola Hansen” is responsible for.

We use the following SELECT statement:

SELECT po.OrderID, p.LastName, p.FirstName

FROM Persons AS p,

Product_Orders AS po

WHERE p.LastName=’Hansen’ AND p.FirstName=’Ola’

The same SELECT statement without aliases:

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName

FROM Persons,

Product_Orders

WHERE Persons.LastName=’Hansen’ AND Persons.FirstName=’Ola’

As you’ll see from the two SELECT statements above; aliases can make queries easier to both write and to read.

SQL wildcards can be used when searching for data in a database. Wildcards can substitute for one or more characters during the search.

SQL wildcards must be used with the SQL LIKE operator.

The following wildcards can be used with the SQL LIKE operator:

EXAMPLES

Using the % Wildcard

1. Say we want to select the persons living in a city that starts with “sa” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE ‘sa%’

2. Next, we want to select the persons living in a city that contains the pattern “nes” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE ‘%nes%’

Using the _ Wildcard

1. Say we want to select the persons with a first name that starts with any character, followed by “la” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName LIKE ‘_la’

2. Next, we want to select the persons with a last name that starts with “S”, followed by any character, followed by “end”, followed by any character, followed by “on” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE ‘S_end_on’

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SYNTAX

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

EXAMPLES

1. Say we want to select the persons living in a city that starts with “s” from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE ‘s%’

The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

2. Next, we want to select the persons living in a city that ends with an “s” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE ‘%s’

3. Next, we want to select the persons living in a city that contains the pattern “tav” from the “Persons” table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE ‘%tav%’

4. It is also possible to select the persons living in a city that NOT contains the pattern “tav” from the “Persons” table, by using the NOT keyword.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City NOT LIKE ‘%tav%’

The BETWEEN operator is used in a WHERE clause to select a range of data between two values. The values can be numbers, text, or dates.

SYNTAX

SELECT column_name(s)

FROM table_name

WHERE column_name

BETWEEN value1 AND value2

EXAMPLES

1. Say we want to select the persons with a last name between “Hansen” and “Pettersen” from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName

BETWEEN ‘Hansen’ AND ‘Pettersen’

2. Next, we want to display the persons outside the range in the previous example, use NOT BETWEEN:

We use the following SELECT Statement:

SELECT * FROM Persons

WHERE LastName

NOT BETWEEN ‘Hansen’ AND ‘Pettersen’

NOTE

The BETWEEN operator is treated differently in different databases and may not produce results you are expecting. So, check how your database treats the BETWEEN operator before relying on it.