Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009

    Unanswered: Problems with Aggregating Totals/Formatting Differences: Excel 2007


    I have an Excel problem, but it also involves Access so maybe I should be posting there, not sure.

    Anyway, this excel sheet is a template used in order to aggregate and sum data for monthly reporting purposes. There are about 8 different sites entering this data into their own access database. Then the reports get saved as text files and sent to me where I then import the text files into excel in order for the numbers to be totaled. (i.e. totals page on sheet 1, site1 data imported to sheet 2, site2 data imported to sheet 3, etc.... )

    The existing template is based on logic that all the databases look identical when they are exported and the code to total the sums assumes that each cell from each different worksheet contains the same data point.

    However, each site probably has different versions of access so when the data gets imported to excel the formatting is slightly different for some sites which obviously affects the accuracy of the reporting.

    So I need to modify the Excel code so it searches for the right data point as opposed to a specific cell. I am at a beginner level in VBA, but have only really worked with it in Access, so I am not even sure where to begin with trying to figure out how to do this in Excel!

    Any help or suggestions would be appreciated!!

  2. #2
    Join Date
    Jan 2009
    Kerala, India

    Problems with Aggregating Totals/Formatting Differences: Excel 2007

    I think it is better if you ask your external sites to save the data in coma delimited text format and send to you. You can link this text file directly into Access as table and do further work in Access itself.

    Check this Utility Function for Exporting Table in delimited Text format:

    GetRows() Function and Exporting Data (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts