File save as location

[Nygie]

Morning all.

I have written my first little bit of code totally on me todd and got it to work kind of.
Basically I have an excel file called template whcih I copied to a new folder. Opened said file and ran my code which enters date into cell B2 then saves the files as e.g. Week Ending 25 October 2019.xlsx It then advances count by 1 and WE date by 7 days and repeats 'x' number of times.
I have a couple of questions neither of which s a big issue, but I was always a kid that asked WHY

My 1st question being it saves not into the original file location not the folder I copied the template to before funning the code, is there something stored in file properties somewhere that causes this.

My 2nd question is it prompts me to save as xlsx as it has code in and I just click yes to save without. I tried defining the file as .xlsx but it wouldn't compile.

My code
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CreateFiles()
Dim WE As Date
WE = "25/10/2019"
Dim count As Long
count = 0

Do While count < 5
Range("B2").Value = WE
ActiveWorkbook.SaveAs Filename:="Week Ending" & Format(WE, " dd mmmm yyyy")
WE = (WE + 7)
count = (count + 1)
Loop

End Sub
[/FONT]
Thanks in advance

 

[Yongle]

I think this answers both your questions
http://codevba.com/excel/workbook_saveas.htm#.Xam5mmZ7nIU

 

[John_w]

I don't understand your first question - it's not clear where you want the copies saved. For your second question, use Application.DisplayAlerts = False to suppress the prompt.

Try this macro:

Private Sub Create_xlsx_Workbooks()

    Dim tempCopy As String
    Dim tempWorkbook As Workbook
    Dim p As Long
    Dim WE As Date
    Dim count As Long
    
    'Create a temporary copy of the .xlsm/.xlsb file
    
    With ActiveWorkbook
        p = InStrRev(.FullName, ".")
        tempCopy = Left(.FullName, p - 1) & " TEMP COPY" & Mid(.FullName, p)
        .SaveCopyAs tempCopy
    End With
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    'Open the temporary copy and save it as multiple .xlsx files
    
    Set tempWorkbook = Workbooks.Open(tempCopy)
    WE = "25/10/2019"
    For count = 1 To 5
        tempWorkbook.ActiveSheet.Range("B2").Value = WE
        tempWorkbook.SaveAs Filename:=tempWorkbook.Path & "\Week Ending" & Format(WE, " dd mmmm yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        WE = WE + 7
    Next
    tempWorkbook.Close False
    
    'Delete the temporary copy
    
    Kill tempCopy
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

 

[Nygie]

Hi John, thanks for the reply.

My question 1 has an extra not in which confuses things a little, sorry.
I just wanted it to save to the current folder location, which it does seem to if I put it onto a folder on the desktop.
I can add the displayalerts and have it working in 14 lines, I am a noob at this so take what I say with a pinch of salt but your way (which is probably more correct) has pretty much double the number of code lines. Not being ungrateful but as I stated I was always a Why child

 

[John_w]

If you don't specify a folder path on the SaveAs then it saves it in some default location. Try this with an explicit path and FileFormat:=xlOpenXMLWorkbook on the SaveAs line:

Sub CreateFiles()
    Dim WE As Date
    WE = "25/10/2019"
    Dim count As Long
    Application.DisplayAlerts = False
    For count = 1 To 5
        Range("B2").Value = WE
        ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\Week Ending" & Format(WE, " dd mmmm yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        WE = WE + 7
    Next
    Application.DisplayAlerts = True
End Sub

The reason for my longer method is that it keeps the original macro workbook open at the end, whereas with the above shorter method the workbook open at end is "Week Ending 22 November 2019.xlsx".

 

[Nygie]

Ah that makes sense, thank you for your time.
Saved 2 lines using the different loop method too, nice one.
Thanks again