標題: [發問] VBA VLookup允許用戶選擇目錄上的文件名 [打印本頁]

---

作者: boomf2    時間: 2019-12-1 18:30     標題: VBA VLookup允許用戶選擇目錄上的文件名

我在在網絡上找到這個問題(看PartA) 他們說: Set myFile = Application.GetOpenFilename 不能回傳一個object.
之後有人解答要使用以下B部份.去修改...可是我不懂要如何修改 , 有人能試一下嗎?

1. Sub VlookupMacro()
   
2. 
   
3. Dim FirstRow As Long
   
4. Dim FinalRow As Long
   
5. Dim myValues As Range
   
6. Dim myResults As Range
   
7. Dim myFile As Range
   
8. Dim myCount As Integer
   
9. 
   
10. Set myFile = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
    
11. 
    
12. Set myValues = Application.InputBox("Please select the first cell in the column with the values that you're looking for", Type:=8)
    
13. 
    
14. Set myResults = Application.InputBox("Please select the first cell where you want your lookup results to start ", Type:=8)
    
15. 
    
16. Range(myResults, myResults.Offset(FinalRow - FirstRow)).Formula = _
    
17.     "=VLOOKUP(" & Cells(FirstRow, myValues.Column) & ", myFile.value($A$2:$B$U20000), 5, False)"
    
18. 
    
19. If MsgBox("Do you want to convert to values?", vbYesNo) = vbNo Then Exit Sub
    
20. 
    
21. Columns(myResults.Column).Copy
    
22. Columns(myResults.Column).PasteSpecial xlPasteValues
    
23. Application.CutCopyMode = False
    
24. 
    
25. End Sub

複製代碼

===============================================
B部:

1. FileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
   
2. If FileName = False Then
   
3.      ' User pressed Cancel
   
4.     MsgBox "Please select a file"
   
5.     Exit Sub
   
6. Else
   
7.     Workbooks.Open Filename:=FileName  
   
8. End If

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)