As you can imagine, the ability to export data to Microsoft Excel directly from the SAP Business One client is a powerful tool. This can be used to get a good look at the data, run further summaries and calculations, and can even be used to import the data back into SAP B1 using the Data Transfer Wizard. Let's get started exploring this functionality that you will definitely enjoy.
Exporting to Excel Step-by-Step
To get started you have to decide which data are you looking to export. Data from SAP Business One can be exported from almost anything from query results, reports, and marketing documents - you can even export the main menu! Let's say you want to run a report and export the results to Excel for further calculations and analysis.
Run the report as you normally would. Make sure your results window has focus and look on the main icon bar along the top of your client for an icon that looks like an Excel icon. The icon is a sheet of paper with a green X written on it.
Once you click this button it will ask you to name your Excel file. The default file extension is plain text (TXT) for exporting. This of course can be opened with Excel but I usually prefer to have an Excel file straight out of the gate.
If you'd like an Excel file, make sure you select from the drop down, "Microsoft Excel 97-2003 workbook" or XLS. Select the directory you want to save the file to and click "Save". Keep in mind that if you are using Excel 2010 you will have to resave it in the new workbook format once you open it, if you choose to.
Before you zoom off to Microsoft Excel to look at your brand new Excel report, you will have to wait for another dialogue box to pop up. This one is asking you what you want SAP Business One to do with the currency symbols in your report. I have often went to find my Excel file before answering this step. When you try to open it before you do, you will get an error message.
The available choices are "Yes, to the same column" which will format your currency in Excel to include the symbol, "Yes, to a separate column" which will pull the currency symbol to it's own column, or "No" which will just include the numbers. If you primarily deal in one currency, answering No would be fine most of the time.
Make your choice and head over to Excel to further manipulate and analyze your data.