CODEWITHSUNDEEP
excelvbaloops
ladymrt
ladymrt

Reputation: 95

Do While Loop to Open Several Workbooks with Variable in the File Name

Fair warning, this is my first (and failed) attempt at using a loop statement.

I have a folder containing a lot of workbooks that all have the date as part of their file name. I would like to create a macro that uses a date provided by a user to open all workbooks with that date in their name.

My current Loop statement is just re-opening the same file over and over again instead of opening all of the files with that date in the name and then exiting the loop, even though there are several files in the folder with that date in the name. This is what I have so far...

Dim finddate As Variant
Dim formatedate As String
Dim mypath As String


finddate = InputBox("Enter Date to Show Records")
Range("A1") = finddate
formatedate = ActiveSheet.Range("A2").Value


mypath = "filepathhere\???????" & formatedate & ".xlsm"

Do While mypath <> ""
Workbooks.Open Filename:=mypath
Loop

A formula in A2 formats whatever date the user enters to match the way the files are named. Is there something else that I need to do to tell it to move on from the first file and look for others in the folder? Also I'm not sure how to exit the loop so once it (hopefully) opens the files with that date, it stops looping through the folder.

Upvotes: 0

Views: 546

Answers (2)

LFB
LFB

Reputation: 686

You need to garantee that the formatedate changes its value before calling Loop, so it would probably look something like this:

Dim finddate As Variant
Dim formatedate As String
Dim mypath As String

finddate = InputBox("Enter Date to Show Records")    
Range("A1") = finddate

Do While mypath <> ""

        formatedate = ActiveSheet.Range("A2").Value
        mypath = "filepathhere\???????" & formatedate & ".xlsm"
        Workbooks.Open Filename:=mypath

        'IMPORTANT, insert some code here that garantees that
        'cell A1 and/or A2 has a different file name, otherwise,
        'it will keep passing the same value over and over

Loop

You have to somehow garantee that the value in A2. Also, you have to garantee that eventually mypath = "" otherwise the loop will never end. Somethings about the description of your issue wew not completely clear to me, but I think the main concept is:

Always have the variable in the While condition change before calling Loop, and always make sure eventually the condition to break the loop will be met.

Other than that, I think you can adapt the answer if it doesn't completely fit the structure of your workbook.

Upvotes: 1

William Tong
William Tong

Reputation: 475

A simple work around would be deleting the file after using it. Of course you should backup your files somewhere else. And usually I will rename the file first.

       Set fso = CreateObject("Scripting.FileSystemObject")
       Set fsoFolder = fso.getfolder(filePath)
       fsoFile.Name = "d:\folder\blablabla.xls"
        ' do your things

       FileSystem.Kill "d:\folder\blablabla.xls" 'delete file

Upvotes: 0

Related Questions