Excel Link
HelpID: 524122003
Excel Link exports a started count to an Excel file. The count can be directly entered into the spreadsheet then imported back into GP to update the Stock Count.
Excel Link can be opened from the Print Stock Tags window when a count is started, or via Transactions >> Inventory >> Excel Link.
Stock Count ID
Select a Count ID. The Setup option called “Require Starting Count Before Printing Tags” applies to this window. If the option is unmarked you will be able to selected an unstarted Count and export it to Excel. Importing can ONLY be done with a started count.
Exporting
Convert U of M
By default, the Captured Quantity, if included in the export, is shown in the Base Unit of Measure. You can use the U of M Map window to make the Captured Quantity be displayed in a different U of M. For example, you might want to show it in CASE rather than EACH.
This does NOT affect counting the items. The spreadsheet can record the count for an item in any U of M that is valid for the item.
Click the Expansion button to the right of Convert U of M to open the U of M Map window.
The U of M Map window displays each U of M Schedule that is on the Stock Count. For all items having the same U of M Schedule you can choose which alternate U of M to use for those items.
Include Captured Quantity
When marked, the Captured Quantity will be included in the export. Otherwise the Captured Quantity column in the spreadsheet will be zero.
Include Quantity Available
When marked, the Quantity Available will be added to each row in the export. Depending on the type of Item, and whether Multi-Bins is enabled, the following quantity will be shown:
- Item-Site Quantity Available
- Item-Site-Bin Quantity Available
- Lot Quantity Available
- Lot Bin Quantity Available
The Quantity Available is not displayed for serial numbered items because it is always one or zero.
Include Quantity On Hand
When marked, the Quantity On Hand will be added to each row in the export. Depending on the type of Item, and whether Multi-Bins is enabled, the following quantity will be shown:
- Item-Site Quantity On Hand
- Item-Site-Bin Quantity On Hand
- Lot Quantity On Hand
- Lot Bin Quantity On Hand
The Quantity On Hand is not displayed for serial numbered items because it is always one or zero.
Export Format
There are two export format options: (1) One Workbook for the entire count, and (2) One Workbook per site.
The new worksheet in Excel will be named automatically using a combination of the Stock Count ID, and if exporting a workbook per Site it will include the Site ID.
For example, if Stock Count TEST includes three sites, three workbooks will be created as shown below.
Click EXPORT.
The format (layout) of the spreadsheet cannot be modified or Excel Link will not be able to import the count.
You do not need to export the count before importing it. Exporting is used to create a file where the count can be entered. You can also create your own file manually as long as it has the required columns (see File Layout).
Using the Spreadsheet
For exported rows, it is important that only the Counted Quantity be changed in the spreadsheet. Do not make changes to the Item Number, Location Code, or Bin Number fields.
Below is a sample of what the export file will look like.
There must be a complete line for each counted Item or Item/Serial-Lot Number (as shown above for 100XLG). For each serial number (or lot number), the spreadsheet must contain Site, Bin (if multi-bin is enabled), Item Number, Serial/Lot Number, Counted QTY, and U of M.
When manually adding Items to the count spreadsheet, each row must contain information in the required fields. The spreadsheet must contain the following columns in the order shown in the File Layout section. The first row must contain column headers, but the labels in the column headers are not important.
Columns that are not used, such as Serial/Lot Number if you do not have serial/lot numbered items, must be present in the file but can be blank. For example, the file must have Column E, but it does not need a column header or any data.
You do not need to use the file Export funtionality. A similar file could be created using data from bardcode scanning guns as long as it has the required layout (see File Layout).
File Layout
The Import utility reads ONLY the required columns. Other columns may be blank, or used to contain other data as they are informational only and will not affect the import.
| Column | Column Header | Required | Notes |
| A | Site | YES | |
| B | Bin | YES | Only required if multi-bins is enabled. Column B must be present but can be blank. |
| C | Item Number | YES | |
| D | Item Description | NO | |
| E | Serial/Lot Number | YES | Only for serial/lot numbered items. The spreadsheet must contain a complete record (all required fields populated) for each serial or lot number counted. Each serial/lot number must be recorded on a separate row of the spreadsheet. |
| F | Capture Quantity | NO | |
| G | Counted Quantity | YES | |
| H | U of M | YES | It must be on the Item’s U of M Schdule. The count can be imported using any valid U of M on the Item’s U of M Schdule. Conversion to Base will be made during the import. |
| I | MFG Date | NO | The following fields are for Lot Numbers only. The fields may be populated when counting a NEW lot number (i.e. it does NOT already exist in GP).
The lot attribute columns are only imported for NEW lot numbers, otherwise they are ignored. |
| J | EXP Date | NO | |
| K | Lot Attribute 1 | NO | |
| L | Lot Attribute 2 | NO | |
| M | Lot Attribute 3 | NO | |
| N | Lot Attribute 4 | NO | This is a date field |
| O | Lot Attribute 5 | NO | This is a date field |
| P | QTY Available | NO | |
| Q | QTY On Hand | NO |
Importing
Navigation: Transactions >> Inventory >> Excel Link
Steps to IMPORT a count from a spreadsheet:
- Select a started count.
- Be sure the Counted Quantity is entered for at least one item. If there are no counted items nothing will happen when the spreadsheet is imported. CompleteCount automatically zeros the Quantity On Hand for items that are NOT counted (it assumes the count is zero).
- Select a workbook to import.
- Automatically add new Sites: when marked, the Site ID validation will be skipped. The Site ID in the spreadsheet does not have to exist in GP. When the Count is Submitted, new Sites will be created.
- Automatically add new Bins: when marked, the Bin validation will be skipped. New Bins will be added when the Count is Submitted.
- Click Import.
Validation
The first step in the import process is to validate the data in the data in the Excel file. CompleteCount will check the following:
- Item Numbers are the correct length, and are valid (exist in GP). Items may be included in the Import that are not already on the Count. CompleteCount will add them to the Count.
- Sites IDs are not blank, are the correct length, and are valid (exist in GP), unless “Automatically add new Sites” is marked, in which case they do not need to exist in GP. CompleteCount can create new Sites on the fly, create Item-Site assignments, and add the Item-Site to the Count.
- Bins are the correct length, and are valid (exist in GP for the Site), unless “Automatically add new Bins” is marked, in which case they do not need to exist in GP. CompleteCount can create new Bins on the fly, add then to a Site, create the necessary Item-Site and Item-Site-Bin records, and add the Item, Site and Bin to the Stock Count.
- Units of Measure are the correct length, and valid for the Item Number. The count can be recorded in any Unit of Measure that is valid for the Item Number.
- Serial/Lot Numbers are the correct length, and are present for Serial/Lot controlled items, and are NOT present for non-tracked items.
- The quantity is valid. It must be correct for the number of decimals places, and for serial-tracked items it must be 1 or 0.
If any validation check fails, the import will not continue—nothing will be imported from the spreadsheet. The spreadsheet must be fixed. A report will print displaying the error code(s) for each error found in the import file (see the Error Codes section).
If all data in the spreadsheet passes the validations, the import will begin.
If multiple workbooks were created, the import process can be repeated several times. Each time a spreadsheet is imported it will add the count from the new sheet to the existing count. Repeat this process until all count sheets have been imported.
For each row in the spreadsheet a “virtual tag” will be generated. The Item Number and Count from the spreadsheet row will be entered on the Tag. See the rest of the CompleteCount manual for information about working with Stock Tags.
You can view the imported count information with Stock Tag Reports (Reports >> Inventory >> Stock Tags Reports). Select the All Tags report.
If mistakes are discovered after importing a spreadsheet (such as the count was not correct, or a workbook was imported twice) the imported count can be Deleted and re-imported. Click the DELETE button on the Excel Link window to delete the imported information.
The Imported Files scrolling window shows the names of the files that have already been imported. If you attempt to import a file name that already exists, you will receive the following message:
A file with the name has been imported. Do you want to continue?
You may continue past this message. But be aware that the system will not prevent you from importing the same spreadsheet multiple times.
When the import is complete, go to the section on Submitting Tags.
Error Codes
The possible error codes are:
- Bin1: The Bin exceeds 15 characters, or is not upper case
- Item1: The Item Number exceeds 30 characters, or is not upper case
- Site1: The site exceeds 10 characters, or is not upper case
- SerLot1: The serial/lot number exceeds 20 characters, or is not upper case
- UofM1: The U of M exceeds 8 characters
- Site2: Site is empty or is not valid
- Bin2: Bin is empty (and Multi-Bins is enabled), or Bin is not valid for the site
- Item2: Item Number is empty, or is not valid
- SerLot2: Serial/Lot Number is required, and is empty
- SerQty2: Serialized Item has Quantity not equal to 1 (zeros are excluded and assumed to mean not counted/not found)
- CountedQty2: Quantity is not valid for the Item’s Quantity Decimals setting
- UofM2: U of M is not valid for the Item’s U of M Schedule
Deleting the Import
Until a count is submitted (see Submitting Tags), it can be deleted. This will not harm the Stock Count is any way. The workbooks can then be fixed and reimported.
Click the DELETE button on the Excel Link window to delete the imported count information. This will delete ALL Tags for the Count. Recall that the Import process creates a virtual tag for each row in the import spreadsheet. If you have already imported several other spreadsheets, and import a final spreadsheet that contains an error, and you delete the import at this point, you will need to re-import ALL of the prior spreadsheets.