Macro with dynamic date in folder path

jww1985

New Member
Joined
May 4, 2016
Messages
1
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
If you mean 05 for month 5 then:
Code:
    Set wbCopy = Workbooks.Open("X:\...\2016\" & Format(Date, "YYYY-MM") & "\importfile.csv")
 

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
546
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.

Code:
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

Active Member
Joined
Apr 18, 2007
Messages
419
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.

Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
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:
Code:
Set wbCopy = Workbooks.Open("X:\...\2016\" & Format(DateAdd("m", -1, Date), "YYYY-MM") & "\importfile.csv")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

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