Go Premium for a chance to win a PS4. Enter to Win

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

Transferring access report to excel...

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
sirbounty
Asked:
sirbounty
  • 7
  • 5
1 Solution
 
Patrick MatthewsCommented:
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
 
sirbountyAuthor Commented:
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
 
Patrick MatthewsCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
sirbountyAuthor Commented:
Probably so...I'm trying to remember how to check that...back in a moment.
0
 
sirbountyAuthor Commented:
Ah - it appears that it 'is' a query... :^)
0
 
sirbountyAuthor Commented:
Still around matthewspatrick?
0
 
Patrick MatthewsCommented:
Sorry, I'll try to check in after dinner :)
0
 
sirbountyAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
sirbountyAuthor Commented:
Don't apologize...I'll take a look at this - hopefully before I head out...
Thanx again!
0
 
sirbountyAuthor Commented:
sorry for the delay -unexpected travel...

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

Any idea?
0
 
Patrick MatthewsCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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