#3 Ouvrir d'autres fichiers

With Excel you can open directly documents with the suffixes .XLS, .CSV, .HTM or .HTML (Web). You clean out what you don't need and you can start using the data.

For other documents .TXT or .ANYTHING including .PRN you will use the Import Wizard.

If you have a printed document with data that you need it means that a .PRN file has been created to send it to the printer. Get a hold of this .PRN electronic file and use the Import Wizard.

Usually the columns of data (fields) are quite static. Rows (records) on the other hand can vary a lot (number of records, order of records). SUMPRODUCT formulas allow you to summarized the data and create AUTOMATED reports.

 Simple text formulas will help you reformat certain data (assembling name and surname, assembling regional code and phone number).

  #4 "Données/Données externes/Importer des données"

It is very easy to automate the import process of data residing in text files or Excel files.

#5 Fonctions et formules

On the local network

You can use a formula to bring into a spreadsheet data that is in another spreadsheet locally.

The basic formulas looks like this if the source spreadsheet and the destination spreadsheet are in the same directory, on the same hard drive:
=[otherspreadsheetsName.xls]SheetName!$A$1
If there is a space in the source sheet's name, the formula (with apostrophes) is this:
='[otherspreadsheetsName.xls]Sheet Name'!$A$1 

If the Excel source spreadsheet is not in the same directory, the formula looks like this:
='C:\TEMP\[therspreadsheetsName]sheet Name'!$A$1 

Using the WWW

Tired of sending the same Excel reports to tens of people each week. Here is a SIMPLE solution
='http://www.excel-vba.com/[excel-online.xls]Sheet1'!D8

Open a new Excel workbook. Copy/Paste the formula above in any cell. Save it and re-open it. The result is 1,729.75 because the value in the cell of the workbook on the WWW is 1,729.75.
Click here to download the source

So you send a report to everybody with the WWW formula and each time they open it it is updated because you have updated the master that sits on the WWW.

  #6 VBA pour Excel (macros)

With VBA (macros) you can automate all the data entries presented above.

Even better you can use SQL within VBA to import data directly from central databases or ask the database people to create stored procedures that you will call with VBA and that will run on the server rather than your station. It is much faster.

  #7 Microsoft Query

It is very easy to automate the import process of data residing in text files or Excel files

#8 Importeur

There are many importers offered to you. They are all expensive, they require extensive training and they all try to do better than Excel as reporting application and they fail. The worst is Crystal Report.

EssBase (Hyperion) and its Excel add-in are among the best tools on the market to bring the data to the analysts and the report developers. If you consider buying Essbase ($25,000) don't buy their reporting and analyzing applications. USE EXCEL as your reporting and analyzing interface.

The add-in is a little heavy (a lot of useless functions) because its creators wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout.

You can use VBA (macros) (and it is very easy) to automate the importation of data hence developing multiple retrieves in a single spreadsheet to either assemble this information into a relational report or to create a "multiple retrieves/multiple reports" spreadsheet.

If your ERP system is SAP, the Interactive Excel add-in is also great tool to bring data into an Excel spreadsheet. You can bring the data from the data warehouse directly into an Excel worksheet by creating a simple MATRIX.

The add-in is a little heavy (a lot of useless functions) because its creator wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout.

There are all kinds of data importers that come as add-ins to Excel (like I-Apps, Crystal Reports). They are usually expensive, complex and like most applications designed to do it all they do everything somewhat like you would want them to do and they do a lot of things that you don't really need and that make the program complex. Find a good Excel-VBA developer and he will design a customized efficient importing tool that will do exactly what you need and NOTHING MORE.