RE: [MS_AccessPros] Re: Exporting Access 2007 query to Excel 2007

What is the SQL view of [Expired Contracts]?
I don't see any rst.MoveNext inside your loop.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: no_reply@yahoogroups.com
Date: Thu, 4 Aug 2011 22:01:10 +0000
Subject: [MS_AccessPros] Re: Exporting Access 2007 query to Excel 2007

Hello all,

I'm using this code for the criteria for my query, and i've added in some coding that someone helped me with to export the query to Excel. However, when enter the number of days and hit the "run" command button, it doesn't open the query in Excel, instead it tells me that Access has encountered an error. Can someone please look at my code and see what is wrong with it?

I don't know anything about coding and this is a code that someone helped me with:

----RUN COMMAND BUTTON CODE------

Private Sub Command2_Click()
Dim db As DAO.Database, rst As DAO.Recordset
If IsNull(DLookup("[CONTRACT END DATE]", "[Expired Contracts]")) Then
MsgBox "There are no contracts expiring."
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "Main Menu"
Else
' Open a recordset on the expiring contracts
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [VENDOR E-MAIL], [CONTRACT END DATE], [E-MAIL], [CONTACT PERSON] " & _
"FROM ([CONTRACT INFORMATION] INNER JOIN [VENDOR INFORMATION] ON " & _
"[CONTRACT INFORMATION].[VENDOR ID] = [VENDOR INFORMATION].[VENDOR ID]) " & _
"INNER JOIN [EMPLOYEE INPUT INFORMATION] ON " & _
"[CONTRACT INFORMATION].[EMPLOYEE ID] = [EMPLOYEE INPUT INFORMATION].[ID] " & _
"WHERE [CONTRACT END DATE] < #" & (Date + Me.txtNotify) & "#")
' Loop through them all
Do Until rst.EOF
' Make sure we have a valid email
Loop
End If
If Not IsNull(rst![VENDOR E-MAIL]) Then
Call ExporttoExcel
rst.Close
Set rst = Nothing
Set db = Nothing

End If
End Sub
-------MODULE CODE FOR EXPORTTOEXCEL--------
Public Sub ExporttoExcel()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set objXL = CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open("C:\Documents and Settings\Station2\Desktop\Contract\Expired Contracts.xlsx")

Set xlWS = xlWB.Worksheets("Expired Contracts")

Set rst = CurrentDb.OpenRecordset("Expired Contracts")

xlWS.Range("A1").CopyFromRecordset rst

xlWB.Save

rst.Close
Set rst = Nothing

End Sub
----------------------

When I hit the Run command button, nothing it happens....I get an hourglass, and it never opens Excel.

Any help is appreciated.

Thanks!

Kat

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/