Erlandsen Data ConsultingThis example workbook contains a simple custom calendar that can be used as a replacement for the MS Calendar Control that sometimes causes problems with missing references. The example file is for Excel 2007 or later, but the solution will probably work in Excel 97 and newer versions. Click here to download this file. Updated: [...]
The error message “A PivotTable report cannot overlap another PivotTable report” has annoyed me lately, especially when you try to update PivotTables in a workbook with lots of worksheets with multiple PivotTables. Excel is not very helpful since the error message doesn’t include any hints about where those conflicting PivotTables are. With the macros below [...]
According to the Excel Developer Reference for Office 2007 documentation, or well hidden in the built-in the VBA help file, you should be able to delete duplicate entries from a worksheet range using the RemoveDuplicates method like this: ActiveSheet.Range(“A1:F100″).RemoveDuplicates This method is new in Excel 2007 and will not work in older Excel versions. It [...]
OK, no magic involved in this tip. Saving a worksheet as PDF file will take some time, no matter what. You can’t do much about that, but when you are saving many worksheets as PDF files you want this process to go as quickly as possible. I was going to save around 3000 PDF files, [...]
Sometimes you don’t need to ask the user to select a list of files that you want to do something with. If you already know the source folder and types of files you want to retrieve you can use the function below. This function is also a simple replacement for the Application.FileSearch that is not [...]
Time to upgrade an old popular code example, this time as a more complete example almost ready to use. You will still have to make a few decisions about what you actually want to import. But most of the work is done, so enjoy! Below is the main macro that will do most of the [...]
Both of the function examples below can be very useful when you create a solution that is depending on input from a special worksheet. Instead of bothering the user by asking about the worksheet and workbook that contains the data source, you can use the functions below to find that special worksheet, as long as [...]
Both of the function examples below can be very useful when you create a solution that is depending on input from a special worksheet. Instead of bothering the user by asking about the worksheet and workbook that contains the data source, you can use the functions below to find that special worksheet, as long as [...]
The procedure below can be used from Excel to break links to objects in a PowerPoint presentation: Sub BreakLinksInPresentation(objPPT As PowerPoint.Presentation) Dim objSlide As PowerPoint.Slide, objShape As PowerPoint.Shape If objPPT Is Nothing Then Exit Sub For Each objSlide In objPPT.Slides Application.StatusBar = "Breaking Object Links in " & objPPT.Name & ", slide: " & objSlide.Name [...]
Calculating the difference between two dates is rather easy, just subtract one date from the other to return the count of days between the two dates. If you add a number to a date, you will get a new date that is the number of days into the future from the initial date. You can [...]