none
Excel 2010 VBA - Methods & Performance of Data Import (Tables, CSV, XML, ...) RRS feed

  • Question

  • Hi everyone, the general question here is

    What is a really great input/load/output data management strategy for a VBA tool?

    Background

    I'm an amateur developer, making a tool for work which generally reads in input files (or loading a full 'schedule'), manipulates data, plots a timeline gantt chart of events, generates a 'schedule file', and is capable of overwriting input data and generating reports. The project is in its earliest stages, and only myself and a few coworkers will be using the tool.

    I am seeking the best-performing data import paradigm that is native to Excel 2010, with VBA procedures. With a fair bit of research on Excel, I still don't know the best way to store, manage, load into memory, etc. the inputs and outputs for this tool. My best idea so far is manage the input data in Excel Tables and generate a CSV/XML/TXT file for each input table, with the expectation that I would load the exported file into memory at runtime.


    Environment

    • Windows 7 Professional (company)
    • Yes we have SharePoint..
    • Excel 2010, VBA
    • No Internet access
    • Files stored in company network

    Assumptions

    • Tool will be set of GUIs, modules, & worksheets - input controls, output controls, front-end scenario 'settings', timeline plot, and input & output data viewer
    • Tool's file type fate is TBD, I am looking into add-in vs. macro wbk
    • 90% data can be organized into record/attribute 'tables'
    • Data records could range from 10 to perhaps 5,000, sometimes with 30+ fields (too early to tell)
    • ~15 inputs
    • ~5 inputs are CSV reports (from another software)
    • ~2 inputs are formatted text 'messages'
    • Remaining inputs (record/attribute), as well as output reports/files, will be managed in a way that is compatible with one of the resultant data import solutions identified here
    • Input files will not already be open at runtime
    • Only Excel 2010 and Notepad are available (no Access/other software solutions, please)
    • Several users will more than likely use Excel 2010 to edit input files

    VBA Tool Expectations

    • Clearly defined input & output file type(s) (ideally just one file type right?)
    • (Read) Load input data from input files into memory - quickly & efficiently
    • (Read) Load a single, previously-generated, 'schedule' file, if desired
    • (Write) View and edit input data and overwrite input files
    • (Write) Generate reports & schedule files in same format as inputs (probably?)

    Questions (assuming the above conditions)

    • What are some common, easy to manage, input/import/export strategies, with fast import ability?
    • What about Excel Tables? Is there a performance penalty from loading data from various Excel Tables (workbooks closed at runtime)? Are Excel Tables a viable solution for data management?
    • Is either CSV / XML / TXT the solution? Advantages/disadvantages?
    • Should SharePoint enter the dialogue?

    I appreciate any advice you have! Thanks so much, let me know if you need more information.

    Monday, November 2, 2015 10:41 PM

All replies

  • If you structure the data as a table you can use ADO to query Excel workbooks using SQL.  This works reasonably fast and you don't need to open the workbook to get the data.  This works well for small datasets like less than 1000 records.  I use this technique in Excel for some optimization routines.  I think a better approach is to use a database like Access, MySql, SQL Server...etc.  It is very fast. 

    http://www.fontstuff.com/ebooks/free/fsADOConnectExcel.pdf
    https://support.microsoft.com/en-us/kb/257819
    https://support.microsoft.com/en-us/kb/247412
    https://social.msdn.microsoft.com/Forums/en-US/50c36efb-32cd-4402-80cd-eb92721a70a1/update-excel-table-with-sql?forum=isvvba

    Here is a simple gantt chart I create in Excel to manage tasks.

    • Edited by mogulman52 Tuesday, November 3, 2015 2:27 PM
    Tuesday, November 3, 2015 2:30 AM