Databases
StackOverflow2013
Postsdbo
POArchitecture Q - VBA Excel Macro or VS Tools For Office?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
    primarykey
    data
    text
    <p>I have requirements from our client where we basically have to 'parse' PDF files from various different sources.</p> <p>The solution we have come with, as 'phase 1' (as we have short time to market and will save them a huge amount of time) is</p> <p>1) manually use Able2Extract application to pull out the columns you need from the PDF file, and spit out an Excel file. This excel file is still very 'dirty' as it contains tons of header information, extra fields that we don't need, etc..</p> <p>2) run our application, feeding it this excel file, which will do the remaining of the cleanup. It takes this 'dirty' Excel file and then gives them a very clean excel file which just has the 3 or 4 columns they need all lines up very neatly.</p> <p>The first solution we are exploring is using VBA/Excel for step 2). They take their dirty output, paste it in Excel, then run our cleanup macro. Excel is great for this sort of stuff - shifting around and scrubbing data that's already in an Excel spreadsheet. We did a proof of concept with one specific 'source' file, and it came out great. Tooks around half a day to develop this one 'scrubbing script'...</p> <p>Simple enough huh? Not really. This script only works for one specific file type from one specific source. We will have 10 different sources each with possible 3-10 different file types. That means in the end, we may wind up with a huge Excel macro that has 120 of these very specific 'scrubbing scripts'. So my worry is about long term maintainability here. We might also bump into files that we had never seen before that might 'break' our scrubbing script and have to do a quick re-deply / change to a scrubbing script... I've never used Visual Studio Tools for Office and have minimal experience with VBA Excel Macros - but it seems like this might be a good case here. </p> <p>Any words of wisdom from someone who might have done something similar to this before? Are huge VBA Macros like what could result here nightmares to maintain? Is VSTFO a good alternative that will give me that 'easy to shift/scrub data' functionality, but with scalability and robustness? To be honest; my first instinct was a pure .NET solution with dynamically compiled scripts pulled from the database, using our Syncfusion Excel API to do the cleaning/scrubbing... but perhaps this is overkill..</p> <p>Thanks for any advice...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USdferraro
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. USMichael Regan
      1. VTFavorite
    3. VO
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COdferraro, one "bad" use of Excel VBA is trying to use it as a service or server application - been there and done that. There are so many different ways that Excel can throw a dialog, leaving the application helpless until a user clicks on something (even if you've suppressed dialogs this can still happen but without the dialog to click!) that it becomes a whack-a-mole exercise trying to catch all the different situations where a dialog can interrupt the flow. Excel considers itself to be a desktop app and does not go quietly.
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. USJoel Goodwin
    2. COhmm.. I see.. so if we would like to do fully automation end-to-end, we couldn't re-use these 'scrubbing scripts' that live in the VBA macro? What about VSTFO? Would we be able to re-use the code then, in a Windows Service for example? Or are the VSTFO code have similar issues.. ? I'm guessing not, and that we should be able to use it in a service, but...
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. USdferraro
    3. COInvolving Excel (any reference to the Excel object model) in a server process is where the trouble lies. Typically it would manifest itself as a hanging process with no error message, but you can also get orphaned dialogs waiting for user input - I saw a scenario where dialogs for the Excel Solver could not be suppressed. It may have improved with recent versions of Excel (I haven't worked on anything later than Excel 2003). You can still do this, but be prepared to expend some time on maintenance.
      singulars
      1. POArchitecture Q - VBA Excel Macro or VS Tools For Office?
      1. USJoel Goodwin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload