[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

Creating report from excel data

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
0
eezar21
Asked:
eezar21
1 Solution
 
wasiftoorCommented:
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
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
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: http://vbadud.blogspot.com/2007/12/query-table-with-excel-as-data-source.html
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now