Creating report from excel data

Posted on 2012-08-20
Last Modified: 2012-08-21
Hi ,

Apologies for being a complete Newb for this question.

I have to generate a template in either word or excel (preferred) that I can generate a summary report for customers, which needs to populated from data in external excel files.

how do i create areas where I can select data from the external excel sheet (with multiple tabs) and import them as a chart or data that I can create a chart from?

The data I want to import is contained in something like the example attached.  Unfortunately I cannot get CSV or XML versions.

Please let me know if there are more specifics that I need - ideally though there are going to be multiple entries and the template will be reusable as there are multiple clients that will each need their own report from their own data.

I thought about setting up an SQL database but I think that would be messy and time consuming.  I have also tried an MS Query, but i got an error saying that there were no tables in my xlsx file??

Thanks in advance
Question by:eezar21
    1 Comment
    LVL 9

    Accepted Solution

    I can't see any examples attached. But in any case you can try the following VB Code:

    Sub Excel_QueryTable()
    Dim oCn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim ConnString As String
    Dim SQL As String
    Dim qt As QueryTable
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
    Set oCn = New ADODB.Connection
    oCn.ConnectionString = ConnString
    SQL = "Select * from [Sheet1$]"
    Set oRS = New ADODB.Recordset
    oRS.Source = SQL
    oRS.ActiveConnection = oCn
    Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
    If oRS.State <> adStateClosed Then
    End If
    If Not oRS Is Nothing Then Set oRS = Nothing
    If Not oCn Is Nothing Then Set oCn = Nothing
    End Sub

    Open in new window

    For details on the use of Query Tables and Troubleshooting, please look at the following article:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now