我在其中一个工作表上有一个按钮,可让用户继续执行其任务,将他/她的模板保存为文件夹中的单独工作簿。
这是我的代码
Private Sub ContinueButton_Click()
Application.ScreenUpdating = 0
Sheets(cmbSheet.Value).Visible = True
Application.Goto Sheets(cmbSheet.Value).[a22], True
Application.ScreenUpdating = 1
Unload Me
End Sub
现在我需要的是检查该文件夹是否存在,如果该文件夹不存在,我的用户应该能够创建它。
我创建此文件夹的代码如下,但如何将这两个函数连接在一起我根本不知道,因为我对 VBA 相当陌生
Sub CreateDirectory()
Dim sep As String
sep = Application.PathSeparator
'sets the workbook's path as the current directory
ChDir ThisWorkbook.Path
MsgBox "The current directory is:" & vbCrLf & CurDir
'makes new folder in current directory
MkDir CurDir & sep & Settings.Range("C45").Value
MsgBox "The archive directory named " & Settings.Range("C45").Value & " has been created. The path to your directory " & Settings.Range("C45").Value & " is below. " & vbCrLf & CurDir & sep & Settings.Range("C45").Value
End Sub
我将对你的代码进行一些模块化:
首先在这里获取目录路径
Function getDirectoryPath()
getDirectoryPath = ThisWorkbook.Path & Application.PathSeparator & Settings.Range("C45").Value
End Function
您可以使用此功能创建目录
Sub createDirectory(directoryPath)
MkDir directoryPath
End Sub
您可以使用检查目录是否存在Dir
功能
Dir(directoryPath, vbDirectory) 'empty string means directoryPath doesn't exist
单击按钮的最终功能:
Private Sub ContinueButton_Click()
Application.ScreenUpdating = 0
Sheets(cmbSheet.Value).Visible = True
directoryPath = getDirectoryPath
'Creating the directory only if it doesn't exist
If Dir(directoryPath, vbDirectory) = "" Then
createDirectory directoryPath
End If
Application.Goto Sheets(cmbSheet.Value).[a22], True
Application.ScreenUpdating = 1
Unload Me
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)