Open 2 Separate Excel Files Based on Date or Inputbox

[Hobolord]

Hello,

I am trying to create a report from two separate .xls files. I want a macro to open these two files from a button form so that the end user does not have to manually open any files.

The two separate files will be named by month and date in the format "MMM-YY". For example. if it is August 2015, I want the files Jul-15.xls and Jun-15.xls to be opened. If it is January 2017, I want Dec-16.xls and Nov-16.xls to be opened.

I have considered using Inputbox for this and having the user enter in the date, but that would require them to put the date in the correct format "MMM-YY", and I also couldn't figure out how to have the month before that be opened as well without doing a second Inputbox.

Is there a way for VBA to recognize the date and know which files I want opened, or is there another method using Inputbox that wouldn't require two separate boxes?


Thanks in advance for your help!

 

[portews]

Try this:

it should open the files for the two months before, if it can find them. If it can't open a file it will open the Find File dialog box and let you pick it. If you cancel, it will go on to try to find the next file.

Sub openTwoMonths()

'go to the oldest month first
For i = 2 To 1 Step -1


'check to see if the file is there
If Dir(MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls") <> "" Then
       
       'if it is, open it
        Workbooks.Open (MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls")
Else
     
     'if not, call the file dialog box
     strFileToOpen = Application.GetOpenFilename _
          (Title:="Find File " & MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls", _
          FileFilter:="Excel Files *.xls* (*.xls*),")
     
     'if you pick a file, open it
     If strFileToOpen = True Then
         Workbooks.Open Filename:=strFileToOpen
     End If
End If


'go to the next file
Next
End Sub

 

[dmt32]

Hi,
welcome to the board.

see if following code does what you want:

Place code in standard module & assign to your button.

Sub OpenFiles()
    Dim FileDate As Variant
    Dim FileName As String, FilePath As String
    Dim Default As String, Prompt As String, Title As String
    Dim i As Integer


    Prompt = "Enter Month & Year For Required Files"
    Title = "Open Files"
    
    FilePath = "C:\my file path here\"
    
    Do
    Default = Format(Date, "MMM YY")
        FileDate = InputBox(Prompt, Title, Default)
        'cancel pressed
        If StrPtr(FileDate) = 0 Then Exit Sub
        'input error
        If Len(FileDate) > 0 And Not IsDate(FileDate) Then MsgBox "Please Enter a Valid Date", 48, "Error"
    
    Loop Until IsDate(FileDate)
    
    On Error GoTo myerror
    For i = 1 To 2
        FileName = Format(DateSerial(Year(FileDate), Month(FileDate) - i, 1), "MMM-YY")
        Workbooks.Open FilePath & FileName & ".xls"
    Next i


myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Providing users enter a valid date, code will apply correct FileName format.

Change the FilePath shown in RED as required.

Hope Helpful

Dave

 

[Hobolord]

Portews - I looked over your code, but I am a not as experienced as I'd like, and probably missed the part where I was supposed to define the file path. Thank you for responding, though, it's very much appreciated.

Dave - Thank you so much as well! Your code worked perfectly!

I look forward to learning a lot more from VBA veterans like the two of you. Have a great day!

 

[portews]

Here's my code with a place to hard code the path.

Sub openTwoMonths()
Dim FilePath As String


FilePath = [COLOR=#ff0000]"C:\my file path here\"[/COLOR]


'go to the oldest month first
For i = 2 To 1 Step -1




'check to see if the file is there
If Dir(FilePath & MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls") <> "" Then
       
       'if it is, open it
        Workbooks.Open (FilePath & MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls")
Else
     
     'if not, call the file dialog box
     strFileToOpen = FilePath
     strFileToOpen = Application.GetOpenFilename _
          (Title:="Find File " & MonthName(Month(Now()) - i, 3) & "-" & Right(Year(Now()), 2) & ".xls", _
          FileFilter:="Excel Files *.xls* (*.xls*),")
     
     'if you pick a file, open it
     If strFileToOpen = True Then
         Workbooks.Open Filename:=strFileToOpen
     End If
End If




'go to the next file
Next
End Sub

 

[dmt32]

Dave - Thank you so much as well! Your code worked perfectly!

I look forward to learning a lot more from VBA veterans like the two of you. Have a great day!

You are welcome & glad solution worked ok for you.

If new to VBA there are sites available that offer free guidance / sample files in Excel which are always worth a visit.

This site: http://www.contextures.com/ is just one example & of course, there is this Forum.

Dave