将路径文件分配给 VBA 中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24248946/
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
Assign Path File into a variable in VBA
提问by Bart g
I am almost done with this small part of my program. I am trying to assign a path file to a variable so I can use that variable in the second part, I have tried the following. I appreciate any help. Thank you very much in advanced
我几乎完成了我程序的这一小部分。我正在尝试将路径文件分配给一个变量，以便我可以在第二部分中使用该变量，我尝试了以下操作。我很感激任何帮助。非常感谢您提前
FIRST PART
第一部分
Public strPathAndFile As String
Sub CommandButton2_Click()
Dim fd As FileDialog
Dim strShortName As String
Dim strInitialDir As String
Dim wb As Workbook
strInitialDir = CurDir 'Save current directory if required
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.InitialFileName = CurDir & "\" 'Startup folder (Optional line of code)
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "All Excel Files", "*.xls;*.xlsm;*.xlsx" 'Display excel files only
If .Show = False Then
MsgBox "User cancelled without selecting. Processing terminated."
ChDir (strInitialDir) 'Change back to Initial directory if user cancels
Exit Sub
End If
'Next line strPathAndFile contains path and filename of selected file ' < ======= FROM HERE
strPathAndFile = .SelectedItems(1)
End With
'The following populates a cell with just the filename (without the path)
strShortName = Right(strPathAndFile, Len(strPathAndFile) - _
InStrRev(strPathAndFile, "\"))
' Store the path file in this cell
ThisWorkbook.Worksheets("Sheet1").Range("F12") = strShortName
End Sub
SECOND PART - Here I want to use the strPathAndFilevariable.
第二部分 - 在这里我想使用strPathAndFile变量。
Sub CommandButton3_Click()
' Read a file and find the title name
' Variables
Dim WhatToFind As Variant
Dim wbFind As Workbook
Dim foundRange As Range
' Assign file path to a variable
Set wbFind = Workbooks.Open(strPathAndFile) ' <======== TO HERE
' Do not show actions on screen
Application.ScreenUpdating = False
' Array of valuse to be found
WhatToFind = Array("Dog", "House", "Table")
With wbFind.Sheets("general_report")
Set foundRange = .Cells.Find(What:="status", After:=.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
End With
If Not foundRange Is Nothing Then foundRange.Activate
' close the source workbook without saving any changes
wbFind.Close False
' free memory
Set wbFind = Nothing
' Show actions on screen
Application.ScreenUpdating = False
End Sub
回答by evenprime
You are already saving the path in the cell,Worksheets("Sheet1").Range("F12"), Cant you just do
您已经在单元格中保存路径，Worksheets("Sheet1").Range("F12")，您不能这样做
' Store the path file in this cell
ThisWorkbook.Worksheets("Sheet1").Range("F12") = strPathAndFile
then use
然后使用
Dim fpath As String
fpath = Worksheets("Sheet1").Range("F12").Value
Set oWB = Application.Workbooks.Open(fpath)
回答by xQbert
Public strFileAndPath As String
Sub getpath()
Dim retvalue As String
retvalue = ThisWorkbook.FullName
strFileAndPath = retvalue
End Sub
you just need to set the strfileandpath global variable which can be done via strFileAndPath = thisworkbook.fullname
你只需要设置 strfileandpath 全局变量，这可以通过 strFileAndPath = thisworkbook.fullname
The other stuff above was me testing it.
上面的其他东西是我测试的。
