You will be outputting files from Sage into Excel



STARTUP INSTRUCTIONS – PLEASE READ THESE CAREFULLY.

To print these instructions, from the menu select: File - Print.

To run this tutorial you will need to have Microsoft Word and Excel on your own PC.

The tutorial consists of two files, a Word file and Sage data file named SAGEPIVOT.CSV SAGEPIVOT.CSV contains the raw data you are going to analyse via pivot tables. The Word file contains the instructions on how to create those pivot tables in Excel. Your next steps will be to:

1) Download SAGEPIVOT.CSV and the Word file from Accountingweb on to your own PC

2) Disconnect from the Internet.

3) Print out the Word file containing your instructions. (20 pages)

4) Start up Excel and, with the instructions before you, work your way through the tutorial.

The detailed procedures follow. If you have any trouble downloading either file from AccountingWEB, send an email to Rob Benson at rbenson@sift.co.uk and we will email you a copy by return.

DOWNLOAD PROCEDURE – WORD FILE

You have two files to download from Accountingweb. Take the Word file first, as follows:

Double click on the underlined Word file. The egg-timer appears. The file is 147 kb in size and will take about 20 seconds to download. When it appears on the screen, select: File - Save As from your menu. Wait a few seconds. The Save As box appears. First, select a suitable folder to save the file in. Then, save as: File name: SageTwo Files of Type: Word Document

After it has been saved, click on Back in your menu. You are returned to Accountingweb.

DOWNLOAD PROCEDURE – “SAGEPIVOT” FILE

SAGEPIVOT.CSV will come down in one of two ways – the easy way or the hard way. The easy way is when the “File transfer” box appears. The hard way is when the transactions start to appear on the screen. We don’t know why one appears instead of the other (if anyone out there can enlighten us, I’d be grateful). So, follow the instructions below:

Click on SAGEPIVOT.CSV. One of two things will happen.

Either: the File Download box appears. Accept the default “Save the file to disk”. The Save As box offers a file name of: SAGEPIVOT.CSV and files of type: Excel Comma Separated Values. Choose the folder on your PC that you want to save the file in. Then Save the file to disk, accepting these defaults (the file is 454kb in size).

This is the easy way. Now close down AccountingWEB, load up Excel, and start the tutorial.

[non-UK users, please read the last section of this document]

Or: the File Download box doesn’t appear and transactions begin to appear on your screen. As the file downloads the scroll bar on the right moves up to the top of the screen. Try gently pulling the scroll bar downwards. When you can pull the RH scroll bar down to the bottom of the screen and it stays there, the download will be complete. You should now be able to see the last record, which is account 9999, Mispostings account, a cancelled cheque value 1533.40, dated 30-06-99.

When the file has been downloaded, select: File: Save As. Wait a few seconds. The Save As box appears, offering File name = SAGEPIVOT_CSV and Files of Type = Web page, complete.

These are incorrect and you need to change them.

Change Web page, complete to Text file.

Also chop the filename down from SAGEPIVOT_CSV to SAGEPIVOT. Save.

Now start up Excel. Import the SAGEPIVOT.TXT file into Excel as follows:

File - Open. Open the folder that contains SAGEPIVOT.TXT You can’t see SAGEPIVOT.TXT.

Files of Type: (at the bottom) change from Microsoft Excel Workbook to Text Files

SAGEPIVOT now appears. Open it. The Text Import Wizard, Step 1 of 3 appears.

Change the Data Type from Fixed Width to Delimited. Click: Next.

The Text Import Wizard - Step 2 of 3 appears. Click on: Comma

Vertical lines appear between the columns Click Next. Step 3 of 3 appears.

The first column is highlighted. At the head of the column is the format which Excel assumes it will be in when it is imported – the “General” format. Excel assumes GENERAL for all fields.

In the top right of the screen are the possible Column data formats - General Text Date & Skip

General format is OK for all your fields except any date fields

Scroll across to the column headed DATE. The first date is 31/12/1998.

Click on the “General” heading of the DATE column. The column is highlighted

You will need to change the date format above this column from “General” to “DMY”.

In the Column Data Format box the “Date” option should have the default value DMY against it. This means that Excel is expecting that any Date fields it finds in the text file will be in Day/Month/Year format, which is the UK standard.

[Non-UK users - If DMY is not the default offered, select DMY from the list so that it is.]

Next to DMY click on: Date. The heading of the DATE column should now read DMY.

Click on Finish. Wait a moment. The file is imported into Excel.

That was the hard way. Now proceed with the tutorial.

USERS WHO DON’T USE THE UK’S DD/MM/YY FORMAT

The dates in SAGEPIVOT.CSV are set up in the DD/MM/YY format used in the United Kingdom. If your PC is set to another date format, the dates will look odd and you won’t be able to “Group” them into months.

You need to use the “Get External Data” option in Excel to import the SAGEPIVOT.CSV file and, while importing the file, to change the date format around. Do this as follows:

Start up Excel. Select: Data - Get External Data – Import Text file. Find the folder containing SAGEPIVOT.CSV. Change Files of Type from Text files to All files. SAGEPIVOT appears.

Click on SAGEPIVOT. The Import Wizard, screen1 of 3 appears.

Go to line 6 at the top of this page, and follow the instructions from there.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download