Make new folder & save to it
Hi, I was wondering if anybody knows a way, through VBA, to create a new folder in the same directory and then save the active workbook to that new folder? Thanks in advance -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
Make new folder & save to it
Dear Krabople:
Here are a couple of routines that may help. Modofy as necessary. Sub MakeFolderAndSaveActiveBook() Dim foldername As String Dim filename As String Dim s As String Dim wb As Workbook foldername = "ggg" ' modify for folder name filename = "filename.xls" s = CreateFolder(foldername) If Len(s) 0 Then MsgBox s, vbOKOnly, "Created" s = s & "\" & filename ActiveWorkbook.SaveAs s MsgBox s, vbOKOnly, "Created" Else MsgBox "Cannot Create Folder " & foldername, vbOKOnly, "Cannot create folder" End If End Sub Function CreateFolder(folder As String) As String Dim p As Variant On Error GoTo Err CreateFolder = "" p = CurDir If Len(p) 0 Then If Right(p, 1) < "\" Then p = p & "\" p = p & folder MkDir p CreateFolder = p End If Err: End Function -- HTHs Martin "krabople" wrote: Hi, I was wondering if anybody knows a way, through VBA, to create a new folder in the same directory and then save the active workbook to that new folder? Thanks in advance -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
Make new folder & save to it
That's perfect, thanks very much for your help! -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
Make new folder & save to it
Hi, thanks again for the code, it works great. The only problem is that it creates the folder in My Documents for some reason whereas I need the folder to be in the same directory as the active workbook. How do I do this? -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
Make new folder & save to it
Try this:
Function CreateFolder(folder As String) As String Dim p As Variant On Error GoTo Err CreateFolder = "" p = ActiveWorkbook.Path ' CurDir of active workbook<<<<<<<<<<<< If Len(p) 0 Then If Right(p, 1) < "\" Then p = p & "\" p = p & folder MkDir p CreateFolder = p End If Err: End Function -- HTHs Martin "krabople" wrote: Hi, thanks again for the code, it works great. The only problem is that it creates the folder in My Documents for some reason whereas I need the folder to be in the same directory as the active workbook. How do I do this? -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
Make new folder & save to it
Brilliant that's done the job, thanks again -- krabople ------------------------------------------------------------------------ krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774 View this thread: http://www.excelforum.com/showthread...hreadid=504845 |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com