VBA Code - Check and Create Directory

[Spaztic]

I need some coding help. The code takes the Excel file and saves as a PDF file with a slightly different file name. The PDF file is supposed to go into a folder called "SavedWork" on the desktop.

Right now, if my desktop has the "SavedWork" folder, the code works (file is put in that folder). But if there isn't the "SavedWork" folder on the desktop, it doesn't (file won't save anywhere / code has error).

How do I change my code to:
* create the "SavedWork" folder on the desktop if it isn't already there on the desktop

Dim sPath As String, sName As String
desktopDir = CreateObject("WScript.Shell").specialfolders("Desktop")
sPath = desktopDir & "\SavedWork"

sName = ThisWorkbook.Name
sName = Left(sName, InStrRev(sName, ".") - 1)

' Check to see if file exists
    Dim strFileExists As String
    Dim strFileName As String
    Dim tsfile As String
    
    strFileName = sName & "_" & "ISS" & ".pdf"
    tsfile = sPath & "\" & strFileName
    strFileExists = Dir(tsfile)
    
    Dim AnswerYes As String
        
    If strFileExists <> "" Then
        MsgBox strFileName & " already exists", vbExclamation, "FileCheck"
        AnswerYes = MsgBox("Overwrite File?", vbQuestion + vbYesNo, "FileCheck")
        If AnswerYes = vbNo Then
            GoTo Unhide
        End If
    End If
  
ThisWorkbook.Save
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath & "\" & sName & "_" & "ISS" & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[Micron]

There are lots and lots of code examples that creates file folders if they don't exist. Did you try looking at some of them? Some use FSO (file scripting object); some use Shell. Which you want to use might be better for you to search out and see which you find attractive. FSO will require a reference to that library - don't know if that will be an issue for anyone. Here's a few

Create a folder in any user's Desktop directory if it does not exist

I have a file which, among other things, opens a specific folder when a button is pressed. I will be distributing this file to other users so the code must know to look at the correct directory to:...

stackoverflow.com

Create folder path if does not exist (saving from VBA)

I have a list of items in a sheet like so: My code goes through each row and groups the supplier and copies some information into a work book for each supplier. In this scenario there are 2 unique

stackoverflow.com

E

Post in thread 'VBA code to create a folder (if it doesn't already exist) based on cell values'

Jan 7, 2021

Please try this

Sub folder_exist_or_create()
Dim path As String
Dim folder As String
path = Range("Z1").Value & Range("Y1").Value
folder = Dir(path, vbDirectory)
If Range("D1").Value = "Update File Series" Then
    If folder = vbNullString Then
        VBA.FileSystem.MkDir (path)
        MsgBox "Folder created"
    Else
    MsgBox "Folder exist"
    End If
End If
End Sub

• ER_Neha

• 

 

[Spaztic]

I'm confused by the "Z1", "Y1", "D1" values. And where do I enter what you have in my code? (Again, I'm pretty new to coding)

 

[Micron]

Don't know what happened there, that last one was supposed to be a link yet it got posted as code. If you read the thread you'd know what relevance those had but I guess you couldn't! Those cells contain folder/file path info:

EDIT - I can't seem to post only the link, but if you click on the panel right below where that post is "quoted" it will take you there.

Thread 'VBA code to create a folder (if it doesn't already exist) based on cell values'

Jan 7, 2021

Hello,

I am hoping someone could help me write a certain code that can do the following:

If Cell D1 has text: "Update File Series"
Create a file in the directory specified in cell: Z1 and name it based on cell Y1.

If the file in this directory with this name does not exist, do not create and end sub.

So I suppose I want the code to not only run when Cell D1 has text: "Update File Series" but also check if the file already exists and then create it if it does not

I hope I am being clear. Thank you to anyone who can help with this!

• willow1985
• Replies: 5
• Forum: Excel Questions

 

[Spaztic]

This worked... I added the following to the code:
CreateDir (sPath)
-----------------------------------------------------------
Private Sub CreateDir(localDir As String)
On Error GoTo ErrorHandler
MkDir localDir
ErrorHandler:
Exit Sub
End Sub