使用FileDialog打开一个工作簿，并在Excel VBA中进行操作 [英] Open a workbook using FileDialog and manipulate it in Excel VBA

问题描述

我正在学习如何使用Excel宏，我发现这个代码：

I am learning how to use Excel macros and I found this code:

Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

    .AllowMultiSelect = False
    .Title = "Please select the file to kill his non colored cells"
    .Filters.Add "Excel", "*.xls"
    .Filters.Add "All", "*.*"

    If .Show = True Then
        txtFileName = .SelectedItems(1)
    End If

End With

此代码打开FileDialog。如何打开所选的Excel文件，而不会覆写以前打开的文件？

This code opens the FileDialog. How can I open the selected Excel file without over-writing the previously opened?

推荐答案

谢谢Frank.i得到了这个想法。
这是工作代码。

Thankyou Frank.i got the idea. Here is the working code.

Option Explicit
Private Sub CommandButton1_Click()

  Dim directory As String, fileName As String, sheet As Worksheet, total As Integer
  Dim fd As Office.FileDialog

  Set fd = Application.FileDialog(msoFileDialogFilePicker)

  With fd
    .AllowMultiSelect = False
    .Title = "Please select the file."
    .Filters.Clear
    .Filters.Add "Excel 2003", "*.xls?"

    If .Show = True Then
      fileName = Dir(.SelectedItems(1))

    End If
  End With

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Workbooks.Open (fileName)

  For Each sheet In Workbooks(fileName).Worksheets
    total = Workbooks("import-sheets.xlsm").Worksheets.Count
    Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("import-sheets.xlsm").Worksheets(total)
  Next sheet

  Workbooks(fileName).Close

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True

End Sub

这篇关于使用FileDialog打开一个工作簿，并在Excel VBA中进行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！