Macro with dynamic date in folder path

[jww1985]

Hi,

I'm quite new to using VBA, and already very enthusiastic! I am trying to create a macro where I am able import sheets from other workbooks automatically into my workbook. I have managed to do so with the following macro:

Sub import()
Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
Dim wbPaste As Workbook
Dim wsPaste As Worksheet
Dim rngPaste As Range
Dim Pivot As PivotTable

Set wbCopy = Workbooks.Open("X:\...\2016\2016-04\importfile.csv")
Set wsCopy = wbCopy.Worksheets("import")
Set rngCopy = wsCopy.Range("a:xfd").EntireColumn
Set wbPaste = Workbooks("myfile.xlsm")
Set wsPaste = wbPaste.Worksheets("import")
Set rngPaste = wsPaste.Range("a1")

rngCopy.Copy
rngPaste.PasteSpecial

Workbooks.Application.CutCopyMode = False
Workbooks("importfile.csv").Close False

Range("V3") = "x"
Range("Y3") = "xx"


ActiveWorkbook.RefreshAll




End Sub


My question is: How do I make a dynamic formula in such a way that for example when in month no. 5, the macro knows it has to search in folder "2016-04" and so on? And in a file named for example "importfile 2016-04.xlsx".

I hope my question is clear, if not: please let me know so I can better specify.


Thanks

 

[John_w]

If you mean 05 for month 5 then:

    Set wbCopy = Workbooks.Open("X:\...\2016\" & Format(Date, "YYYY-MM") & "\importfile.csv")

 

[Rhodie72]

I hope this does what you need it to do. I've been coding VB for a few years. This is my first CODE post as a reply. One of the critical issues you were going to face was ultimately what would happen in January... when the date was wrong for the previous month AND year.

Good luck with this.

Sub Dynamic_date_in_folder_path()

Dim MyYear As Long, i As Long, setPath As String, myPath() As Variant

msgbox"When you choose the directory, make sure it's the one" & chr(13) & _
" with all the folders of years",vbcritical,"Dynamic date app"

setPath  = InputBox("What is the base path to the directory of each year?", "Set  working directory", GetSetting("DynDate", "Folders", "setPath"))

    Select Case setPath

        Case ""
            MsgBox "Path not set!"

        Case Else

        SaveSetting "DynDate", "Folders", "setPath", setPath

        Let MyYear = Year(Now())
        Let myPath = Array(setPath, "\importfile.csv")

            Select Case Month(Now())

                Case 1
                    Set wbCopy = "Workbooks.Open(" & myPath(0) & _
                    (MyYear - 1) & "\" & (MyYear - 1) & "-" & Month(Now()) + 11 & myPath(1)

                Case 2 To 10
                    Set wbCopy = "Workbooks.Open(" & myPath(0) & _
                    MyYear & "-0" & Month(Now()) - 1 & myPath(1)

                Case 11 To 12
                    Set wbCopy = "Workbooks.Open" & myPath(0) & _
                    MyYear & "\" & MyYear & "-" & Month(Now()) - 1 & myPath(1)

            End Select
    End Select

End Sub

 

[Atroxell]

Hi,

I'm quite new to using VBA, and already very enthusiastic! I am trying to create a macro where I am able import sheets from other workbooks automatically into my workbook. I have managed to do so with the following macro:

Sub import()
Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
Dim wbPaste As Workbook
Dim wsPaste As Worksheet
Dim rngPaste As Range
Dim Pivot As PivotTable

Set wbCopy = Workbooks.Open("X:\...\2016\2016-04\importfile.csv")
Set wsCopy = wbCopy.Worksheets("import")
Set rngCopy = wsCopy.Range("a:xfd").EntireColumn
Set wbPaste = Workbooks("myfile.xlsm")
Set wsPaste = wbPaste.Worksheets("import")
Set rngPaste = wsPaste.Range("a1")

rngCopy.Copy
rngPaste.PasteSpecial

Workbooks.Application.CutCopyMode = False
Workbooks("importfile.csv").Close False

Range("V3") = "x"
Range("Y3") = "xx"


ActiveWorkbook.RefreshAll




End Sub


My question is: How do I make a dynamic formula in such a way that for example when in month no. 5, the macro knows it has to search in folder "2016-04" and so on? And in a file named for example "importfile 2016-04.xlsx".

I hope my question is clear, if not: please let me know so I can better specify.


Thanks

I use this type of concept daily. My solution was to build a custom sub() that calculates all the needed date params at one time. It's fairly easy to add a paramter that will pass the date you want to use as a parameter to calculate off. Call your variables as Public and it's a quick one-liner that will give you all your numbers in text format.

Sub varDate()
      mm = Format(Date, "mm")
      dd = Format(Date, "dd")
      yy = Format(Date, "yy")
      yyyy = Format(Date, "yyyy")
      monthDir = MonthName(Month(Date), True) & yy
      dateStr = Format(Date, "yyyy_mm_dd")
End Sub

HTH

 

[John_w]

My question is: How do I make a dynamic formula in such a way that for example when in month no. 5, the macro knows it has to search in folder "2016-04" and so on? And in a file named for example "importfile 2016-04.xlsx"

I may have misunderstood you with my first reply. If you want the year-month prior to the current date's month then:

Set wbCopy = Workbooks.Open("X:\...\2016\" & Format(DateAdd("m", -1, Date), "YYYY-MM") & "\importfile.csv")