Save New file to Default location

[MarkCBB]

Hi there,

I am looking for some code that will save a New Workbook to the users default location, and automatically name the file "Master database" & Now()

 

[Richard Schollar]

Dim wb As Workbook
 
Set wb = Workbooks.Add
 
wb.SaveAs Application.DefaultFilePath & "\" & "Master database" & Format(Now,"yyyy-mm-dd hh.mm") & ".xls"

 

[MarkCBB]

Thanks Richard,

Works perfectly.

 

[MarkCBB]

Hi there Richard,

Is there away to create a new folder when this code is run. so that each day the extracted worksheets are filed by date. and on error i.e. if it is run twice a day a msgbox will appear, I know how to do that, I am just not sure on the error hanling.

Sub Test()

Dim Master_WKB As Workbook
Dim Value_R As Range
Dim wb As Workbook
Set Value_R = Sheet1.Range("A1")
Set Master_WKB = ActiveWorkbook
Value_R.Value = 0
    
    Do
    Value_R.Value = Value_R.Value + 1
    Sheets("Sheet1").Copy
    Set wb = ActiveWorkbook
    
    wb.SaveAs Application.DefaultFilePath & "\CSI REPORTS " & Format(Now, "YYY-MMM-DD") _
    & "\" & "CSI REPORTS " & Range("C1").Value & Format(Now, "yyyy-mm-dd hh.mm") & ".xls"
    
    ActiveWindow.Close
    Master_WKB.Activate
    Loop Until Value_R.Value = Value_R.Offset(0, 1).Value
    
End Sub

 

[Richard Schollar]

You can create a folder using MkDir - to do this successfully, path to the folder must exist (so you can't create a deep folder structure with one pass of MkDir, you would need to sequentially generate each folder in turn until you get to the deepest level).

If Len(Dir("D:\Market Data", vbDirectory)) > 0 Then
    'save file down to already existsing folder Market Data?
Else
    'make folder:
    MkDir "D:\Market Data"
    'code to save files
End If

 

[MarkCBB]

HI there Richard,

Hey that is perfect, Thank you.

Just a quick question regarding the first line of code:

If Len(Dir("D:\Market Data", vbDirectory)) > 0 Then

is this code saying if the length of the Directory's name is greater than 0 then do this, meaning if the directory's name did not exist the value that would be returned by the LEN would be 0? or would if be an Error?

what does the

, vbDirectory

mean? is this referring to the end of the path i.e. Market Data?

I appreciate your help and assistance.

thank you

Kindest Regards,
Mark Blackburn