Loop through range to create new files with dynamic subfolders [Solved]

Question

Loop through range to create new files with dynamic subfolders [Solved]

Report

Mo_1001

Posts: 3
Registration date: Thursday October 1, 2020
Status: Member
Last seen: October 12, 2020

- Updated on Oct 15, 2020 at 11:45 AM
TrowaD

Posts: 2688
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 7, 2021

- Oct 22, 2020 at 11:57 AM

I would like to have suggestions for a VBA code that does this:

Generate an Excel file for each contract number. When copying the contract number to a specific cell in the workbook, a personalized Excel file is created for this contract number. I would thereafter like to save this personalized file in a distinct Excel file. The Excel file must be saved in a specific folder which path could be referred to the contract number, that is the information about the specific path would already be in the "model" Excel file.

Therefore, the steps would be :

1- In the sheet, "Info_base", copy the 1st contract number on top of a list which is saved on the range of cells A3:A22,
2- Copy this contract number, on another sheet "Formulaire", at cell D22. With this copy a personalized file is being created for that contract number.
3- Save this personalized file in a specific subfolder (where path could be a field in the "Info_base" database.) The name of the file would look like:
2021-01_Analysis_(Name of group)_#(contract number)(Note : Group name is also a field in the "Info_base" database.
4- Once the personalized Excel file had been saved, need to go back to the model "Excel" file.
5- Then redo steps 1 to 4, for each contract number (19 times)

I don't think it is so complicated for anyone who is a bit more used to code VBA loop than me. I think we might need to declare the range of cells where "Info_Base" is as a 2 dimensions table...but maybe not ?

Anyway, I would appreciate an helping hand on this. Thanks in advance !

Loop through range to create new files with dynamic subfolders
Batch file create text file with date and time ✓ - Forum - Programming
How to create rar file - How-To - Video software
Could not create output file delphi - How-To - Windows 7
Create bin file ✓ - Forum - Programming
Windows create empty file with size - How-To - Windows 7

2 replies

Answer 2 / 2

TrowaD

Posts: 2688
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 7, 2021

452
Oct 22, 2020 at 11:57 AM

Hi Mo,

Change the main path "C:\MyDocuments\", where your subfolders from column AC are located, on code line 13 and give the following code a try:

Sub RunMe()
Dim mCN, mGN, mSF As String
Dim mWB As String

mWB = ThisWorkbook.FullName
Sheets("Info_base").Select

For Each cell In Range("A3:A22")
    mCN = cell.Value
    mGN = Range("B" & cell.Row).Value
    mSF = Range("AC" & cell.Row).Value
    Sheets("Formulaire").Range("D22").Value = mCN
    ActiveWorkbook.SaveAs Filename:="C:\MyDocuments\" & mSF & "\" & "2021-01_Analysis_" & mGN & "_#" & mCN & ".xlsm"
Next cell

Workbooks.Open (mWB)
Workbooks("2021-01_Analysis_" & mGN & "_#" & mCN & ".xlsm").Close
End Sub

Reply Ask a question

TrowaD · Accepted Answer · 2020-10-15T11:42:38-0400

Answer 1 / 2

TrowaD

Posts: 2688
Registration date: Sunday September 12, 2010
Status: Moderator
Last seen: January 7, 2021

452
Oct 15, 2020 at 11:42 AM

Hi Mo,

Some questions for you:
1) What do you mean by "personalized file"? Is that a new file with the "Formulaire" sheet copied into it?
2) Where are the subfolder names located on the "Info_base" sheet?
3) Where are the Groupnames located on the "Info_base" sheet?

Best regards,
Trowa

Report

Mo_1001
Oct 15, 2020 at 02:26 PM

Thank you Trowa ! here are my answers to your questions :
1- It will include all the sheets from the "model" excel file, including '"Formulaire"
2- The subfolder names would be in the 29th column, that is column AC, with the contract numbers being in column A of the sheet "Info-base"
3- The groupnames are on column B, just right beside the contract numbers.

Thanks again

Loop through range to create new files with dynamic subfolders [Solved]

2 replies

Your reply

Subscribe To Our Newsletter!