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

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Dim fname1 As Variant
    Dim fpath As String, fname As String
    
    Do
        fname1 = InputBox("enter date YYYYMMDD", "Enter Date")
'cancel pressed
        If StrPtr(fname1) = 0 Then Exit Sub
    Loop Until Len(fname1) = 8 And IsNumeric(fname1)
    
    fname = fname1 & "_country.xlsm"
    
'subtract 1 from month value entered
    fname1 = Replace(fname1, CInt(Mid(fname1, 5, 2)), CInt(Mid(fname1, 5, 2)) - 1)
    
    fpath = "C:\Users\Desktop\"
    
    Workbooks.Open Filename:="C:\Users\kc\Desktop\folder\" & fname1 & "_data.xlsx"
    
    ActiveWorkbook.SaveAs Filename:=fpath & fname, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Dave

 

[JoeMo]

Hi,
untested but see if this update to your code does what you want

Dim fname1 As Variant
    Dim fpath As String, fname As String
    
    Do
        fname1 = InputBox("enter date YYYYMMDD", "Enter Date")
'cancel pressed
        If StrPtr(fname1) = 0 Then Exit Sub
    Loop Until Len(fname1) = 8 And IsNumeric(fname1)
    
    fname = fname1 & "_country.xlsm"
    
'subtract 1 from month value entered
    fname1 = Replace(fname1, CInt(Mid(fname1, 5, 2)), CInt(Mid(fname1, 5, 2)) - 1)
    
    fpath = "C:\Users\Desktop\"
    
    Workbooks.Open Filename:="C:\Users\kc\Desktop\folder\" & fname1 & "_data.xlsx"
    
    ActiveWorkbook.SaveAs Filename:=fpath & fname, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Dave

What happens come January when MM = 01 and last month's YYYY is YYYY - 1?

 

[dmt32]

What happens come January when MM = 01 and last month's YYYY is YYYY - 1?

Well I guess its not going to work - That's problem when been in sun all day & don't think things through before posting!

 

[JoeMo]

Well I guess its not going to work - That's problem when been in sun all day & don't think things through before posting!

OP also needs to tell us if the DD the user enters will always be 30 as in the example in post #1 . If not, then how is the DD of last month's file determined?

 

[dmt32]

Hi,
As JoeMo kindly pointed out, my suggestion at manipulating the month part of the string will fail with some date values.
Ignore my 1^st post & see if this update will work for you.

 Dim fname1 As Variant    
 Dim fpath As String, fname As String
    
    Do
        fname1 = InputBox("enter date YYYYMMDD", "Enter Date")
'cancel pressed
        If StrPtr(fname1) = 0 Then Exit Sub
    Loop Until Len(fname1) = 8 And IsNumeric(fname1)
    
    fname = fname1 & "_country.xlsm"
    
    fname1 = DateSerial(CInt(Left(fname1, 4)), CInt(Mid(fname1, 5, 2)), CInt(Right(fname1, 2)))
'subtract 1 from month value entered
    fname1 = CStr(Format(DateAdd("m", -1, fname1), "YYYYMMDD"))
    
    fpath = "C:\Users\Desktop\"
    
    Workbooks.Open Filename:="C:\Users\kc\Desktop\folder\" & fname1 & "_data.xlsx"
    
    ActiveWorkbook.SaveAs Filename:=fpath & fname, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Hope this takes into account different dates but perhaps JoeMo will kindly point out if I have overlooked something.

Dave

 

[JoeMo]

Hi,
As JoeMo kindly pointed out, my suggestion at manipulating the month part of the string will fail with some date values.
Ignore my 1^st post & see if this update will work for you.

 Dim fname1 As Variant    
 Dim fpath As String, fname As String
    
    Do
        fname1 = InputBox("enter date YYYYMMDD", "Enter Date")
'cancel pressed
        If StrPtr(fname1) = 0 Then Exit Sub
    Loop Until Len(fname1) = 8 And IsNumeric(fname1)
    
    fname = fname1 & "_country.xlsm"
    
    fname1 = DateSerial(CInt(Left(fname1, 4)), CInt(Mid(fname1, 5, 2)), CInt(Right(fname1, 2)))
'subtract 1 from month value entered
    fname1 = CStr(Format(DateAdd("m", -1, fname1), "YYYYMMDD"))
    
    fpath = "C:\Users\Desktop\"
    
    Workbooks.Open Filename:="C:\Users\kc\Desktop\folder\" & fname1 & "_data.xlsx"
    
    ActiveWorkbook.SaveAs Filename:=fpath & fname, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Hope this takes into account different dates but perhaps JoeMo will kindly point out if I have overlooked something.

Dave

Better, assuming the prior month DD is same as user-entered DD.

 

[dmt32]

Better, assuming the prior month DD is same as user-entered DD.

I do get that but await to see OPs response to your comment before adjusting solution.

Dave

 

[lillypop]

Thanks guys.

The DD will change depending on the last calendar day. eg. 20180630, 20180731...etc.

I have not tested the code, because there is some data exporting work to do before I advance further. I will need to export query results from sql into excel first, but I will post this as a separate question.

 

[dmt32]

Thanks guys.

The DD will change depending on the last calendar day. eg. 20180630, 20180731...etc.

I have not tested the code, because there is some data exporting work to do before I advance further. I will need to export query results from sql into excel first, but I will post this as a separate question.

Hi,
based on what you say, that answers point I think JoeMo was making & hopefully, amended code will do what you want - If not, can be modified to open on Year / Month values only if needed.

Post back if update needed.

Dave