From Access Query to Excel and Open the File

[hansjhamm]

Originally I posted this in the Access Forum and was told doing it this
way will not work.
Does anyone on this side know how to do this??
See Original Post below:

Thanks,

Hans


I have imported the query into Excel and able to save it, but I have
people that complained about having to find the file somewhere....
How would I run this query and NOT save to My Documents, but create a
new Excel file and just open it, leaving the save option to the user?
Also, can you do Excel cell formatting inside the VBA/Access code?

DoCmd.OpenQuery "ISS Open Position Query", acViewNormal, acEdit
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ISS
Open Position Query", "C:\Documents and Settings\Administrator\My
Documents\ISS Open Position Query", True, ""
DoCmd.Close acForm, "HR Reporting"

 

[aaron.kempf]

there's an easier stolution.. but does the query take a long time to
execute?

Docmd.openQuery
and then
Docmd.RunCommand acCmdOutputToExcel or soemthing crazy simple like that

i'll try to find the correct constant for you and I should return with
that info soon


-Aaron

 

[Pete_UK]

What's this? Aaron offering a solution ?? Wonders will never cease !!

Mind you, it is three and a half hours since he said he'd be back soon
....

Pete

 

[Ed Ferrero]

Hi Hans,

Usually, I open a new spreadsheet, use Data --> Import External Data --> New
Database Query, bring in the data from the Access db. Then Data --> Import
External Data --> Data Range Properties - make sure the Refresh data on file
Open checkbox is ticked.

Fomat as required, then save the workbook as a template.

Now, in Access, you just have to open the workbook...

Dim oApp As Object
Dim oWb As Object
Dim FileNmPath As String
Dim sCondition As String

' you can store workbook filename\path in a table
' if you have multiple excel templates with
' different reports
FileNmPath = "C:\Somewhere\workbookname.xlt"

Set oApp = CreateObject("Excel.Application")
With oApp
.Visible = True
Set oWb = .workbooks.Open(FileNmPath)
End With

Set oWb = Nothing
Set oApp = Nothing


Hope I have understood what you want to do.

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

 

[hansjhamm]

Thanks Guys for the HELP....I will check it out and let you know what
works here.

Hans

 

[aaron.kempf]

Pete;

maybe if i wasn't always correcting misinformation and mdb-wimps then
maybe I could give more helpful hints

I love helping people. i help a lot more people than i get credit for.

but im at war against microsoft and all mdb-wimps
Microsoft fired me THREE TIMES just because I had the balls to stand up
for what I beleive in.

Yes, SQL authentication STILL needs more help.
Yes, I still have a laundry list of a half dozen bugs i want fixed.
Yes, I still have to correct a hundred peoples' misinformation about
Access Data Projects

what is the URL again

blogs.msdn.com/access

read it and weep. if everyone on this newsgroup would LAY THE **** OFF
AND START HELPING PEOPLE WITH ADP THEN MAYBE --- JUST MAYBE -- things
would start getting better around here.

As it is; i have to FIGHT against a hundred people that spread
misinformation

-Aaron
ADP Nationalist

 

[aaron.kempf]

and seriously

you kids think that Excel is a DATABASE

ROFL

there is no RI, there is no inputMask, Formats; nothing for etl.. but
you kids use Excel for ETL _ALL_ the time

-aaron