使用 FileDialog 打开工作簿并在 Excel VBA 中对其进行操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25153342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Open a workbook using FileDialog and manipulate it in Excel VBA
提问by user3576866
I am learning how to use Excel macros and I found this code:
我正在学习如何使用 Excel 宏，我发现了以下代码：
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
This code opens the FileDialog. How can I open the selected Excel file without over-writing the previously opened?
此代码打开 FileDialog。如何在不覆盖之前打开的 Excel 文件的情况下打开选定的 Excel 文件？
回答by user3576866
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
回答by Frank
Unless I misunderstand your question, you can just open a file read only. Here is a simply example, without any checks.
除非我误解了您的问题，否则您只能以只读方式打开文件。这是一个简单的例子，没有任何检查。
To get the file path from the user use this function:
要从用户获取文件路径，请使用此函数：
Private Function get_user_specified_filepath() As String
'or use the other code example here.
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Please select the file."
get_user_specified_filepath = fd.SelectedItems(1)
End Function
Then just open the file read only and assign it to a variable:
然后只需打开只读文件并将其分配给一个变量：
dim wb as workbook
set wb = Workbooks.Open(get_user_specified_filepath(), ReadOnly:=True)
