如何打开带有路径文件的工作簿

Question

我在Workbooks((上很吃力。在下面的代码中，我希望用户从对话框中选择文件并获取路径文件。所有这些步骤都在起作用。然而，我正在努力使用pathKeys。当我写Workbooks(pathkeys)时，我似乎有一个错误9(编写范围内的脚本(。

Sub getData()
Dim diagBoxkeys As FileDialog
Dim pathKeys As String

Set diagBoxkeys = Application.FileDialog(msoFileDialogFilePicker)
diagBoxkeys.Title = "Keys File " & FileType
diagBoxkeys.Filters.Clear
diagBoxkeys.Show
If diagBoxkeys.SelectedItems.Count = 1 Then
pathKeys = diagBoxkeys.SelectedItems(1)
End If

MsgBox (pathKeys)
Dim wbKeys As Workbook
ScreenUpdating = False

Set wbKeys = GetObject(pathKeys)
Workbooks(pathKeys).Worksheets(1).Columns(2).Copy Destination:=Workbooks("Macro_PORTAL_APRR.xlsm").Worksheets(1).Columns(1)  
wbKeys.Close Savechanges:=False

End Sub

然而，当我在这个代码中用Workbooks("Keys_2021-12-27_13_43_21_utf-8.csv")替换Workbooks(pathKeys)时，它可以完美地工作。

我不明白为什么pathKeys不被接受为pathKeys = C:Userstn5809DocumentsPROJETSPORTAL_APRRKeys_2021-12-27_13_43_21_utf-8.csv

我做错了什么？

Answer 1

这可能更适合您。

'This should appear at the top of all modules.
'It forces you to declare all variables.
'Tools ~ Options ~ Require Variable Declaration.
Option Explicit
Public Sub GetData()

'*** If Macro_Portal is not file containing this code: ***
Dim MacroPortalPath As String
MacroPortalPath = OpenFile ' or MacroPortalPath = "C:......Macro_PORTAL_APRR.xlsm"
Dim MacroPortal As Workbook
MacroPortal = Workbooks.Open(MacroPortalPath)
'*** If Macro Portal is the file containing this code: ***
'Dim MacroPortal As Workbook
'Set MacroPortal = ThisWorkbook

Dim pathKeys As String
pathKeys = OpenFile

If pathKeys <> "" Then
Dim wrkBk As Workbook
Set wrkBk = Workbooks.Open(pathKeys)

'Best to use sheet name rather than where it is in workbook (can be moved by user).
'You could replace MacroPortal with ThisWorkbook and remove first block of code in this procedure.
wrkBk.Worksheets("Sheet1").columns2.Copy Destination:=MacroPortal.Worksheets("Sheet1").Column(1)
wrkBk.Close SaveChanges:=False

End If

End Sub
Public Function OpenFile() As String
Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogFilePicker)

With dialogBox
.Title = "Keys File"
.AllowMultiSelect = False
.InitialFileName = "C:"
If .Show = -1 Then
OpenFile = .SelectedItems(1)
End If
End With
End Function