
|
Book Home Page Bloglines 1906 CelebrateStadium 2006 OfficeZealot Scobleizer TechRepublic AskWoody SpyJournal Computers Software Microsoft Windows Excel FrontPage PowerPoint Outlook Word Host your Web site with PureHost! |
![]() Thursday, November 05, 2009 – Permalink – Change Code to CommentsFast solutionWhen you're testing procedures, you can temporarily convert a block of VBA code to comments that will be ignored during a trial run. Doing so manually by inserting an apostrophe before each line of code can be a real chore. To simplify this task,
See all Topics excel Labels: VBA <Doug Klippert@ 3:42 AM
Comments:
Post a Comment
Wednesday, November 04, 2009 – Permalink – Paste is SpecialVersatile functionsIf the data you brought into Excel comes through as text rather than numbers, Paste Special can fix it.
What's So Special About "Paste Special"? Excel Paste Special function Pasting Using Paste Special See all Topics excel Labels: Tips <Doug Klippert@ 3:08 AM
Comments:
Post a Comment
Thursday, October 29, 2009 – Permalink – Hep MeHelp topic locationsThis from Ron de Bruin:
Help Context IDs for Excel See all Topics excel <Doug Klippert@ 3:09 AM
Comments:
Post a Comment
Sunday, October 25, 2009 – Permalink – Export Formatted SheetsAccess to ExcelAccess provides an easy way to export data to Excel through the Office Links feature. To use this feature, simply select a relevant database object and choose Tools>Office Links> Analyze It With Excel. The worksheet Excel creates includes some minor formatting applied to the field headings that appear in row 1. Some formatting in your original Access database affects the worksheet cell formatting as well. For example, if you're exporting from a datasheet, gridline and font attributes are carried over to Excel. If you use the Office Links feature to export data behind a form, text box shading and font properties are applied. The final result in Excel may not exactly match your Access data; however, you'll probably find that less work is required to get your Excel version of the data into an easily readable state. In Office 2007-10 it's External Data>Excel ![]() See all Topics excel Labels: Tips <Doug Klippert@ 3:07 AM
Comments:
Post a Comment
Thursday, October 22, 2009 – Permalink – Move Using AltOne sheet to anotherTo move data from one worksheet to another, highlight the data. Hold down the ALT key and move the mouse until the pointer arrow is on the border of the selection. Drag the selection down to the destination worksheet tab. When the arrow touches the tab, Excel switches to the desired worksheet. Now drag the selection to the correct position. Let go of the mouse and then the ALT key. To copy data hold down the CTRL+ALT keys and perform the steps above. BTW the screen will not scroll while you hold the ALT key down. See all Topics excel <Doug Klippert@ 3:56 AM
Comments:
Post a Comment
Saturday, October 17, 2009 – Permalink – Shortcut BordersKeyboard trickUse Ctrl+1 to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab. Now you can use the following key strokes (2002+):
![]() Shortcuts for Applying Borders Ctrl+Shift+7 will outline a cell without having to display the Format dialog. See all Topics excel <Doug Klippert@ 3:28 AM
Comments:
Post a Comment
Sunday, October 11, 2009 – Permalink – Update Excel on the WebAuto RepublishYou can save an Excel file as a Web page and makes it easy to update data in a worksheet that has already been saved to the Web. Here is how to save an Excel file as a Web page and set it up it for automatic updates:
Save Excel as Web Page DevX.com: Four Ways to Use Excel on the Web Penn State: Interactive Excel on the Web See all Topics excel <Doug Klippert@ 3:49 AM
Comments:
Post a Comment
Thursday, October 08, 2009 – Permalink – Copy PasteExcel tablesOne way to create a new table in an Access database from information included in an Excel spreadsheet is to select the pertinent data on the spreadsheet, including the field names. Copy the selection (Edit>Copy, or CTRL+C) Switch back to Access . With Tables objects being shown in the database window, choose Edit>Paste, or use the CTRL+V shortcut. Access will ask if the first row contains the field names and then will paste the information as a new database table. See all Topics excel Labels: Tips <Doug Klippert@ 3:46 AM
Comments:
Post a Comment
Wednesday, September 30, 2009 – Permalink – When 28 is 30How long is a month?When you use the banker's DAYS360 function to calculate the number of days between two dates, you can get an odd answer. If you use the DAYS360 function with a start date of February 28 and with an end date of March 28, a value of 28 days is returned. You expect a value of 30 days to be returned for every full month. (12*30=360) This behavior may occur if you use the U.S. method, also known as the NASD method, with the DAYS360 function. To work around this behavior, use the European method with the DAYS360 function. With the European method starting dates and ending dates that occur on the 31st of a month become equal to the 30th of the same month. To use the European method with the DAYS360 function, use the following syntax: =DAYS360(cell number of start date,cell number of end date,TRUE) Using FALSE or omitting the third parameter uses the U.S. method Support.microsoft.com An unexpected value with the DAYS360 function See all Topics excel Labels: Functions, Reference, Troubleshooting <Doug Klippert@ 3:19 AM
Comments:
Post a Comment
Thursday, September 24, 2009 – Permalink – Shut it All DownClose the spreadsheet and ExcelAs it comes out of the box, at this point, Excel 2007 is set to display spreadsheets on the task bar. No big deal 2003 did too. If, however, you only have one workbook open and want the application to shut down when you close the book you must make a little change. Just as before, you must deselect Windows in the Taskbar from the View Options. In 2007 it is located by clicking on the logo icon, then choose Excel Options. The Windows entry is on the Personalize screen ![]() See all Topics excel <Doug Klippert@ 3:44 AM
Comments:
Post a Comment
Thursday, September 17, 2009 – Permalink – Lock the BarnProtect your workJohn Walkenbach has put together an FAQ on Workbook/Worksheet/VBA protection. Spreadsheet Protection FAQ The Microsoft Knowledge Base article KB 293445 Has a list of references to protection information. Here is more information Overview of security and protection in Excel See all Topics excel <Doug Klippert@ 3:44 AM
Comments:
You might want to check out Mike Alexander's blog post about how easy it is to remove worksheet protection in Excel 2007.
Post a Comment
http://datapigtechnologies.com/blog/index.php/hack-into-a-protected-excel-2007-sheet/
Saturday, September 12, 2009 – Permalink – Declaring Multiple VariablesDeclare each oneWhen setting up a macro in VBA, if you want to declare multiple variables in one line of code, be sure to specify the type for each variable, even if the variables are the same type. Avoid code like the following: Dim strFName, strLName, strMI As String In such a case, only the last variable, strMI, is actually declared as a String type. The first two variables are designated by default as Variant data types. To correctly declare the three variables, you would use the statement: Dim strFName As String, strLName As String, strMI As StringSee all Topics excel Labels: VBA <Doug Klippert@ 6:47 AM
Comments:
Post a Comment
Friday, September 11, 2009 – Permalink – AutoShapesDrawing bar objectsKim Hedrich has put together a series of basic articles on AutoShapes for TechTrax. AutoShapesPart 1 - How to draw circles, ovals, squares and rectangles; also modifying fill and line colour AutoShapes Part 2 - Fill Effects AutoShapes Part 3 - Shadows and 3-D AutoShapes - Text Inside a Shape See all Topics excel <Doug Klippert@ 3:13 AM
Comments:
Post a Comment
Thursday, September 10, 2009 – Permalink – Trouble with Save?Also great notification serviceYou may experience issues when you try to save a Microsoft Excel file if one or more of the following conditions are true:
Receive Free Email Alerts every time Microsoft Publishes NEW Support or Knowledge Base Articles! kbAlertz.com is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies, you subscribe to.kbAlertz See all Topics excel Labels: Troubleshooting <Doug Klippert@ 3:50 AM
Comments:
Post a Comment
Wednesday, September 02, 2009 – Permalink – Dynamic TabsChange tab names automaticallyChanging the names of tabs is easy, just double click the tab or right click and choose rename. Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet. Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End SubAllen also has some error checking code on his site: Dynamic Worksheet Tabs Dick Kusleika suggests another way using a change event: Naming a sheet based on a cell See all Topics excel <Doug Klippert@ 3:33 AM
Comments:
Post a Comment
Tuesday, August 25, 2009 – Permalink – Charting ToolsSample graphsEd Ferrero has a number of sample Excel charts and tools. One he calls a Wind Rose: How to build a wind rose. Converts compass directions to degrees and radians, and plots a nice-looking chart. Ferrero Consulting Pty Ltd: EdFerrero.com/Charting See all Topics excel <Doug Klippert@ 3:05 AM
Comments:
Post a Comment
Saturday, August 22, 2009 – Permalink – Self HelpGet started in the right directionThe Office of Technology Services of Towson University, located in Towson, Md., provides Self-Help Training Documents for many applications. They are available for many levels of knowledge. They’re clean, clear, and concise.
See all Topics excel Labels: Tutorials <Doug Klippert@ 3:06 AM
Comments:
Post a Comment
Thursday, August 20, 2009 – Permalink – Still More FunctionsNever EnoughLaurent Longre Has put together an Excel add-in with 65 more functions. Here are a few you might find useful: MOREFUNC.XLL, 65 add-in worksheet functions See all Topics excel <Doug Klippert@ 3:14 AM
Comments:
Post a Comment
Friday, August 14, 2009 – Permalink – Digital SignaturesHow do I know it's real?If you find a need to provide some sort of certification that your document has not been tampered with and is the rel thing, you might consider a digital signature. This Microsoft Support article discusses the process. What is a digital certificate? Here's information for Excel. Digital Signatures for Excel See all Topics excel <Doug Klippert@ 3:58 AM
Comments:
Post a Comment
Thursday, August 13, 2009 – Permalink – Scroll RestrictionsWithout ProtectionYou can protect a spreadsheet so that data is shielded from inadvertent entries. There is another way to set up a scroll area that does not involve protection. Open the Control Toolbox (right-click any existing tool bar) and click on the Properties icon. In the Scroll Area text box, type the scroll area range, or type the defined Name for the range. To cancel the Scroll Area restricted range, clear the Scroll Area text box. ![]() If you are using Office 2007, the Property icon is on the Developers tab. ![]() Multiple areas can be selected using Protection, but only one area is allowed using the Scroll Property. See all Topics excel <Doug Klippert@ 3:29 AM
Comments:
Post a Comment
Monday, August 10, 2009 – Permalink – Military ClipartThousands of itemsIf you find the need for Armed Forces photos and art, here is the place to look. Regardless of your opinion about their present mission, the military does present a spectacular visage. ![]() "06/17/06 - An F/A-18E Super Hornet aircraft sits at the ready as storm clouds pass overhead aboard the Nimitz-class aircraft carrier USS Ronald Reagan (CVN 76) in the Philippine Sea June 17, 2006. HqDA.Army.Mil - Clipart See all Topics excel Labels: Graphics <Doug Klippert@ 3:02 AM
Comments:
Post a Comment
Wednesday, August 05, 2009 – Permalink – Large Text FilesSplit between worksheetsWhile this problem is alleviated in Excel 2007 with its 1,048,576 rows by 16,348 columns, The old XL versions are still here. Text files with a large number of records are better handled in a program like Access. Having said that, there can be times that these lists must be imported into Excel. If the file has over 65,536 records, the data will not fit on a single worksheet. Here's a Microsoft Knowledge Base article with the macro code needed to bring oversized text data into Excel and split it into multiple worksheets: Importing Text Files Larger Than 16,384/65,536 Rows Notice the code about 17 lines from the bottom of the macro. 'For xl97 and later change 16384 to 65536. Also, after import, the data must be parsed. Use Data>Text to columns. If you have not worked with macros before, Dave McRitchie has a tutorial: Getting Started with Macros and User Defined Functions See all Topics excel <Doug Klippert@ 3:13 AM
Comments:
Post a Comment
Thursday, July 23, 2009 – Permalink – Excel VBA Help FileDownload from MicrosoftHere is a downloadable compiled Help file covering the vagaries of Excel VBA. You can use this file without having to, necessarily opening the application. Excel 2003 VBA Language Reference See all Topics excel Labels: VBA <Doug Klippert@ 3:58 AM
Comments:
Post a Comment
Thursday, July 16, 2009 – Permalink – 65,539 QuestionThe eleventh place errorSure, you got a recall notice on your new car because the drink holder was the wrong size. Big deal, Excel 2007 also has/had a problem with some calculations. The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000. Excel 2007 hotfix package Calculation Issue Update See all Topics excel Labels: Troubleshooting <Doug Klippert@ 5:39 AM
Comments:
Post a Comment
Access-Excel-XML-HTMLTransfer dataXML makes data transferable between applications. Here is a tutorial with downloadable files. Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others. An introduction to Excel and XML data files Also: Some nice photos and calendar layout: Monthly calendar with photos See all Topics excel <Doug Klippert@ 3:56 AM
Comments:
Post a Comment
Wednesday, July 15, 2009 – Permalink – Restore DefaultsOffice 2003 redoTo reset the original settings in Office 2003, follow these steps. Make sure that you back up your files before you follow these steps.
Microsoft Office Diagnostics in 2007 replaces Diagnose and Repair: Howtogeek.com See all Topics excel Labels: Tips, Troubleshooting <Doug Klippert@ 3:28 AM
Comments:
Post a Comment
Sunday, July 12, 2009 – Permalink – Plain NumbersI'd Like to Make It ClearPlain Figures is a method of transforming statistical and financial data into figures, tables and graphs that people readily understand. Have you ever:
<Doug Klippert@ 3:02 AM
Comments:
Post a Comment
Wednesday, July 08, 2009 – Permalink – Polyglot PolynomialsToolPak TranslatorI was disappointed recently when I tried to look up Eric Desart's ToolPak translator. I found his site "niet beschikbaar." I won't use the boy and wet thumb story, but Ron de Bruin did spring up to save the day and make the download available. "Ever wanted an oversight of the Analysis-ToolPak Add-In functions, their descriptions, their arguments, their VBA and Procedure names, and all of this in your LOCAL language including translations versus the corresponding English names? Analysis ToolPak Translator See all Topics excel <Doug Klippert@ 3:03 AM
Comments:
Post a Comment
Tuesday, June 30, 2009 – Permalink – Thirtieth Condition FormattingThree is not always enoughPre-2007 Excel gives the user the ability to specify up to three conditions under Format>Conditional Formatting. If that is not enough, Frank Kabel and Bob Phillips of xlDynamic.com offer a free download that extends the conditions to 30! ![]() Extended Conditional Formatter Also see: Conditional Formatting (including 2007) See all Topics excel <Doug Klippert@ 3:09 AM
Comments:
Post a Comment
Sunday, June 21, 2009 – Permalink – Clip Art at HomeInstall moreDo you remember all of the clip art that was available locally with Office XP? When you have an Internet connection, you have access to the Office Online collection, but if you would like more clip art installed on your machine: A small amount of sample clip art images was included The 2007 Office systems and Office 2003 and is part of the "local collection" that is searched when you do not have Internet access to the Microsoft Office Online Clip Art and Media Web site. Office 2003 no longer included a media content CD with additional clip art. However, the Microsoft Office XP Media Content CD can still be installed locally or on a network share.Support.Microsoft.com How to add clip art to Clip Organizer in a 2007 Office system and in Office 2003 See all Topics excel Labels: Graphics <Doug Klippert@ 3:04 AM
Comments:
Post a Comment
Wednesday, June 17, 2009 – Permalink – VBA Variable ProblemsExplicit protectionIt's good practice to always use the Option Explicit statement in the beginning of your code modules to ensure that all variables are unambiguously declared in your procedures. With this process in place, you'll receive a "Variable not defined" error if you try to execute code containing undeclared variables. Without this statement, it's possible to mistype variable names, which would be interpreted as new Variant type variables. This could severely impact the results of your code, and you might not ever know it. If you do find a problem, tracking down where the error is can be a chore. Although you can manually type the statement into your modules, changing a setting in Access can ensure that the statement is always added to new modules.
See all Topics excel Labels: VBA <Doug Klippert@ 3:36 AM
Comments:
Post a Comment
Sunday, June 14, 2009 – Permalink – Julian DatesGregorian to/from Julian![]() Julian dates refer to the number of days from the first of the year and the number of days until the end of the year. The year -45 has been called the "year of confusion," because in that year Julius Caesar inserted 90 days to bring the months of the Roman calendar back to their traditional place with respect to the seasons. This was Caesar's first step in replacing a calendar that had gone badly awry. Caesar created a solar calendar with twelve months of fixed lengths and a provision for an intercalary day to be added every fourth year. As a result, the average length of the Julian calendar year was 365.25 days. Calendars by L. E. Doggett From Chip Pearson's site CPearson.com: "Many applications (especially mainframe systems) store dates in the Julian format, which is a 5-digit number, consisting of a 2-digit year and a 3-digit day-of-year number. For example, 24-August-1999 is stored as 99236, since 24-August is the 236th day of the year. Excel does not support Julian dates directly, but you can use them with only a few fairly simple formulas. US Naval Observatory has this definition (and a calculator): Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date. April 29, 2004 at 6:00 AM would be 2453854.75 See all Topics excel <Doug Klippert@ 3:43 AM
Comments:
Post a Comment
|