Create Folder in VBA

[bamaisgreat]

Thanks for looking at my post.

What I would like to do is have a macro in a workbook that when run it would create a folder with the same name as the excel file in the same location. Then I would like it to somehow save the excel file in the folder that was just created and delete or kill the old one.

Make since??

 

[ZVI]

Hi,
Try this:

Sub Test()
  Dim i As Long, Path As String, NewPath As String, FileName As String, WbName As String
  Path = ThisWorkbook.Path
  FileName = ThisWorkbook.Name
  WbName = Left(FileName, InStrRev(FileName, ".") - 1)
  If UCase(Path) Like "*" & UCase(WbName) Then Exit Sub
  NewPath = Path & "\" & WbName
  MkDir NewPath
  ThisWorkbook.SaveAs NewPath & "\" & ThisWorkbook.Name
  Kill Path & "\" & FileName
End Sub

Regards

 

[Logit]

.
Similar ...

Option Explicit


Sub CommandButton1_Click()
    Dim fso As Object
    Dim Filename As String
    Dim fldrpath As String
    Set fso = CreateObject("scripting.filesystemobject")
    Filename = ActiveWorkbook.Name
    If InStr(Filename, ".") > 0 Then
       Filename = Left(Filename, InStr(Filename, ".") - 1)
    End If
    fldrpath = Application.ActiveWorkbook.Path & "\" & Filename
    If Not fso.folderexists(fldrpath) Then
        fso.createfolder (fldrpath)
    End If
    ThisWorkbook.SaveCopyAs fldrpath & "\" & Filename & ".xlsm"
    With ThisWorkbook
        .Saved = True
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        Application.Quit
    End With
End Sub

 

[bamaisgreat]

Works Great ! Could it be edited so I could put it in my personal workbook?

 

[ZVI]

Could it be edited so I could put it in my personal workbook?

Sure, just replace ThisWorkbook by ActiveWorkbook:

Sub Test()
  Dim i As Long, Path As String, NewPath As String, FileName As String, WbName As String
  Path = ActiveWorkbook.Path
  FileName = ActiveWorkbook.Name
  WbName = Left(FileName, InStrRev(FileName, ".") - 1)
  If UCase(Path) Like "*" & UCase(WbName) Then Exit Sub
  NewPath = Path & "\" & WbName
  MkDir NewPath
  ActiveWorkbook.SaveAs NewPath & "\" & ActiveWorkbook.Name
  Kill Path & "\" & FileName
End Sub

The same is for the code of Logit