Macros: Level 6 or 7 Program
We have prepared several Excel macros for use with the Level 6 or 7 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 Microsoft is always making changes, 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
Three
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. (Macros are found under the "Developer" ribbon in new versions of Excel.) 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. (Macros are found under the "Developer" ribbon in new versions of Excel) 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.
Replace Catalog Numbers With Item Names. If you have lots of products, this macro may be helpful for two reports: 1) The first report is the Consignment Report (above). 2) The second is the spreadsheet that you can create showing the Inventory Transaction Data (created in the Product Information Database by selecting FILE >> MOVE TRANSACTION DATA TO SPREADSHEET.
In both of these cases, some or all of the entries are indicated by catalog numbers. If you have lots of products, you may wish to convert the catalog numbers to the full item names. This macro does that.
As a pre-requisite, you'll need to make a spreadsheet of your catalog numbers and item names. Here's how to do that:
1. Go into the product database (PRODUCT TOOLS >>
PRODUCT INFORMATION & CATALOG NUMBERS). Once in the product database, select FILE >> FORMAT & PRINT DATABASE FROM
SPREADSHEET. This provides you with a spreadsheet you with all of the product data. But we don't need all of the data so do the next couple of steps.
2. With the spreadsheet showing, delete all of the columns until you just have the catalog number and item name column. You should now have just the two columns.
3. Additionally, delete the top heading row. The final result is a spreadsheet with have catalog numbers and item names (no heading row). Now save the spreadsheet as: "CatalogTable.xls" NOTE: you are saving it as CatalogTable.xls NOT CatalogTable.xlsx. Remember save it as an XLS file.
4. Next create a directory on the C:\ Drive. Make the following directory and subdirectory: C:\AnyOrder\Reports. Once you've made this directory, copy the CatalogTable.xls into this directory. Why this directory? This is where the macro expects to find the table. If desired, you can place the table in a different directory, but you'll need to make the change to the macro. If you know how to edit a macro, find "C:\AnyOrder\Reports" in the macro and change it to whatever directory you decide to use.
You only have to do the steps above once. Once that's done, it's just a matter of running the macro.
Create a report. Let's use the inventory transaction data as an example. Go into the Product Database: PRODUCT TOOLS >> PRODUCT INFORMATION & CATALOG NUMBERS. Once in the Product Database, select FILE >> MOVE TRANSACTION DATA TO SPREADSHEET (ALL PRODUCTS).
An Excel spreadsheet appears with catalog numbers. You can use the macro to convert the catalog numbers into item names.
Before you run this macro, place your cursor on top of the column which has the catalog numbers. (A message in the macro will remind you to do this). In the Transaction Data spreadsheet, it's the first column.
Once you have the cursor there, select TOOLS >> MACRO >> MACROS. (Macros are found under the "Developer" ribbon in new versions of Excel) If you followed the directions, below (Adding Macros to Excel), you'll see "Replace_Catalog_Number." Select it. Then click on "Run."
What it does is to open CatalogTable.xls, copies it to a separate sheet with the transaction data workbook. Then runs down the catalog number column, looking up catalog numbers and replacing them. Once the replacements are made, it removes the CatalogTable data sheet. When it's finished all of the catalog numbers have become item names.
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 .
How you set things up depends on your version of Excel . . .
- If your version of Excel is dated 2007 OR later, go here
- If you have a version of Excel released before 2007, go here
Procedure for All Versions of Excel dated 2007 or 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 Vista or Windows 7. Note that "YourName" will often be your name, like Cindy, Justin, etc. It's the directory on the computer where your files are kept.
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 showingC. Add AnyOrder's macros to Personal.xlsb
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.
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:C. Using AnyOrder's macros in Excel 2007 (or above)
- 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.
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."
Procedure for All Versions of Excel Dated Prior to 2007
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.B. Add AnyOrder's macros to Personal.xls
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.
1. Start Excel. Select Window >> Unhide.C. Using AnyOrder's macros
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.
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."
___________________________________________________________________________________________________
AnyOrder is another finely crafted software program from:
Great Rift Software
Innovation, Excellence and Service