首页 文章

将文件导入当前VBA工作簿

提问于
浏览 次
0

我正在尝试构建一个VBA宏，以允许用户从文件Dialog中选择一个文件，并将Sheet 1从文件导入到Active VBA工作簿，并将Sheet重命名为“Import” . 我是新手，这是我得到的 . 这允许我打开文件对话框并选择一个文件 . 但它所做的只是打开文件，这不是很有帮助 .

Sub OpenAFile()

Dim fd As FileDialog
Dim FileWasChosen As Boolean

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Add "Any Excel Files", "*.xl*"

FileWasChosen = fd.Show

If Not FileWasChosen Then
MsgBox "You didn't select a file"
Exit Sub
End If

fd.Execute

End Sub
vba

1 回答

  • 0

    你快到了 . 这是一种做法 . 这有点令人费解，我知道有更多的方式，但它将完成工作 .

    Sub OpenAFile()

Dim fd As FileDialog
Dim FileWasChosen As Boolean

Dim homeWorkbook As Workbook
Set homeWorkbook = ActiveWorkbook

Dim targetBook As Workbook
Dim targetSheet As Worksheet

Application.DisplayAlerts = False

Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Add "Any Excel Files", "*.xl*"

FileWasChosen = fd.Show

If Not FileWasChosen Then
     MsgBox "You didn't select a file"
Exit Sub
End If

fd.Execute

Set targetBook = ActiveWorkbook
Set targetSheet = targetBook.Worksheets(1)

targetSheet.Copy After:=homeWorkbook.Sheets(homeWorkbook.Sheets.Count)
targetBook.Close

Application.DisplayAlerts = True

With homeWorkbook.Sheets(homeWorkbook.Sheets.Count)
    .Name = "Import"
End With


End Sub
    回复于

相关问题