Create save as file location? save as Excel workbook NO MACROS??

tnt6869 · Mar 2, 2013

Hello all! as always I appreciate any and all input.
I currently have an Excel 2010 macro enabled template that when a command button is clicked it does the following:

1-Replaces a date formula in a cell with the value

2-Deletes the command button that was just clicked

3-Saves the file (this is where I need some help)
4-And Emails the update
Here is the code that I am using (I am open to suggestions for any of this code):

Code:

Private Sub SaveIt()
'
' Macro1 Macro
'

'REPLACES THE DATE FORMULA IN B3 WITH THE DATE VALUE
    Range("B3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
'DELETES THE "SAVE IT" BUTTON
    ActiveSheet.Shapes.Range(Array("Rectangle 2")).Delete

'SAVES THE CURRENT FILE WITH THE DATE AS THE NAME
'CHANGE THE FILE LOCATION
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\KTS218\Documents\End of Shift Reports\" & Format(Range("B3").Value, "mm-dd-yyyy") & ".xls", FileFormat:= _
        xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
        
'EMAILS THE UPDATE
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
'ENTER THE NAME OF THE EMAIL GROUP IN THE "TO" LINE
        .To = "Kens update test"
        .CC = ""
        .BCC = ""
        .Subject = "Shift Update"
        .Body = ""
        '.Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        .Attachments.Add ("C:\Users\KTS218\Documents\End of Shift Reports\" & Format(Range("B3").Value, "mm-dd-yyyy") & ".xls")
        .Display
'or use
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
        
        
        
'CLOSES EXCEL
    Application.Quit
End Sub

Based on conditions in the active workbook I need a macro that will create up to 3 sub folders in the C:\Users\KTS218\Documents\End of Shift Reports folder. In my workbook I have the following information that I would like to be used to create the subfolders.
Fiscal Year in cell C2
Fiscal Quarter in cell D2
Current Month in cell E2
Current year in cell B2
If it doesn’t already exist I would like the macro to create the following folders to save the daily report to:
C:\Users\KTS218\Documents\End of Shift Reports\(the value of C2)\(the value of D2)\(the value of E2)
So for today’s report it would actually be…
C:\Users\KTS218\Documents\End of Shift Reports\FY 2013\Q4 2013\March 2013
As you can tell every month a new folder would be created
Every quarter a folder and sub folder would be created
Every year a folder and 2 sub folders would be created.
On a daily basis the report that is created would be saved into the folder again using the cells C2,D2,and E2 to determine the correct folder.
I would also like to save this as a 2010 Excel workbook (no macros), it is currently being saved as an excel 97 – 2003 workbook, so any help there would also be greatly appreciated.

Andrew Poulsom · Mar 3, 2013

You can use Dir to test if a folder exists and MkDir to create it if it doesn't. If you save your workbook as xlsx (FileFormat:=51) it won't contain any macros.

Create save as file location? save as Excel workbook NO MACROS??

tnt6869

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

Create save as file location? save as Excel workbook NO MACROS??

tnt6869

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock