Macros: Level VI Program
We
have prepared several Excel macros for use with the Level VI
program. They have been designed to be used with some of the key
reports generated by the program. In particular, they make the
reporting process easier by formating the report: adding headers,
calculating subtotals and totals, sorting products and customers,
placing data into groups, and otherwise sprucing things up for
printing. If desired, you can also customize the macros for your
own special purposes.
Note
that because of changes made by Microsoft, AnyOrder's macros may not
run
on all versions of Excel. We are happy to provide the macros but
we can make no guarantees on whether they will run or not on your
version.
From time to time, we may add new
macros, and you might want to check back occassionally to see what's
new.
Download Macros
To download macros, click on the
link:
AnyOrder Macros
The
macros come in self-extracting zip file. Download the file.
Remember the directory where you save the downloaded file. Double
click on the name of the file. You'll be asked to enter a
password. Your password is found by starting AnyOrder and
selecting HELP >> OBTAINING REPORT MACROS. Enter the
password and also indicate where you'd like to extract the files to.
Information on adding AnyOrder's macros is found here: Adding Macros to Excel
Macros Available
Two
macros are currently available. The first creates an inventory
report which is broken down by category. The other creates
AnyOrder's most detailed consignment report. Here's more
information:
Inventory
Category Report.
If you have created categories for your products, this macro will
manipulate the raw data from an inventory report. It will sort
all of the products into their respective categories, and provide
subtotals of all columns in each category. Along with the
subtotals, it provides a grand total of all activity. The
columns provide a great amount of detail on sales activities including
starting inventory, the value of the inventory, the ending inventory
level, the value of the ending inventory, returns, damages, promos,
revenues, cost of goods sold and more. It also neatly formats
the report for printing purposes.
To
create the data for the macro, start AnyOrder. Select PRODUCT
TOOLS >> INVENTORY FUNCTIONS. You can either run a "Current
Inventory" or "Choose Starting and Ending Dates." Let the
inventory run and the "Sales Activity and Inventory Report Screen"'
will appear. Select FILE >> EDIT/PRINT FROM
SPREADSHEET (BY CATEGORY).
This
will open up Excel with the data in place. In Excel, select TOOLS
>> MACRO >> MACROS. If you following the
directions in Adding Macros to Excel, you'll see
"Sales_Inventory_Report_By_Category." Select it. Then
click on "Run." You'll see the report being
formatted. Wait until the process is completely finished.
When finished, you can review and/or print it.
Consignment
Report.
If you have more than one location where products are placed on
consignment, this is an invaluable report. When you run this
macro, it will group all activity for each of your customers
together. After grouping the customers, it will work its way
through each of the products and determine starting consignment
levels. It will calculate all sales, returns and
ending consignment levels for each consigned product under each
customer. At the same time, it formats the reports, add
spaces here and there for readability, and bolds subtotals and
totals. It would be very time consuming to manually do all the
calculations and formatting, but the macro enables you to do it in
minutes.
To
create the data for macro, select PRODUCT TOOLS >> PRODUCT
REPORTS. Then click on "Consignment Sales and Activity Report."
Indicate the dates you want the report to cover. Then click on
"Start Process." AnyOrder will generate the data and start
Excel.
In Excel, select TOOLS
>> MACRO >> MACROS. If you followed the
directions, below (Adding Macros to
Excel), you'll see
"Consignment_Report." Select it. Then click on
"Run." You'll see the report being formatted.
Wait until it is completely finished. When finished, you can
review
and/or print it.
Adding Macros to Excel
The
best procedure is to add the Macros to a worksheet in Excel called the
Personal.xls. When macros are placed in this worksheet, they are
available in all worksheets. In other words, as soon as you
create a new spreadsheet in Excel, you'll be able to access AnyOrder's
Macros. If you don't add the macros to Personal.xls, then you'll
need to import the macros each time you create a spreadsheet that
requires the use of the macros .
(Note that the
macros work in all versions Excel up to 2007, but you never know what
Microsoft
will do with their newest and latest version of Excel. )
Excel
2007 (and later) require a different procedure. If you have Excel
2007, go here.
Prior versions of Excel, use this procedure...
First
. . .Check for Personal.xls
First, check to see if you have a personal.xls file. Start
Excel. Click on the Window menu. Do you see it listed
there. If not, click Window >>Unhide. Do you
see it there? If you do see it, skip the next step..
A. Creating a personal.xls file
(If necessary)
If you don't have
a Personal.xls, do the following:
1.Start
Windows Explorer.
2. Navigate to
C:\Program Files\Microsoft Office\Office 10\XLStart. If you installed
Excel or Microsoft Office in another location, navigate to the \XLStart
folder in that location.
2. In the right
pane of Windows Explorer, right-click. Select New
>> Text Document.
3. Rename the
file as Personal.xls.
4. If Windows
asks you to change the file name extension, click "Yes."
5 .Start Excel
and open personal.xls.
6 . Select Window >> Hide.
Exit Excel.
7. When asked
if to save your changes, click "Yes." The new Personal workbook
will be available the next time you start Excel.
B. Add
AnyOrder's macros to Personal.xls
1.
Start Excel. Select Window >> Unhide.
2. In the Unhide dialog box, make sure that "personal.xls"
is selected. Click OK.
3. Select Tools >>
Macro >> Visual Basic Editor. When the Visual Basic Editor has started,
select File >> Import File.
4. Navigate to the folder
where you have saved AnyOrder's macros. The macros will have a .bas
extension, ie Consign.bas, Inventory.bas. Click
on the desired macro, and then click "Open."
5. Save the module, quit the
Visual Basic Editor. Hide personal.xls by selecting Window
>> Hide
6. Quit Excel and save your changes.
C. Using AnyOrder's macros
1.
Follow the special directions for each macro above. AnyOrder will
start Excel with the data in place.
2. Once
Excel has started with the data from AnyOrder, select
Tools >>
Macro >> Macros.
3. Select the macro you want to
run, and then click "Run."
Procedure for Excel 2007 (and later)
A. First, check to see if you have a
personal.xlsb file.
1. Start
Excel. Click on the Office Button (the big button on the upper
left of the Excel screen). Select OPEN.
2. Browse through the directories and find the XLSTART directory for
Excel 2007. Here's how to find it:
If you have Win XP (or an earlier
operating system). Note that "YourName" will often be your
name, like Cindy, Justin, etc.
It's the directory on the computer where your files are kept.
C:\Documents and
Settings\YourName\Application Data\Microsoft\Excel\XLSTART
If
you have Vista. Note that "YourName" will often be your
name, like Cindy, Justin, etc.
It's the directory on the computer where your files are kept.
C:\Users\YourName\AppData\Roaming\Microsoft\Excel\XLSTART
3. Once you find the directory, look and see if you
have Personal.xlsb. Do you see it listed
there? If you see it, skip the next step. If not, continue
with the next step.
B. Creating a personal.xlsb file
(If necessary)
If you don't have
a Personal.xls, do the following:
1.Start
Excel so that you have an empy worksheet showing
2.Click
on the Office button (the big button on the upper left of the Excel
screen) and select SAVE AS and choose "Excel Binary Workbook."
3. Browse to the XLSTART directory as described in "A" above.
4. Save the
empy worksheet as Personal.xlsb. (Excel will automatically add
the .xlsb.) You're ready to move on.
C. Add
AnyOrder's macros to Personal.xlsb
1. If
you've
found or created Personal.xlsb, it will probably be showing on your
Excel screen. If not, open it by doing the following:
- Start
Excel. Click on
the Office Button (the big button on the upper left of the Excel
screen). Select OPEN.
- Browse
through the directories and find the XLSTART directory for Excel
2007. (See "A" on how to find XLSTART above.)
2. Now
that you have Personal.xlsb showing on the Excel screen, we'll need to
open up Visual Basic (built into Excel). To do that, you need to
have
a "Developer" tab on top of the Excel Screen. If you don't see
the
"Developer" tab (which is the usual situation), you'll need to add
it.
To add it, do the following:
- Click
on the Office Button (the big button on the upper left of the Excel
screen). Look at the bottom right of the menu that appears.
Right
next to "Excel Exit" you'll see "Excel Options." Click on the
"Excel
Options" button.
- On the
left side of
the dialog box that appears, make sure "Popular" is selected.
Then
look at the checkboxes. You'll see "Show Developer Tab in the
Ribbon." Place a checkmark here.
- Exit from
the menu. When you exit from the menu, you'll see a new
"Developer" tab.
3.
Click on the "Developer" tab. On the far left, in the ribbon
area, you'll see a "Visual Basic" button. Click on it.
4.
The Visual Basic Screen will appear. Select File >> Import
File from the menu.
5. Navigate to the folder where you
have saved AnyOrder's macros. The macros will have a .bas
extension, ie Consign.bas, Inventory.bas. Click
on the desired macro, and then click "Open."
6. Select File
>> Save Personal.xlsb. this will save your macro(s) to the
Personal workbook.
7.
Now select File >> Close and Return to Excel.
8.
At this point, we want to hide Personal.xlsb. so that it doesn't
appear every time you open Excel. To do so, select the View tab. You'll
find an option: "Hide" Select this option and the sheet
will be hidden.
9.
Quit Excel and save your changes.
C. Using AnyOrder's macros in
Excel 2007 (or above)
1. Follow the special
directions for each macro above. AnyOrder will start Excel with
the data in place.
2. Once Excel has
started with the data from AnyOrder, select
the "View" tab.
3. After selecting the "View" tab, click on the "Macros" button.
Then click on the "View Macros" menu.
4. From
the "View Macros" list, select the macro you want to run, and then click "Run."
AnyOrder is another carefully crafted software program
from:
.
Great Rift Software
 Innovation, Excellence and Service
|
