save file and create folders and sub folders based on condition

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows
hello
firs I need create folder based on current year with word calls sales 2021 and the sub folders names from sale1 up to 12
I want when save the file as PDF( to subfolder sale1 when reaches up to 60 files then move to next subfolder sale2 ... and so on
so each subfolder is limited when save files only 60 files and when enter a new year then create the folder call sales 2022 and create subfolders and so on every a new year
note: save as pdf based on range b2 =INV-BVN-1000 and the range from A1 : G is dynamically
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,121
I suspect no one has replied because your request isn't very clear. I don't understand all of it, so my interpretation and macro below might be wrong.

In which parent folder should the "Calls Sales 2021" subfolder be created? This folder is defined by the string parentFolder - edit the code as required.

note: save as pdf based on range b2 =INV-BVN-1000
I don't understand that. What should the file name of the PDF file be? Is it the contents of cell B2 (on the active sheet)? The file name must be unique because you can't have files with the same file name in a folder (the sale1, sale2, ... sale12 subfolders). The macro therefore names the PDF after cell B2 and the current date and time in YYYYMMDD HHMMSS format.

the range from A1 : G is dynamically
Do you want to save the range A1 to the last row in column G (on the active sheet) in the PDF?

What should happen when the sale12 subfolder is full? In that case the macro doesn't create a PDF and displays a warning.

Try this macro and see if it's near what you want. Change the Const values to lower numbers to test that the macro correctly creates the next sale1, sale2, etc. subfolder when the maximum number of PDFs has been reached.

VBA Code:
Public Sub Create_PDF_In_Year_Sales_Folder()

    Dim FSO As Object
    Dim FSfolder As Object
    Dim parentFolder As String
    Dim yearFolder As String, saleSubfolder As String
    Dim saleNumber As Long
    Dim PDFfile As String
    Dim foundsaleSubfolder As Boolean
    Dim lastRowG As Long
   
    Const saleSubfolderMaxFiles = 60                     'Maximum number of PDFs in each sale<n> subfolder
    Const saleMaxNumber = 12                             'Maximum number of sale<n> subfolders
   
    parentFolder = "C:\path\to\parent folder\"           'Parent folder of each "Calls Sales <current year>" subfolder
   
    If Right(parentFolder, 1) <> "\" Then parentFolder = parentFolder & "\"
    yearFolder = parentFolder & "Calls Sales " & Year(Date) & "\"
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    If Not FSO.FolderExists(yearFolder) Then
        FSO.CreateFolder yearFolder
    End If
   
    foundsaleSubfolder = False
    saleNumber = 1
    Do
        saleSubfolder = yearFolder & "sale" & saleNumber & "\"
        Debug.Print saleSubfolder
        If FSO.FolderExists(saleSubfolder) Then
            Set FSfolder = FSO.GetFolder(saleSubfolder)
            If FSfolder.Files.Count = saleSubfolderMaxFiles Then
                saleNumber = saleNumber + 1
            Else
                foundsaleSubfolder = True
            End If
        Else
            FSO.CreateFolder saleSubfolder
            foundsaleSubfolder = True
        End If
    Loop Until foundsaleSubfolder Or saleNumber > saleMaxNumber
   
    If saleNumber <= saleMaxNumber Then
        With ActiveWorkbook.ActiveSheet
            PDFfile = saleSubfolder & .Range("B2").Value & " " & Format(Now, "yyyymmdd hhmmss") & ".pdf"
            lastRowG = .Cells(.Rows.Count, "G").End(xlUp).Row
            .Range("A1:G" & lastRowG).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            MsgBox "Created " & PDFfile, vbInformation
        End With
    Else
        MsgBox "PDF not created because there are " & saleSubfolderMaxFiles & " files in all " & saleMaxNumber & " sale subfolders in " & yearFolder, vbExclamation
    End If
   
End Sub
 
Solution

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows
thanks so much I 'm wondered how isn't clear but you achieved what exactly what I want
well done ! just I want making clear about the value in b2 b2 =INV-BVN-1000 should increment INV-BVN-1001 , INV-BVN-1002 and so on
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,205
Members
425,266
Latest member
bishopc22

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
Top