Need Lesson: Exporting Queries to Excel

[eepok]

Hey all,

Need some help here. I need a single-button/click export of a query to excel. I can't seem to find any built in function to do it and I really don't know VBA/SQL to make the function on my own (go go Access GUI survival!).

I know how to slap a button on a form and I've played with some of the VBA that comes with stock button code to make tweaks for other things, but have no clue how to tie in a query and export.

Help me gurus!

 

[pbaldy]

Look at OutputTo and TransferSpreadsheet in VBA help.

 

[eepok]

Look at OutputTo and TransferSpreadsheet in VBA help.

Perfect. =)

And might I add that I appreciate your hands off approach there. Makes me learn the syntax of the commands.

 

[pbaldy]

Thanks for noticing and appreciating! That is my style. Certainly post back if you get stuck and we'll sort it out.

 

[eepok]

Thanks for noticing and appreciating! That is my style. Certainly post back if you get stuck and we'll sort it out.

Ok, maybe I didn't get it *just* right. It seems that if there's no data in the fields designating the boundaries of the range to filter ([FilterStart] and [FilterEnd]), everything in the Query gets deleted-- fields, formulae, etc. I have to rebuild the query (go back-ups!) if I click without entering dates.

Here's the query SQL:

SELECT ContactInfo.FirstName, ContactInfo.MiddleInitial, ContactInfo.LastName, ContactInfo.Email, ContactInfo.StreetAddress1, ContactInfo.StreetAddress2, ContactInfo.City, ContactInfo.State, ContactInfo.Zip, ContactInfo.Country, ContactInfo.PhoneDay, ContactInfo.PhoneEvening, ContactInfo.MASInterest, ContactInfo.PHDInterest, ContactInfo.DateRecordCreated
FROM ContactInfo
WHERE (((ContactInfo.DateRecordCreated) Between [Forms]![optionsform].[FilterStart] And [Forms]![optionsform].[FilterEnd]));

Here's the VBA I slapped together.

DoCmd.OutputTo acOutputQuery, "ExportQuery", acFormatXLS, "MASAdvertExport.xls", True

What did I miss?

 

[pbaldy]

Offhand I don't see anything wrong with that. I would expect the query to return zero records, but you're saying it actually changes the query? That's a new one on me. You could try to get around it by testing those 2 textboxes before running the export, like:

If Not IsDate([Forms]![optionsform].[FilterStart]) OR Not IsDate([Forms]![optionsform].[FilterEnd]) Then
  MsgBox "Hey dummy, fill in the textboxes"
  Exit Sub
End If

DoCmd.OutputTo acOutputQuery, "ExportQuery", acFormatXLS, "MASAdvertExport.xls", True

I might further use a DCount on the query to make sure it returned records before exporting.

 

[eepok]

Offhand I don't see anything wrong with that. I would expect the query to return zero records, but you're saying it actually changes the query? That's a new one on me. You could try to get around it by testing those 2 textboxes before running the export, like...

The message box if/then has prevented the query from being wiped thus far in all of my testing. It looks like a win!

One last question, though: I don't, with my limited knowledge of VB, understand why it helps. Is there an interrupt in the code I'm not seeing?

I would think it would work like "If empty, then put up a message and ignore the rest of the code," but I just don't understand where that is represented in the code.

 

[pbaldy]

Frankly I'm not sure, because nothing should affect the actual SQL of the query. I tested your code, and leaving the form controls empty simply resulted in the export of just the headers (ie an empty recordset). No errors or anything, as I expected. What version do you have, and are you current on service packs?

 

[eepok]

Frankly I'm not sure, because nothing should affect the actual SQL of the query. I tested your code, and leaving the form controls empty simply resulted in the export of just the headers (ie an empty recordset). No errors or anything, as I expected. What version do you have, and are you current on service packs?

Using Access 2007 on XP Pro SP2. I cannot install any updates whatsoever because my hard drive is almost completely write-locked. (*sigh*)

It works, so that's good. But I wish I new why it wasn't.

 

[pbaldy]

I was testing with Access 2000, so I'm going to test on 2007 later and see if I can reproduce the error. It could be you've stumbled into a bug. If so, I'll report it and you'll be famous! Are these the basics?

1) query with form controls in criteria
2) form controls left blank
3) OutputTo using that query causes query SQL to be altered/deleted

 

[pbaldy]

As a follow up, I tested in 2007 and did not have a problem. If I got the basics correct above, can you post a sample db that fails?

 

[eepok]

As a follow up, I tested in 2007 and did not have a problem. If I got the basics correct above, can you post a sample db that fails?

Ok, uploading now. Sorry it's been so long, but I'm hoping you can still take a look at it. I've trimmed out a good deal of the DB and all that's left is what I think may be involved with the error as it's been reproduced in what's in there right now.

(Yes, it was a little premature to say it worked... the issue is still there.)

Oh ya, the issue is as follows:
In the form present at startup, press "options"
In options, play with various combinations of the date boundaries. Eventually you'll get an error after hitting export. When you get that error, check the exportquery. You'll notice there's nothing in there... and just "select;" in the SQL view.

 

[pbaldy]

I experimented with various date combinations and even commented out the code that makes sure dates were input, and never experienced the disappearing SQL. Even with no dates input, the export completed without error (the Excel file was empty except for the header, as I would expect). "play with various combinations" is a little vague (I've tried 10-15). Is there a specific combination that will cause it to occur?

 

[eepok]

I experimented with various date combinations and even commented out the code that makes sure dates were input, and never experienced the disappearing SQL. Even with no dates input, the export completed without error (the Excel file was empty except for the header, as I would expect). "play with various combinations" is a little vague (I've tried 10-15). Is there a specific combination that will cause it to occur?

This is precisely part of the issue. I can't figure it out. It just happened again, too. It's been sitting idle for while and I went to try it out with june1-june30 and boom... query wiped. I'm going to try to nail it down with some methodology and I'll let you know when I know more.

Thanks again for looking into it.