Open the file from previous month of User's input date in dialog box

[lillypop]

Hello,

I am trying to create a vba that will allow a user to enter his chosen date in the dialog box (eg. 20160730), then open a file from the previous month of his chosen date, as there is only 1 file per month in the folder (eg. 20160630_country.xlsm), and rename this file using the user's chosen date YYYYMMDD entered in the dialog box (20160730_country_xlsm). I've managed to get most done, except the part that should identify the file from the previous month. Any idea?



Dim fname As String
Dim fpath As String


fname1 = InputBox("enter date YYYYMMDD")
fname = fname1 & "_country.xlsm"
fpath = "C:\Users\Desktop"

Workbooks.Open Filename:= _
C:\Users\kc\Desktop\folder" & fname1 - 1 & "_data.xlsx" <---error here

ActiveWorkbook.SaveAs Filename:=fpath & fname, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[lillypop]

Thanks. Will this code also work in January?

 

[dmt32]

Thanks. Will this code also work in January?

Hi,
It should do, updated code uses Cint function to coerce inpubox string year, month and day parts to numbers. These are then passed to DateSerial Function which returns a Date. .
DateAdd Function is then used to subtract 1 month from that date. If in you enter 20180131 code should return 20171231.

However, this only works if you save your files in a consistent way. For instance, in your if user enters 20180731 but previous file was saved 20180622 code won't find the file which is the concern JoeMo I think had.

If this does proved to be an issue, it can be overcome by modifying code to open files on Year Month values only.


Dave