File save as location

Nygie

Board Regular
Joined
Apr 15, 2015
Messages
50
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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 :)
 
Upvote 0
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".
 
Upvote 0
Ah that makes sense, thank you for your time.
Saved 2 lines using the different loop method too, nice one.
Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top