christine407
New member
- Local time
- Today, 10:34
- Joined
- Jan 18, 2021
- Messages
- 13
Another duplicate thread, I apologize. I've read through as many examples as I could find. Even though the task seems very similar for most users, the approach seems inconsistent.
So this command works if I want to export the entire table!
The table that I'm filtering my form (gosh form vs table terminology is confusing) is COMTAWells. I probably should have titled it qryCOMTAWells, but next version perhaps. First Question, is it possible to change the title of H:\All_COM_TA_Wells.xlsx to include a date? I expect to make monthly backups, perhaps like YYYYMMDD?
Secondly, I'm having trouble implementing the code for exporting the filtered form (or filtered COMTAWells table). I've seen so many examples and I think I'm not quite following the QueryDefs and DAO characteristics to feel comfortable adding them into the script. Here is my current attempt!
So this command works if I want to export the entire table!
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "COMTAWells", "H:\All_COM_TA_Wells.xlsx", True
The table that I'm filtering my form (gosh form vs table terminology is confusing) is COMTAWells. I probably should have titled it qryCOMTAWells, but next version perhaps. First Question, is it possible to change the title of H:\All_COM_TA_Wells.xlsx to include a date? I expect to make monthly backups, perhaps like YYYYMMDD?
Secondly, I'm having trouble implementing the code for exporting the filtered form (or filtered COMTAWells table). I've seen so many examples and I think I'm not quite following the QueryDefs and DAO characteristics to feel comfortable adding them into the script. Here is my current attempt!
Code:
Private Sub btnFiltExcelExport_Click()
'This is the variable that will contain the where condition of the SQL string
Dim strSQL As String
'This is the entire starting table before being filtered (COMTAWells), alternatively, I've seen qrySQL = "Select * from" & Me.RecordSource, but not sure which is a better approach?
qrySQL = Me.RecordSource
'This will contain the entire SQL string (including the where condition)
Dim qryExport As String
If Me.Filter = "" Then
strSQL = ""
qryExport = qrySQL
Else
strSQL = Me.Filter
qryExport = qrySQL & "WHERE" & Me.Filter
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryExport", "H:\Filtered_COM_TA_Wells.xlsx", True
End Sub