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
Tidak ada komentar:
Posting Komentar