Open date specific filenames based on current date

Question

Hi Everyone I am very new to coding but have been having some success by teaching myself, I learn or am able to figure things out most easily by reverse engineering I apologise if this seems overly simple but I have not been able to find any relevant examples to convert.

I am trying to write a code in VBA that opens and prints a set of files based on an array derived from the current date or alternatively from an input box

The filenames are all date specific ddmmyyarea1

i.e. 180818area1

What I need it to do is

Get todays date i.e. 17/08/18 +1 to have date 18/08/18

open the files that contain that date in their name 180818area1 then 180818area2 and so on

the reason I haven't asked for the whole script is that I prefer to learn by putting the basic building blocks together, however I am struggling on this aspect above

thanks in advance

Below is my 'estimate' of what I think the code might look like, once again I am very new to this:

Sub BatchPrintWordDocuments()
Dim objWordApplication As New Word.Application
Dim strFile As String
Dim strFolder As String

InputBox("Enter the date to print ddmmyy")  
strFolder = file path
strFile = Dir(strFolder & InputBox & Area* vbNormal)

While strFile <> ""
With objWordApplication
.Documents.Open (strFolder & strFile)
End With
strFile = Dir()
Wend

Set objWordApplication = Nothing

End Sub

please add code to your question to make the question more clear. — Alessandro Oliveira, Aug 17 '18 at 01:18
thanks, have added a non-working approximation of the code I need. Another option for getting the date would be an input box, I mainly just need it to open multiple files that all have a common date component in their date naming convention — AnalogueBullion, Aug 17 '18 at 01:47
I just corrected the indentation, next time double check when adding code or quoting. markdown syntax can be a little tricky. — Alessandro Oliveira, Aug 17 '18 at 01:54

donPablo · Accepted Answer · 2018-08-17T15:34:22.613

0

You might give this a try, you were very close. Put in your own FilePath

Sub BatchPrintWordDocuments()
    Dim objWordApplication As New Word.Application
    Dim strFile As String
    Dim strFolder As String

    Dim strDate As String   '<<< add this and use it
    strDate = InputBox("Enter the date to print ddmmyy")

    strFolder = "C:\donPablo\StackOverFlow\"  '<<< file path, with trailing slash
    strFile = Dir(strFolder & strDate & "Area*.XLS*", vbNormal)

    While strFile <> ""
        With objWordApplication
            .Documents.Open (strFolder & strFile)
            '<<< do your stuff here
            .Documents.Close (False)    '<<< close it, and don't save changes
        End With
        strFile = Dir()
    Wend

    Set objWordApplication = Nothing
End Sub

edited Aug 17 '18 at 15:34

answered Aug 17 '18 at 05:43

donPablo

1,937
1
13
18

Thanks donPablo, I was able to get this working but it has also helped me realize a new problem, I need it to try multiple versions of strFile as some times the doc is saved with lower case characters and sometimes with upper case, but I need it to open all docs before it starts the print run – AnalogueBullion Aug 20 '18 at 00:04
Take a look at this for Filename Case >> https://stackoverflow.com/questions/7199039/file-paths-in-windows-environment-not-case-sensitive – donPablo Aug 20 '18 at 00:48
Thanks donPablo, I managed to fix it using a For Next so it runs for upper and lower case scenarios. – AnalogueBullion Aug 20 '18 at 22:42

Open date specific filenames based on current date

1 Answers1