Check if a spreadsheet exists and if not, create it

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I tried to copy code off the net and modify it but it didn't work. I am trying to see if a folder and file exists and if not, create it.

This is what I wrote.
VBA Code:
Sub TestSheetCreate()
    Dim newSheetName As String
    Dim checkSheetName As String
    newSheetName = ThisWorkbook.Path & "\Young People\list.xlsm"
    On Error Resume Next
    checkSheetName = Worksheets(newSheetName).Name
    If checkSheetName = "" Then
        Worksheets.Add.SaveAs = newSheetName
        MsgBox "The sheet named ''" & newSheetName & _
        "'' does not exist in this workbook but it has been created now.", _
        vbInformation
         
    Else
        MsgBox "The sheet named ''" & newSheetName & _
        "''exist in this workbook.", vbInformation
    End If
End Sub

It needs to look for List.xlsm in a folder called "Young People" in the current directory and if it doesn't exist, create it.

Can someone help me with the above code as it doesn't work please?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,019
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Haven't you already got the folder / file checking code ???
VBA Code:
Sub CheckFolderExists()
    If Dir(ThisWorkbook.Path & "\Young People", vbDirectory) = "" Then
        MkDir Path:=ThisWorkbook.Path & "\Young People"
    End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have code that checks if the young person folder exists and to create it if it doesn't. What I need to do is chdeck if the list.xlsm file exists and in the youjng people folder and crfeate it if it doesn't.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,019
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
VBA Code:
Sub MM1()
Dim strFileName As String, strFileExists As String, fpath As String
fpath = ThisWorkbook.Path
    strFileName = fpath & "\Young People\list.xlsm"
    strFileExists = Dir(strFileName)
   If strFileExists = "" Then
        MsgBox "The selected file doesn't exist"
    Else
        MsgBox "The selected file exists"
    End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for that Michael. If list.xlsm doesn't exist, how can i create it with code?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yay! I am getting better at this, I worked it out.

VBA Code:
Sub CheckListExist()
Dim strFileName As String, strFileExists As String, fpath As String
fpath = ThisWorkbook.Path
    strFileName = fpath & "\Young People\List.xlsm"
    strFileExists = Dir(strFileName)
   If strFileExists = "" Then
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\List", 52
    Else
        MsgBox "The selected file exists"
    End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

What is wrong with my code, it tells me that it can't access the file

VBA Code:
Sub CheckListExistCreate()
Dim strFileName As String, strFileExists As String, fpath As String
fpath = ThisWorkbook.Path
    strFileName = fpath & "\Young People\List.xlsm"
    strFileExists = Dir(strFileName)
    If strFileExists = "" Then
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\List", 52
    Else
        Workbooks.Open (ThisWorkbook.Path & "\" & "Young People" & "\" & "List.xlsm")
    End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Cool, I worked it out, i needed to run the coded that checks for the directory first.

VBA Code:
Sub CheckFolderExists()
    If Dir(ThisWorkbook.Path & "\Young People", vbDirectory) = "" Then
        MkDir Path:=ThisWorkbook.Path & "\Young People"
    End If
End Sub
Sub CheckListExistCreate()
Dim strFileName As String, strFileExists As String, fpath As String
fpath = ThisWorkbook.Path
    strFileName = fpath & "\Young People\List.xlsm"
    strFileExists = Dir(strFileName)
    If strFileExists = "" Then
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\List", 52
    Else
        Workbooks.Open (ThisWorkbook.Path & "\" & "Young People" & "\" & "List.xlsm")
    End If
End Sub
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok, I now have this code

VBA Code:
Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, wbList As Workbook, f As Range, shList As Worksheet
Dim wbTrack As ThisWorkbook
    newyp = Tracker.Cells(6, 4)
    If Not isFileOpen("List.xlsm") Then
        Call CheckFolderExists
        Call CheckListExistCreate
      
        Set wbList = Workbooks("list")
        Set shList = wbList.Sheets(1)
    End If
    Set f = shList.Range("A:A").Find(newyp, xlValues, xlWhole)
    If f Is Nothing Then
            shList.Range("A" & Rows.Count).End(xlUp)(2) = newyp
        End If
    YP.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
        Call CreateWB(newyp)
    ThisWorkbook.Names.Add Name:="tblYPNames", _
    RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

and I try to run it and get an error object variable or with block variable not set and it highlights this row
VBA Code:
Set f = shList.Range("A:A").Find(newyp, xlValues, xlWhole)

I want it to check if the name is already in the list of names in column A.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can someone please help me with code to see if the name stored in the variable newyp is already in column A of the first sheet of the list workbook please?
 

Forum statistics

Threads
1,144,370
Messages
5,723,964
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top