目标:使用open function允许用户在多个函数中选择打开的任何文件上运行命令(路径在公共函数中)。
这是提示用户选择文件的初始函数。该文件路径保存在变量" path“中。我公开了这个函数,目的是在多个领域(全球化)中使用"path“。
Public Function OpenFile1() As String
On Error GoTo Trap
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Title = "Open Sterling Shipment History" 'Name for file
.Filters.Clear
.ButtonName = " Open "
.AllowMultiSelect = False
End With
If fd.Show <> 0 Then OpenFile1 = fd.SelectedItems(1)
Leave:
Set fd = Nothing
On Error GoTo 0
Exit Function
Trap:
MsgBox Err.Description, vbCritical
Resume Leave
Dim path As String
path = OpenFile1() 'Calls in file
If path <> vbNullString Then Debug.Print path
Workbooks.Open (path)
'rename the path variable for each function
'so that I can call in different files with that name
End Function
这是第二个函数的摘录,该函数试图从变量" path“调用文件路径,使用它打开工作簿并更改工作簿。
Sub Shipment_History()
Call OpenFile1
Dim sshist As Workbook
Set sshist = Workbooks.Open(path)
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
我也试过了:
Sub Shipment_History()
Call OpenFile1
Workbooks.Open(path)
我的问题是它不允许我打开“路径”。
错误状态
“运行时错误'1004':对不起,我们找不到。它是否可能已被移动、重命名或删除?”
发布于 2018-06-05 15:18:06
由于该函数返回一个字符串( path ),并且它是公开可用的,因此您不需要公共变量来存储路径。
在本地声明path变量并将其值设置为函数返回的值(path):
Sub Shipment_History()
Dim path as string
path = OpenFile1()
If path <> vbNullString Then Workbooks.Open(path)
End Sub
附注:删除Resume Leave
之后除End Function
语句之外的所有内容。
发布于 2018-06-05 02:59:17
path必须在任何函数或Subscript外部声明为公共字符串,函数不需要是公共的,它是变量。
在一个模块上尝试以下内容:
Public path As String
Function setPathValue(ByVal dataPassed As String)
path = dataPassed
End Function
Sub givePathVal()
setPathValue ("This is path value")
End Sub
Sub showPathVal()
MsgBox path
End Sub
https://stackoverflow.com/questions/50686754
复制相似问题