Macro Specifications for Shorefront Y LINKS import into Sage Fund Accounting
The Macro will be designed as a button that, when pressed, will create two identical import tabs within Microsoft Excel based off the information contained in the “Payment Method” column of the original data file.
If the Payment Method column contains the text “Credit”, create an import tab with Session ID “CCYYMMDD”.
If the Payment Method column contains the text “Cash”, create an import tab with the Session ID CRYYMMDD.
The date portion [YYMMDD] of the Session ID’s on both import tabs will be the system date.
If the Macro encounters a blank cell in the “Payment Method” column, it should ignore all of the associated data in that row.
On each import tab, the Macro will default the following fields to columns based on specific criteria: Session ID, Session Status, Session Date, Session Description/Document Description, Effective Date, and Bank Deposit number.
• Session ID for each import tab will contain the specific information mentioned above, depending on the criteria in the “Payment Method” column.
• The Session Status column will be the same on both import tabs. This column will contain “BP” for Batch-To-Post, on each transaction line.
• The Session Date column will be the same on both import tabs, and will be driven by the system date. All of the transactions will contain the same Session Date.
• The Session Description and Document Description columns will contain the same information: “LINKS System Import.” This will be identical on both the cash and credit import tabs.
• The Effective Date will be based off of the “Date” column, which in turn will need to be modified by the macro to a MM/DD/YYY format. The date currently residing in the “Date” column will make up the “Effective Date” on both import sheets.
• The Bank Deposit number column will draw its information from three columns: The “Till” column the “Date” column and the “Payment Method” column, combining the three to read LKSA020212CR (as a credit card transaction example). If it is a credit card transaction a CR will be added, which a CA will be added for cash at the end of the bank deposit number.
Note: The four columns listed above will begin directly after the “Credit” column on both of the import tabs.
Both the “Credit” and “Cash” import files that the Macro creates will be one sided transaction entries. In other words, there will only be a “Credits” column with no corresponding “Debits” column.
The data for this column on the import sheets will be drawn directly from the “Amounts” column on the data sheet. When the macro is reading the numerical amounts, it cannot have any currency indicators or commas. For example, 2100.45 is an acceptable numerical import while $2,100.45 is not and the import will fail as a result. Offsets will be applied during the import into Sage.
The Transaction Description column on each import file will be a combination of the “Number” “Code” “Description” “Cust Number” and “Cust Name” columns. The macro will need to read the information contained in each column, separating it with a – when the macro begins to read the next column. However, due to Sage Fund Accounting length restrictions, the macro needs to truncate this information at 75 characters. An example of what the Transaction Description column will need to look like: 1-S002-Group-1138-ELINA BERRY, making sure this is kept under 75 characters on both import sheets.
The macro will need to add two additional columns to the end of each import tab. These columns are “Transaction Source” and “Transaction Type”. For each entry, the “Transaction Source” column will contain CR, and the “Transaction Type” column with contain a N. This information will be the same on both import tabs.
Because Sage required that Cash Receipts need to have full segment coding, the Macro will need to create 5 columns on both import tabs. These columns will be “Fund” “Department” “GL” “Prog” and “Grant”.
• The “Fund” column on both import tabs will be the same for every transaction and will default to 01.
• The “Department” column on both import tabs will default to 480 for all associated transactions.
• The “GL” column on both import tabs will default to 4710 for all associated transactions.
• The “Program” column will not default to identical values for each row on both import tabs. It will use each rows data within the “Code” column on the data file to populate the “Program” column on the import tabs.
• The “Grant” column on both import tabs will default to 12345 for all of the associated transactions.
Below are two screenshots of what the import tabs should look like after the macro has been run. These will be the columns that will populate each import tab, and the corresponding data that will match with each transaction from the original data file.
The below description is based off of a previously created workbook, that once the project has been accepted will be sent to the programmer.
13 freelancers are bidding on average $125 for this job
Hello, I'm experienced with VBA specially for MsOffice products. It'll be piece of cake. It would be great if I could provide you sample work. Please contact me.