CODEWITHSUNDEEP
vbaexcel
Charlie
Charlie

Reputation: 101

Excel Generate New Workbook in a dynamic directory Path

I am using a workbook that generates reports according to the country selected. Each country uses an specific path directory.

When it comes to import information form their root folder its OK.

My problem is when I generate a new workbook with the report. I try to save it in the specific location which changes with the country:

'Generate a new workbook refering to the first Worksheet
 Set WkReport = Workbooks.Add(xlWBATWorksheet)
 With WkReport
 // Skip selecting the sheet, just reference it explicitly and copy it      after//
 // the blank sheet in the new wb.
ThisWorkbook.Worksheets("REPORT").Copy after:=.Worksheets(.Worksheets.Count)
End With
// Kill alerts, delete the blank sheet in the new wb and turn alerts back on//
Application.DisplayAlerts = False

With WkReport

.SaveAs Filename:="L:\Fold1\Fold2\Fold3\" & rngFolder & "\" & rngYear & "\" & rngMonth &"\"& rngName & "_Report_" & rngDate & ".xlsx"

End With

Application.DisplayAlerts = True'`enter code here`

(rngSmthing are ranges referring to cells in the workbook)

All those are dynamics ranges that changes according to information introduced by the user.

Therefore when I create the workbook it must be saved in different location according to this information.

Name of the file contains another dynamic range "rngName" followed up by Report and "rngDate": Filename = rngName_Report_rngDate.xlsx

What my code does is to save in L:\Fold1\Fold2\Fold3 with the filename Report.xlsx

Examples of Path directories if user selects... Germany: L:Folder1\Folder2\Folder3\Germany\2015\06-2015\GE_Report_31-06-15.xlsx Hungary: L:Folder1\Folder2\Folder3\Hungary\2015\06_2015\HU_Report_31-06-15.xlsx

!PROBLEM SOLVED! I simply forgot to set the rngSmthng Variables... (Clap Clap) Anyway, someone may find it useful in case that you want to set different save paths according to your ranges:

'cellRef is a named cell within the workbook where user selects data
rngName = ws.Range("cellRef").Value

In that way you have a dynamic path finder.

Upvotes: 1

Views: 4243

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Glad you found the answer. As a side-note - this is how I would write the procedure.

Sub Test()

    Dim wkReport As Workbook
    Dim sFolder As String
    Dim sPath As String
    Dim rngFolder As Range
    Dim rngName As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set rngFolder = .Range("A1")
        Set rngName = .Range("A2")
    End With

    sFolder = "L:\Fold1\Fold2\Fold3\" & rngFolder & "\" & Format(Date, "yyyy\\mm mmm\\")
    CreateFolder sFolder

    sPath = sFolder & rngName & "_Report_" & Format(Date, "dd-mm-yy") & ".xlsx"

    Set wkReport = Workbooks.Add(xlWBATWorksheet)
    With wkReport
        ThisWorkbook.Worksheets("REPORT").Copy after:=.Worksheets(.Worksheets.Count)
        .Worksheets(1).Delete
        .SaveAs sPath, ThisWorkbook.FileFormat
    End With

End Sub

' Purpose   : Will Recursively Build A Directory Tree
Sub CreateFolder(Folder)
On Error Resume Next
Dim objFSO As Object: Set objFSO = CreateObject("Scripting.FileSystemObject")
    If Folder <> "" Then
        If Not objFSO.FileExists(objFSO.GetParentFolderName(Folder)) Then
            Call CreateFolder(objFSO.GetParentFolderName(Folder))
        End If
        objFSO.CreateFolder (Folder)
    End If
End Sub

Note:
Format(Date, "yyyy\\mm mmm\\") will return 2015\12 Dec\.
Format(Date, "yyyy\mm mmm\") will return 2015m12 Dec.

Upvotes: 2

Charlie
Charlie

Reputation: 101

Really sorry guys... And many thanks for your help... no way you could have guessed it.

The problem was that those variables I have them set in a different macro, which I completely forgot... so of course it does not recognize the variables.. because I didnt create them in this Macro!!

Again my apologies should review my code twice before posting

Upvotes: 0

Related Questions