Erlandsen Data Consulting

Problems with Excel 2010 and the SmartView Addin

OPE — Sat, 16 Feb 2013 21:59:07 +0000

After installing the Oracle SmartView addin in Excel 2010, the Excel application started to behave rather strange. Problem #1 When quitting the Excel application while a workbook/addin with a password protected VBProject is open (not an unusual situation), the user would be prompted from 1 to more than 100 times to enter a VBProject password. [...]

This is a post from Erlandsen Data Consulting

Problems with Excel 2010 and the SmartView Addin

Custom Calendar For Selecting Dates From User

OPE — Sun, 13 May 2012 21:13:57 +0000

This 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: [...]

This is a post from Erlandsen Data Consulting

Custom Calendar For Selecting Dates From User

Zip codes and names for Norway

OPE — Sat, 24 Mar 2012 12:00:24 +0000

I’m really not sure if anyone outside Norway finds this list interesting, but here it is anyway… The download is based on the information from http://www.bring.no/144754/postnummertabeller Updated with new zip-codes 2012-03-24. Click here to download this file. Updated: 2012-03-24 Requires: XL2007 File size: 167 kB This is a post from Erlandsen Data ConsultingZip codes and [...]

This is a post from Erlandsen Data Consulting

Zip codes and names for Norway

Find PivotTable Reports That Overlap Each Other

OPE — Tue, 06 Mar 2012 19:09:42 +0000

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 [...]

This is a post from Erlandsen Data Consulting

Find PivotTable Reports That Overlap Each Other

Remove Duplicate Items From A Worksheet Range

OPE — Wed, 08 Feb 2012 20:55:34 +0000

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 [...]

This is a post from Erlandsen Data Consulting

Remove Duplicate Items From A Worksheet Range

Save Multiple PDF Files In Shorter Time

OPE — Tue, 01 Nov 2011 21:56:32 +0000

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, [...]

This is a post from Erlandsen Data Consulting

Save Multiple PDF Files In Shorter Time

Retrieving Filenames From A Folder

OPE — Sat, 29 Oct 2011 21:07:54 +0000

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 [...]

This is a post from Erlandsen Data Consulting

Retrieving Filenames From A Folder

Import Data From Multiple Workbooks

OPE — Wed, 26 Oct 2011 18:36:00 +0000

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 [...]

This is a post from Erlandsen Data Consulting

Import Data From Multiple Workbooks

Error Message “Data source reference is not valid”

OPE — Wed, 21 Sep 2011 19:28:19 +0000

You might have encountered this error message while trying to create a PivotTable, and scratched your head trying to figure out what is wrong with the source data you have selected. Especially if you have had no trouble creating a PivotTable before using the same or a similar data source range. Well, despite what the [...]

This is a post from Erlandsen Data Consulting

Error Message “Data source reference is not valid”

Retrieve A Worksheet Based On The Worksheet Contents

OPE — Thu, 21 Jul 2011 08:47:35 +0000

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 [...]

This is a post from Erlandsen Data Consulting

Retrieve A Worksheet Based On The Worksheet Contents