Check if file exists > create the file/write data

[davedave24]

Hi guys. I have a set of data to write to an excel file, which may or may not exist. I have done only a little work on creating files and the various examples I've tried off google all go in loops and break down (mostly where the file is in a different folder, this is what is causing the problems for me).

1) check if a file exists in a folder, based on the value of a listbox (listName) which holds company names.

Example: listName = CompanyX
File = \CompanyX\companyX.xlxs

3 scenarios:
a) the folder doesn't exist. If not, create it, then create the file (probability - slim, a possiblilty)
b) the folder does exist, but the file does not. Create the file within the folder (proability - 50%)
c) the folder exists and the file also exists. (probability - 50%)


Once the file has been found/created, write the data (not an issue), save the file, close the file.


This is my current code, which works a little bit. I'm posting it because somebody will ask, but if you have a more elegant solution you could whip up, that would be great.

wk = "C:\Users\Dave\Documents\xl\" & CustomerName & "\" & CustomerName & " Week " & WeekNumber & ".xlsx"
        If FileThere(wk) Then                   'The workbook already exists
            MsgBox "File exists"
            'open the workbook
            Workbooks(wk).Activate
            'move to last used cell in column A, then move down 1
            Workbooks(wk).Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Select
            Workbooks(wk).Worksheets("sheet1").ActiveCell.Offset(1, 0).Select
            
            'paste the data
            PasteDataInCustomerSheet
            
            'insert a new row
            InsertRowsAndFillFormulas
            
            'save the workbook, then close
            Workbooks(wk).Save
            Workbooks(wk).Close
        Else
            MsgBox "File Not There! Creating it now!"
            'create the new workbook from the template
            NewWorkbooks
        End If

Function FileThere(FileName As String) As Boolean
    'Check if a file exists
    FileThere = (Dir(FileName) > "")
End Function

Sub NewWorkbooks()
    'create a new workbook with the name of the customer and their weeknumber
    Dim myWorkbook As Workbook
    Dim wk As String
    Application.DisplayAlerts = False
    wk = "C:\Users\Dave\Documents\xl\" & CustomerName & "\" & CustomerName & " Week " & WeekNumber & ".xlsx"
    
    'CREATE THE FOLDER
    On Error Resume Next
    MkDir "C:\Users\Dave\Documents\xl\" & CustomerName
    On Error GoTo 0

    'Create a new workbook from the template with the week layout
    Set myWorkbook = Workbooks.Add(Template:="C:\Users\Dave\Documents\xl\weektemplate.xltx")
    myWorkbook.Sheets("sheet1").Range("A5") = "Week " & WeekNumber
    myWorkbook.Sheets("sheet1").Range("A6").Select
        
    'Write all the pieces of data into the new week!
    PasteDataInCustomerSheet
    
    'Insert a new row underneat when finished, complete with all formulas
    InsertRowsAndFillFormulas
    
    'SAVE THE WORKBOOK
    Workbooks(wk).Save
    Workbooks(wk).Close
End Sub

 

[Gavona]

thread707-1252900 has a few functions posted by Zack (firefytr). And the example code he posted could be adapted to your purpose

The logic would be:
1. If folder does not exist then create it
2. If file does not exist then
create it
Else
open it
3. Do your stuff (which could be a separate procedure to keep the code organised)

Note that

Gavin