I've never tried this, but a client tells me that when they import from an Excel workbook with multiple sheets, Adagio will import only the sheet that was active when the Excel file was saved. They would prefer that Adagio prompt them for the sheet to use, like other programs do (such as Word)

They have created an Excel workbook with a list of checks to be imported into Payables Manual Check Batches. They enter each day's checks into a different sheet, so that they can import them into separate batches. Separate batches help them control the process.

Right now they have to open the Excel workbook after each import, change the active sheet, re-save, and then close the workbook, in between each import. It would improve efficiency if they didn't have to do this.

Any advice?

Steve

Have a separate workbook for each day with only a single worksheet in it? I'm assuming that these amounts are never edited, otherwise the active sheet would naturally be the one "on top" (which is the one that Adagio will import).

Why not just copy batches if the amounts aren't changing?

If the cheque amounts are always the same, why not import invoices with approriate dates and then do a system check run to select the ones to pay?

I guess I'm missing something about the reason they are doing what they do.

Hi Andrew

This is a company that processes checks and keeps the books for their clients (they are an accounting firm). Their clients fill out the spreadsheets. It would be cumbersome for them to email individual worksheets, and for my client to have to browse for each one when importing, at least as cumbersome as the current procedures. We are talking about a huge volume - about 40 datasets. Each keystroke is amplified by 40 or 400.

What about the thought of prompting for an individual sheet during import, as a File Information prompt?

Steve

Why place all the checks in different worksheets? If the companies are small enough to use a bookkeeping service, couldn't they get all their checks onto a single sheet? Excel's filters make it easy to isolate a day's transactions.

The same code is used for an import, regardless whether you are using Excel or CSV text files. There are no worksheets in a CSV file, so having the prompt would be awkward.

Perhaps you could write all the worksheets out to a single CSV file. Have you tried that?

Maybe I'll let my client chime in and explain if they think it will add to the discussion.

In the meantime, I will assume you are not open to the suggestion.

Steve

I'm running into a similar problem with a recent GridView implementation. I use the /XDW parameter to automatically export a workspace to Excel. I have AR9.0B setup to automatically process an import template that pulls in this GV export. The only really "manual" process is that we have to open up the Excel file (before loading AR) to delete the first sheet so that the import can proceed. I built a macro to partially automate the deletion, but it's still not as clean as it could be.

If we could define the desired sheet on the import template our problem is solved. (Or, if we could use a cmd line parameter to export a single view from a workspace, but that wouldn't solve Steve's problem.)

Ryan: You can export a single View. Use the command line parameter /XDV.

Steve: Touching the import process is fraught with the risk of breaking all the import templates that people have defined and spent hours getting right. Changes also have to be rolled through all the Adagio modules. I'm just exploring alternative methods of easing an inconvenience that don't require resources on our part. Input from the user about how they came to adopt this method of recording checks from their clients would be useful.

Not to hijack the thread, but the view uses GETEXTERNAL data calculations, so it must be run as part of a workspace in order to refresh the values. It's my understanding that I can't export just the one view while still refreshing the entire workspace.

Hi Ryan,

OK - you're hijacking the thread, but I'll answer anyway.

The GETEXTERNALDATA function does not require a View to be loaded with the data from the other table. That's a requirement of the GETLINKxxxx() call. The help text doesn't make this clear.