File save as location

Nygie

New Member
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
 

John_w

MrExcel MVP
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:
Code:
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

New Member
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

MrExcel MVP
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:

Code:
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

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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top