vba - excel单元格=另一个工作簿的文件名

Question

我有一个用于更新另一个工作簿中的工作表的宏，如何使用同一文件来更新其文件名不带 .xlsx 的单元格。

我可以使用vFile或wbCopyFrom Dim吗？

Sub UpdateTSOM()
Application.ScreenUpdating = False
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

If MsgBox("Update Transmission Stock Status data?", vbYesNo) = vbYes Then
Worksheets("TSOM").Range("B2:N3000").ClearContents
Else: Exit Sub
End If
 On Error GoTo whoa
'Open file with data to be copied
 vFile = "C:\Users\taylorm1\Desktop\OUC\_Materials\Stock Status\Transmission Stock Status*.xlsx"
'vFile = "P:\ESO\1790-ORL\OUC\_Materials\Stock Status\Transmission Stock Status **-**-**.xlsx"

Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)

'Copy Range
wsCopyFrom.Range("A1:N3000").Copy
'wsCopyFrom.Range("A1:A" & LastRow).Copy
wsCopyTo.Range("B2").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

SendKeys "Y"
SendKeys ("{ESC}")

'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False
    Application.Wait (Now + 0.000005)
    Call NoSelect
    Exit Sub
    Application.ScreenUpdating = True
whoa:
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)

Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)

'Copy Range
wsCopyFrom.Range("A1:N3000").Copy
'wsCopyFrom.Range("A1:A" & LastRow).Copy
wsCopyTo.Range("B2").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

SendKeys "Y"
SendKeys ("{ESC}")

'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False
    Application.Wait (Now + 0.000005)
    Call NoSelect
    Exit Sub

'whoa: 'If filename changes then open folder
'Call Shell("explorer.exe" & " " & "P:\ESO\1790-ORL\OUC\_Materials\Stock Status", vbNormalFocus)

End Sub

Thanks

Answer 1

您可以获取不带路径和扩展名的文件名，如下所示:

Dim s As String
s = Mid(vFile, InStrRev(vFile, "\") + 1)
s = Left$(s, InStrRev(s, ".") - 1)

或者，如果您想保留完整路径但仅删除扩展名:

Dim s As String
s = Left(vFile, InStrRev(vFile, ".") - 1)

然后将其分配给任何单元格:myCell.Value = s