Output query to excel

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

I would like to output a query in my database into excel but I want the
output to be iserted as a worksheet into an already existing excel file. How
do I do this?
 
you can export to a named range; or any range..using
docmd.transferspreadsheet.

I believe that the syntax is
Docmd.TransferSpreadsheet acExport, "TblName", "C:\MySpreadsheet.xls!
Sheet1$A2:B12"

or something close to that.
So then of course; you just need to open the workbook; add a worksheet
and then land in that range.

At least; I think that is the right place to look ;)

Right?

-Aaron
 
Ayo said:
I would like to output a query in my database into excel but I want the
output to be iserted as a worksheet into an already existing excel file. How
do I do this?
Click to expand...

Transferring Records to Excel with Automation
http://www.mvps.org/access/modules/mdl0035.htm
Sub sCopyRSToNamedRange() has the necessary code to specify a work
sheet name. You'll have to do some minor work in the VBA code to
update an already existing Excel file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Thanks Tony, that was precisely what I needed.

Tony Toews said:
Transferring Records to Excel with Automation
http://www.mvps.org/access/modules/mdl0035.htm
Sub sCopyRSToNamedRange() has the necessary code to specify a work
sheet name. You'll have to do some minor work in the VBA code to
update an already existing Excel file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Click to expand...
 
You need to look into this macro called TransferSpreadsheet. Read up on its
help and documentation in MS Access internal help. It will do exactly what
you're asking if you handle it correctly. TransferSpreadsheet can also be
activated through VBA code, which I recommend that you follow.
 
Ayo,

This code:

DoCmd.TransferSpreadsheet acExport, 8, "YourQueryOrTabel",
"YourFileName.xls", True, ""
DoCmd.TransferSpreadsheet acExport, 8, "YourQueryOrTabel2",
"YourFileName.xls", True, ""

creates "YourFileName.xls" (if not already exists) and creates 2
worksheets in that file (YourQueryOrTabel and YourQueryOrTabel2).
Excel is not opened in this process.

FK
 
Wait a second--

Tony's (obsolete) reccomendation uses DAO.
You will not be able to use DAO on Access 2000 or 2002-- as it is 'not
included by default'.

Furthermore-- I demonstrated code that lets you do this-- WITHOUT
CODE-- above Tony's posting.

So _I_ get the points in this thread.

These two remaining posts back up my claims.

Thanks

-Aaron
 
Of course you can use DAO in Access 2000 and 2002. All you have to do is
include the reference to the Microsoft DAO 3.6 Object Library. (You are
aware, I trust, that DAO is the default in Access 2007, whereas there's no
reference to ADO. Sure, it"s a different library name, but it's still
declared as DAO.)

And how can you say that you "demonstrated code that lets you do this--
WITHOUT CODE" Either it's code or it isn't. (HINT: DoCmd.TransferSpreadsheet
is VBA).

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wait a second--

Tony's (obsolete) reccomendation uses DAO.
You will not be able to use DAO on Access 2000 or 2002-- as it is 'not
included by default'.

Furthermore-- I demonstrated code that lets you do this-- WITHOUT
CODE-- above Tony's posting.

So _I_ get the points in this thread.

These two remaining posts back up my claims.

Thanks
 
[email protected] said:
Tony's (obsolete) reccomendation uses DAO.
You will not be able to use DAO on Access 2000 or 2002-- as it is 'not
included by default'.
Click to expand...

Obsolete? Rubbish.
Furthermore-- I demonstrated code that lets you do this-- WITHOUT
CODE-- above Tony's posting.
So _I_ get the points in this thread.
Click to expand...

Take all the points you want.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
TransferSpreadsheet is actually a macro ;)

I'm not so sure that I agree with you that DAO is the default in
Access 2007.

I'll glady demonstrate that it is _NOT_.

Furthermore; it hasn't been available-- by default in 2 out of the 3
last versions.
I will not stand for your incorrect information.

I also do not allow vendors to change directions a decade after the
fact.

Thanks

-Aaron
 
Tony;

Tony;

again-- I would ask you to stop stalking me.

You have put approximately 2,000 posts in these newsgroups, accusing
other people of being a a r o n k e m p f.

You put incorrect information up on a webpage-- and you did everything
in your power to screw with my life.
I have asked you a dozen times to stop following me around jerk.

I do not feel safe with your threatening posts.

Thanks for honoring my request to start following the stalking laws of
_BOTH_ the State of Washington and that so-called country of yours.

-Aaron
 
[email protected] said:
again-- I would ask you to stop stalking me.
Click to expand...

Again I'm not stalking you.
You have put approximately 2,000 posts in these newsgroups, accusing
other people of being a a r o n k e m p f.
Click to expand...

Wrong. The evidence as available in Google Groups links you to all
those postings using false names.
You put incorrect information up on a webpage--
Click to expand...

Post the URL and indicate what is incorrect information.
and you did everything in your power to screw with my life.
Click to expand...

You did everything to yourself.
I do not feel safe with your threatening posts.
Click to expand...

My posts are not threatening.
Thanks for honoring my request to start following the stalking laws of
_BOTH_ the State of Washington and that so-called country of yours.
Click to expand...

I am not breaking any stalking or harassment laws of any state in the
USA or province in Canada or federally in the USA or Canada.

You, of course, can report my behavior to the police or law
enforcement agency in the country, state or province of your choice.
I would ask that you do so.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
[email protected] said:
I'm not so sure that I agree with you that DAO is the default in
Access 2007.
Click to expand...
I'll glady demonstrate that it is _NOT_.
Click to expand...

I will gladly demonstrate that it is.

I just created a new accdb in A2007. I used the following code to
display the references.

Sub ViewReferenceDetails()
Dim ref As Reference

For Each ref In Access.References
Debug.Print ref.Name & " - " & ref.Major & "." & ref.Minor & "
- " & ref.FullPath
Next ref

End Sub

VBA - 4.0 - C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6\VBE6.DLL
Access - 9.0 - C:\Program Files\Microsoft Office\Office12\MSACC.OLB
stdole - 2.0 - C:\WINDOWS\system32\stdole2.tlb
DAO - 12.0 - C:\PROGRA~1\COMMON~1\MICROS~1\OFFICE12\ACEDAO.DLL

See the words DAO in there?
Furthermore; it hasn't been available-- by default in 2 out of the 3
last versions.
I will not stand for your incorrect information.
Click to expand...

Doug is correct.
I also do not allow vendors to change directions a decade after the
fact.
Click to expand...

Glad to see you can influence Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Tony;

You _ARE_ stalking me.

I do not feel safe. Basically; the _ONLY_ thing I need to do in order
to demonstrate stalking in the state of washington? It is that 'i do
not feel safe because of your actions'.

THANKS

Now-- regarding 'DAO'.

I just am not positive that it shows up _UNDER ALL FILETYPES_.

I know for a fact it doesn't as a matter of fact.
So if you would like to make a disclaimer 'using _CRAPPY_ filetypes;
then DAO is one of the default libraries-- even though it is not
called DAO any longer' -- then I would be _FINE_ with that statement.

Until then; you guys are spreading mis-information.

Now regarding this whole stalking thing.
From some random website
http://www.duhaime.org/LegalResourc...ticleId/95/Canadas-Criminal-Law-Stalking.aspx
-------------------------------------------------------------------------
Stalking is a crime which Canadian criminal law calls criminal
harassment. It is clearly defined at section 264 of Canada's Criminal
Code. The Code states that no person shall, without lawful authority
and knowing that another person is harassed (or recklessly as to
whether the other person is harassed):
XXXXXXX----- > BINGO!-------> repeatedly follow the other person, or
anyone known to them, from place to place;
XXXXXXX----- > BINGO!-------> repeatedly communicate with, either
directly or indirectly, the other person or anyone known to them;
"beset" or watch a place where the other person is visiting, lives or
works; or
XXXXXXX----- > BINGO!-------> engage in threatening conduct directed
at the other person or any member of their family.
The punishment for stalking can be as high as a five year jail term.

You are definitely 'repeatedly following me from place to place'.
You are definitely 'repeatedly communicate with me from place to
place'.

I will no longer put up with your CRIMINAL HARRASSMENT, Tony.

I am started a letter writing campaign; I will mail every police
department in two different countries-- making complaints against
you-- if that is what I need to do .

I DEMAND THAT YOU STOP STALKING ME.

Furthermore; you agreed earlier that you 'Posted a website containing
information about me'.

a) I did not give you a _RIGHT_ to follow me around; _STALKING_ me.
b) I did not give you the _RIGHT_ to run around telling everyone that
i'm full of shit and I don't work for Microsoft

YOU DO NOT WORK FRO MICROSOFT.

You are neither _VALUABLE_ or _PROFESSIONAL_.

You are the head of an organized campaign to stalk and harrass me.

Ever heard of RICO?
Ever heard of the _MAFIA_?

Your harrassment of me-- in this manner-- should result in PRISON time
for you.

I will not put up with your stalking; nor your mis-information.

You are not allowed to intimidate, harrass, follow me..

I am going to get a restraining order against you-- in several
different countries.

What happens after than depends on _YOUR_ behaivor.

Furthermore; I am working on copyrighting _MY_NAME_ and I will thus
sue you for copyright infringement if you _EVER_ say my name again.

Just because you're an idiot; it doesn't mean that I need to put up
with your harrassment.

NOW STOP FOLLOWING ME AROUND, PSYCHO

-Aaron
 
And for the record?

It is called 'Microsoft Access _DATABASE_ENGINE_ object library'.

So _NO_. DAO IS _NOT_ one of the default libraries.

Furthermore--

re:Wrong. The evidence as available in Google Groups links you to
all
those postings using false names.

Evidence in Google Groups
_LINKS_AARON_TO_ALL_THESE_POSTINGS_UNDER_FALSE_NAMES_?

WHERE DID YOU COME UP WITH THIS?

HONESTLY TONY-- WHERE DID YOU COME UP WITH THIS.

My evidence is at my lawyers office.

LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.
LEAVE ME ALONE, TONY. I WILL NOT PUT UP WITH YOUR HARRASSMENT.



-Aaron Kempf
MCITP: Database Administrator
 
where is the information?
How about-- where you ADMITTED-- hosting a website like that.

Why would you change your story from one thread to another?

OH LET ME GUESS-- BECAUSE YOU ARE A PART OF AN CRIMINAL ORGANIZATION
THAT IS WORKING IN A CONCERTED EFFORT TO SMEAR MY GOOD NAME.

Have you ever heard of the MAFIA, Tony?

I'm hoping to use those _MAFIA_ laws against you Tony.

THAT is where we are at.

NOW LEAVE ME ALONE, PSYCHO STALKER

-Aaron
 
Tony;

I'm not going to sue you.

I _AM_ going to get legal protection from you PSYCHO STALKER LEAVE ME
ALONE

-Aaron
 
Back
Top