我正在努力使用 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(pathKeys)與Workbooks("Keys_2021-12-27_13_43_21_utf-8.csv")它完美的作品。
我不明白為什么 pathKeys 不被接受為 pathKeys = C:\Users\tn5809\Documents\PROJETS\PORTAL_APRR\Keys_2021-12-27_13_43_21_utf-8.csv
我究竟做錯了什么 ？
uj5u.com熱心網友回復：
這可能對您更有效。
'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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405367.html
標籤：
上一篇：跳過包含空單元格的行
下一篇：如何限制訪問中的小數位