访问 - 使用自定义文件名导出子表单过滤的结果 [英] Access - Export subform filtered results with custom file name

问题描述

我将筛选结果从我的子表单导出到Excel，并按需要命名Excel文件。这是我的代码：

  Sub XcelExport（）
 Dim Results As Recordset 
 Dim RecCount As Integer 
 Dim XcelFileName As String 
 Dim FilePath As String 
 Dim wb As Excel.Workbook 
 Dim XcelFile As Excel.Application 
 
'设置日期文件的名称
 XcelFileName =MySubform_Results_&格式（日期，dd / mm / yyyy）& .xlsx
 
'设置保存文件的目标文件夹
 FilePath = CurrentProject.Path& \& XcelFileName 
 
设置XcelFile =新建Excel.Application 
设置wb = XcelFile.Workbooks.Add 
'提取子表单记录源
设置结果= Forms！[MainForm]！ [MySubform] .Form.RecordsetClone 
 
与wb 
 XcelFile.ScreenUpdating = False 
'将字段名称添加到工作簿
对于RecCount = 0到Results.Fields.Count -  1 
 XcelFile.Cells（1，RecCount + 1）.Value = Results.Fields（RecCount）.Name 
 Next RecCount 
 
'将子表单结果复制到Excel文件
 XcelFile.Range（A2）。CopyFromRecordset结果
 
 .SaveAs文件名：= FilePath，FileFormat：= 51 
 XcelFile.ScreenUpdating = True 
 .Close 
结束
设置XcelFile =没有
设置结果=没有
结束Sub 
  

代码工作，有一个缺陷。当我再次运行它时，它会再次创建一个新文件，但是 .RecordsetClone 不见了，所以Subform的值不再被导出。除此之外，我发现代码工作很奇怪，只需看看»用wb«语句 - 我必须在某些命令上引用 XcelFile 或者他们没有工作，不管我是否已经在上面的代码中将xb设置为XcelFile（ Set wb = XcelFile.Workbooks.Add ）。我的代码有什么问题，有人有更好的解决方案吗？

解决方案

 所以这是最终的代码，希望对别人也是有用的。 
 
 Sub XcelExport（）
 Dim结果作为Recordset 
 Dim RecCount As Integer 
 Dim XcelFileName As String 
 Dim FilePath As String 
 Dim wb作为Excel.Workbook 
 Dim XcelFile作为Excel.Application 
 
'设置日期文件的名称
 XcelFileName =MySubform_Results_&格式（日期，dd / mm / yyyy）& .xlsx
 
'设置保存文件的目标文件夹
 FilePath = CurrentProject.Path& \& XcelFileName 
 
设置XcelFile =新建Excel.Application 
设置wb = XcelFile.Workbooks.Add 
'提取子表单记录源
设置结果= Forms！[MainForm]！ [MySubform] .Form.RecordsetClone 
 
与wb 
 XcelFile.ScreenUpdating = False 
'将字段名称添加到工作簿
对于RecCount = 0到Results.Fields.Count -  1 
 XcelFile.Cells（1，RecCount + 1）.Value = Results.Fields（RecCount）.Name 
下一个RecCount 
 
'将子表单结果复制到Excel文件并设置第一行的结果
 Results.Movefirst 
 XcelFile.Range（A2）。CopyFromRecordset结果
 
 .SaveAs文件名：= FilePath，FileFormat：= 51 
 XcelFile .ScreenUpdating = True 
 .Close 
 End with 
 Set XcelFile = Nothing 
 Set Results = Nothing 
 End Sub 
  / pre> 
I'm exporting filtered results from my subform to Excel, and naming Excel file as I want. Here's my code :
Sub XcelExport()
Dim Results As Recordset
Dim RecCount As Integer
Dim XcelFileName As String
Dim FilePath As String
Dim wb As Excel.Workbook
Dim XcelFile As Excel.Application

'Set name of file with date
XcelFileName = "MySubform_Results_" & Format(Date, "dd/mm/yyyy") & ".xlsx"

' Set destinaton folder of saved file
FilePath = CurrentProject.Path & "\" & XcelFileName

Set XcelFile = New Excel.Application
Set wb = XcelFile.Workbooks.Add
'Fetch subform record source
Set Results = Forms![MainForm]![MySubform].Form.RecordsetClone

With wb
XcelFile.ScreenUpdating = False
' Add field names to workbook
For RecCount = 0 To Results.Fields.Count - 1
XcelFile.Cells(1, RecCount + 1).Value = Results.Fields(RecCount).Name
Next RecCount

' Copy subform results to Excel file
XcelFile.Range("A2").CopyFromRecordset Results

.SaveAs Filename:=FilePath, FileFormat:=51
XcelFile.ScreenUpdating = True
.Close
End With
Set XcelFile = Nothing
Set Results = Nothing
End Sub
Code works, with one flaw. When I run it again, it creates a new file again, but .RecordsetClone is gone, so values from Subform are not exported again. Beside that, I find it very strange that code works, just take a look at »with wb« statement – I had to reference to XcelFile on certain commands or they didn't work, regardless I allready set wb to XcelFile in code above (Set wb = XcelFile.Workbooks.Add). What Is wrong in my code, does anybody have a better solution ???
解决方案
So this is final code, I hope It will be useful to someone else too.

Sub XcelExport()
Dim Results As Recordset
Dim RecCount As Integer
Dim XcelFileName As String
Dim FilePath As String
Dim wb As Excel.Workbook
Dim XcelFile As Excel.Application

'Set name of file with date
XcelFileName = "MySubform_Results_" & Format(Date, "dd/mm/yyyy") & ".xlsx"

' Set destinaton folder of saved file
FilePath = CurrentProject.Path & "\" & XcelFileName

Set XcelFile = New Excel.Application
Set wb = XcelFile.Workbooks.Add
'Fetch subform record source
Set Results = Forms![MainForm]![MySubform].Form.RecordsetClone

With wb
XcelFile.ScreenUpdating = False
' Add field names to workbook
For RecCount = 0 To Results.Fields.Count - 1
XcelFile.Cells(1, RecCount + 1).Value = Results.Fields(RecCount).Name
Next RecCount

' Copy subform results to Excel file and set Results to first row
Results.Movefirst
XcelFile.Range("A2").CopyFromRecordset Results

.SaveAs Filename:=FilePath, FileFormat:=51
XcelFile.ScreenUpdating = True
.Close
End With
Set XcelFile = Nothing
Set Results = Nothing
End Sub


                        
这篇关于访问 - 使用自定义文件名导出子表单过滤的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！