Export query to Excel

[Michael Abrams]

Query name = OPEN ISSUES FOR REPS
Target - S drive - Folder - HMO ISSUES - LETTERS SENT & RESPONSES

Code: Select all

Dim strFileName As String

strFileName = "S:\HMO ISSUES - LETTERS SENT & RESPONSES”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OPEN ISSUES FOR REPS", strFileName, True

Receiving message:

Screenshot 2023-05-18 073653.png

Folder is not open and I have FULL permissions to view & write.

Can you see what needs to be tweaked?

Thank you for all you do

[HansV]

The value of strFileName is a folder path, you forgot to include the file name. Moreover, you have curly quotes at the end of the string (but perhaps that's just in this thread).
Try

Code: Select all

strFileName = "S:\HMO ISSUES - LETTERS SENT & RESPONSES\Export.xlsx"

or

Code: Select all

strFileName = "S:\HMO ISSUES - LETTERS SENT & RESPONSES\OPEN ISSUES FOR REPS.xlsx"

[Michael Abrams]

Thak you HansV. Works fine now.

[Michael Abrams]

I know I have done this before, but after 20 years, I can't remember what project it was.
2 queries:
OPEN ISSUES FOR BAY CLIENTS
OPEN ISSUES FOR VALLEY CLIENTS
Both have the same "where" clause: WHERE (((ISSUES.DATE_ISSUE_OPENED)<[ENTER DATE MM/DD/YYYY]))

Command button on form REPORT MENU code:

Code: Select all

Private Sub cmdLeaderReview_Click()

Dim strFileName1 As String
Dim strFileName2 As String

strFileName1 = "S:\HMO ISSUES - LETTERS SENT & RESPONSES\SUPV ISSUES\OPEN ISSUES FOR BAY CLIENTS.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OPEN ISSUES FOR BAY CLIENTS", strFileName1, True

strFileName2 = "S:\HMO ISSUES - LETTERS SENT & RESPONSES\SUPV ISSUES\OPEN ISSUES FOR VALLEY CLIENTS.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "OPEN ISSUES FOR VALLEY CLIENTS", strFileName2, True

MsgBox "SUPERVISOR REVIEW ISSUES HAVE BEEN EXPORTED", vbInformation

DoCmd.Close acForm, "REPORT MENU"

End Sub

The parameter box does pop up twice (once for each query). I believe I need to fix the where clause in the 2 queries but cannot find where I have done this before.

Thank you in advance.

Michael

[HansV]

Instead of a parameter prompt [ENTER DATE MM/DD/YYYY], place a text box on the form.

Name: txtDate
Format: mm/dd/yyyy
Caption of associated label: Enter date

Change the WHERE clause of both queries to

WHERE ISSUES.DATE_ISSUE_OPENED<[Forms]![REPORT MENU]![txtDate]

[Michael Abrams]

That's IT ! Thank you again HansV