使用.filedialog将查询结果导出到新的Excel文件

holi4683

我有一个拆分数据库，表单的形式显示查询的过滤结果。我只想将结果导出到新的excel应用程序/工作簿中。我只能找到导出到现有文件的示例，我想要一个空白文件，以便用户可以将其保存在所需的位置。如何从filedialog提示符中获取路径和名称并将其设置为变量，以便可以将其放置在DoCmd.TransferSpreadsheet？我现在得到的结果是“ FileDialog（msoFileDialogSaveAs）”作为文件名...。

 Private Sub btnToExcel_Click()

  Dim fd As Office.FileDialog

  Set fd = Application.FileDialog(msoFileDialogSaveAs)

  With fd

        .AllowMultiSelect = True           

        .Title = "Please select file to save"

        If .Show = True Then

        Else

           MsgBox "You clicked Cancel."

        End If

    End With

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Scale_Log", fd, True

  End Sub

holi4683

拼凑出其他可行的方法。将项目从列表框中复制到新的Excel工作簿中。列表框显示我的查询结果。

私人Sub btnExport_Click（）

  Dim myExApp As Excel.Application    'variable for Excel App

  Dim myExSheet As Excel.Worksheet    'variable for Excel Sheet

  Dim i As Long                       'variable for ColumnCount

  Dim j As Long                       'variable for ListCount

  Set myExApp = New Excel.Application



  myExApp.Visible = True              'Sets Excel visible

  myExApp.Workbooks.Add               'Add a new Workbook

  Set myExSheet = myExApp.Workbooks(1).Worksheets(1)



  For i = 1 To ltbFiltered.ColumnCount   'Counter for ColumnCount

      ltbFiltered.BoundColumn = ltbFiltered.BoundColumn + 1 'Setting counter for BoundColumn

      For j = 1 To ltbFiltered.ListCount 'Counter for ListCount

          myExSheet.Cells(j, i) = ltbFiltered.ItemData(j - 1)     'Insert ItemData into Excel Worksheet

      Next j  'Iterating through ListCount

  Next i  'Iterating through ColumnCount

  ltbFiltered.BoundColumn = 1    'Setting BoundColumn to original 1



  Set myExSheet = Nothing 'Release Worksheet

  Set myExApp = Nothing   'Release Excel Application



  End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。