Create directory and subdirectory based on cell data and with file name

[Caveman1964]

Hi, I am stuck.
I want to create a folder named from a cell, then another directory from a cell then a report filename from cell + report for example 123456report.
My below VBA worked with one sub directory but not two.....the below is what I did to attempt this but it is not working....it creates the first folder but not the second. Need help really bad. Cant figure out.
I am looking to do this;
P:\Job Packets\Product\Lot\SallyReport.xlsm

Below does not work, it makes the Product folder but nothing else.

Sub createreports ()
Windows("Reportsfromusers.xlsm").Activate
With Sheets(3)
If .Range("H2").Value = vbNullString Then Exit Sub
On Error Resume Next
MkDir "P:\Job Packets" & .Range("H2").Value
On Error GoTo 0
NewFN = "P:\Job Packets" & .Range("H2").Value & "" & .Range("E3").Value & "" & .Range("E3").Value & "LotMaterial" & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=52
ActiveWorkbook.Close

Appreciate any help.

 

[edmitchell]

Hi

could you confirm what values are in cells H2, and E3? (I presume H2="\Product", but wasn't sure for E3 as you example looks different from the formula?)

thanks

 

[Caveman1964]

Hi

could you confirm what values are in cells H2, and E3? (I presume H2="\Product", but wasn't sure for E3 as you example looks different from the formula?)

thanks

Ah yes....sure.....H2 is a product, (there will be many) and E3 is a lot number. There will be many lot numbers for each product code.
Thanks!

 

[edmitchell]

Hi

sorry so

H2='Product'
E3='Lot'

?

just trying to understand the cell values to the example ('P:\Job Packets\Product\Lot\SallyReport.xlsm')

thanks

 

[Caveman1964]

Hi

sorry so

H2='Product'
E3='Lot'

?

just trying to understand the cell values to the example ('P:\Job Packets\Product\Lot\SallyReport.xlsm')

thanks

You see now you are dealing with an idiot lol.....I have no business attempting this so appreciate your help.
P:\Job Packets\H2\E3\e3+Report.xlsm
So, "P:\Job packets" is static, will always be there. H2 = a product code which will be different most times. E3 is a lot number that will be different each time. So a subfolder representing a product code and a subfolder under that representing a lot number. The subfolder for a given product will end up having multiple lot numbers inside it....then a file xxx.xlsm file inside each lot number.
so the directory would have something like this after a day or two.
P:\Job Packets\Wheelbarrel(H2)\12345678(E3)\12345678report.xlsm
P:\Job Packets\Wheelbarrel(H2)\23456789(E3)\23456789report.xlsm

P:\Job Packets\Cart(H2)\34567890(E3)\34567890report.xlsm
P:\Job Packets\Cart(H2)\45678901(E3)\45678901report.xlsm

So H2 (Product) is a variable that will change but can be same at times, E3 is a variable that will always change because it is a lot number, then the file stored will have that lot number in front of a word "report" .xlsm file.

I hope this makes sense.
And thank you for spending the time to filter through stuff.

 

[edmitchell]

No worries:

I hope this is what you need (worked for me!)

Sub createreports()
 Windows("Reportsfromusers.xlsm").Activate
 
 With Sheets(3)
 
 'Check to see if data is complete
 If .Range("H2").Value = vbNullString Or .Range("E3") = vbNullString Then
    Exit Sub
 End If
 
 'Set high level folder
 TopFolder = "P:\Job Packets\"
 
 On Error Resume Next
 
 MkDir TopFolder & .Range("H2").Value
 
 SubFolder = TopFolder & .Range("H2").Value & "\" & .Range("E3").Value & "\"
 
 MkDir SubFolder
 
 On Error GoTo 0
 
 NewFN = SubFolder & .Range("E3").Value & "report" & ".xlsm"
 
 ActiveWorkbook.SaveAs NewFN, FileFormat:=52
 End With
 
 ActiveWorkbook.Close
 
 End Sub

Regards

 

[Caveman1964]

No worries:

I hope this is what you need (worked for me!)

Sub createreports()
 Windows("Reportsfromusers.xlsm").Activate
 
 With Sheets(3)
 
 'Check to see if data is complete
 If .Range("H2").Value = vbNullString Or .Range("E3") = vbNullString Then
    Exit Sub
 End If
 
 'Set high level folder
 TopFolder = "P:\Job Packets\"
 
 On Error Resume Next
 
 MkDir TopFolder & .Range("H2").Value
 
 SubFolder = TopFolder & .Range("H2").Value & "\" & .Range("E3").Value & "\"
 
 MkDir SubFolder
 
 On Error GoTo 0
 
 NewFN = SubFolder & .Range("E3").Value & "report" & ".xlsm"
 
 ActiveWorkbook.SaveAs NewFN, FileFormat:=52
 End With
 
 ActiveWorkbook.Close
 
 End Sub

Regards

Ok great! It looks good! I will be able to check in a few hours.
I was blessed today. Thank you Ed so much for spending the time to do this.