Solved

Transferring access report to excel...

Posted on 2006-11-15
12
155 Views
Last Modified: 2013-11-28
I have an app that records a help desk-style log into an acess database (ado).

I created a report within Access to generate the output that I want.  Now, I'd like to blend that into my app.

Within access, I can simply open the report, go to Tools/Office Links/Analyze with Excel to accomplish this, but I want to do it from my code.

The reporting feature that I currently have is designed to loop through the recordset and drop the fields into Excel.
That works fine, but I need to duplicate that reporting from the Report that I've created in Access.  I need the easiest solution possible since this code is quite old and I'm trying to talk myself out of doing this anyway... ;)

Let me know if anything else is needed to get started.
Thanx.
0
Comment
Question by:sirbounty
  • 7
  • 5
12 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17949296
Hi sirbounty,

Is this report and all its calculations based on a query?  If so, then the easiest thing to do is probably just to use
the TransferSpreadsheet method to export the query results to Excel.  That will work in just one line of code,
and it has the side benefit of making the Excel data more analysis-friendly than the report dump would be.

Regards,

Patrick
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17950041
Hi Patrick - I'm not familiar with that method, but it sounds intriguing. :^)

It looks as if the control source for the details' items are all field names from the main table...  I suppose that means it's not coming from a query?  I've really got next to zero experience with Reports in Access and find them a bit confusing, admittedly... : \
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17950139
sirbounty,

:)

Did you create the report using the wizard?  If so, what did you select as the source for the report?  BTW,
TransferSpreadsheet works for tables too, not just queries.

Regards,

Patrick
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17950657
Probably so...I'm trying to remember how to check that...back in a moment.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17950683
Ah - it appears that it 'is' a query... :^)
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17981302
Still around matthewspatrick?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17981975
Sorry, I'll try to check in after dinner :)
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17982122
No rush - just wanted to make sure you were getting the notifs...  :^)
I'm out after Wednesday anyway, so it's okay if we don't resolve it this week too...
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17987930
OK, here's the syntax, assuming you're running it from Access:


With DoCmd
    .SetWarnings False
    .TransferSpreadsheet TransferType:=acExport, TableName:="NameOfYourQuery", _
        FileName:="c:\folder\subfolder\filename.xls", HasFieldNames:=True
    .SetWarnings True
End With


Even thought the argument is called TableName, it works with both tables and queries.  If HasFieldNames
is true, then you get a header row; if false, no header.

Sorry for the long wait,

Patrick
0
 
LVL 67

Author Comment

by:sirbounty
ID: 17988022
Don't apologize...I'll take a look at this - hopefully before I head out...
Thanx again!
0
 
LVL 67

Author Comment

by:sirbounty
ID: 18155190
sorry for the delay -unexpected travel...

Not running it from access - need to run it from within VB.

Any idea?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 18156210
Well, if it's an Access report, instantiate an Access.Application object, open that database,
and then do the With block above, just this time:

With AccessApp.DoCmd
...

Patrick
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

867 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

19 Experts available now in Live!

Get 1:1 Help Now