我创建了一个简单的空excel文件,并在工作簿上写了一个简单的宏:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox ThisWorkbook.Path
End Sub
打开文件并在excel文件中插入新工作表后,msgBox不显示任何内容。但是,保存文件后,MsgBox将显示正确的地址。这是为什么?!刚打开文件后如何获取文件地址?
这是因为新创建的工作簿还没有路径!
一旦保存,它就会有一条真实的道路
您可以按以下方式更改您的潜艇:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If ThisWorkbook.Path = "" Then '<--| if the workbook doesn't have path, yet
With Application.FileDialog(msoFileDialogSaveAs) '<--| have the user choose a path and a name
.Title = "Select Folder and name"
.InitialFileName = "C:\"
If .Show = 0 Then Exit Sub '<--| if the user canceled the dialog box then exit sub
ThisWorkbook.SaveAs .SelectedItems(1), xlOpenXMLWorkbookMacroEnabled '<--| otherwise save the workbook to the specified path as a macro enabled excel file
End With
End If
MsgBox ThisWorkbook.Path '<--| finally show the workbook path!
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句