Opening files in folder with loop
<p>I am trying to open excel files from a folder and copy and paste details into a master folder. Within each first level folder, there are some .xlsm files available to open, but there are also some within a second level folder within the first level folder (so an extra \filepath).</p>

<p>Right now, I am trying to figure out how to loop through the first level folder and open the "loose" workbooks (files in the first level folder that ARE NOT in a second level folder).</p>

<p>This is what I have. Please note I will eventually add another "level" of folders, hence the large amount of variables:</p>

<pre><code>Sub Compile_RFQ_Parts()

Dim RFQ_Ecoat As String 'file path for RFQ folder in ecoat folder
Dim RFQ_VendorFolder As String  'file path for specific vendor
Dim RFQ_FileFolder As String    'file path for RFQ folder within a vendor folder
Dim RFQ_File As String  'file within RFQ #### style folder in vendor folder
Dim RFQ_FileLooseVendor As String 'loose file in vendor folder
Dim RFQ_FileLooseEcoat As String    'loose file in ecoat RFQ folder
Dim RFQ_Num As String   'Number of RFQ from formula
Dim DumpLocation As String  'Bulk workbook
Dim DumpSheet As String     'Target sheet in bulk workbook
Dim NextOpenCellRow As Integer 'next open cell at the dump location
Dim RFQcell As Range  'counter for each cell in "part number" range in RFQ file
Dim RFQrange As Range   'range to look for part numbers in RFQ file
</code></pre>

<p>Define Variables</p>

<pre><code>RFQ_Ecoat = "S:\FACILITY\Sales\RFQ"
RFQ_VendorFolder = RFQ_Ecoat &amp; "\Jensen Metals"
RFQ_FileLooseVendor = Dir(RFQ_VendorFolder &amp; "\*.xlsm") 'wildcard to open spreadsheets

DumpLocation = "RFQ_Compile test target.xlsx"
DumpSheet = "Sheet1"
</code></pre>

<p>Begin Loop</p>

<pre><code>'######loop through each .xlsm file in a Vendor folder (not in RFQ folder    but loose in vendor folder)######



Do While RFQ_FileLooseVendor &lt;&gt; ""

Application.DisplayAlerts = False
Workbooks.Open Filename:=RFQ_VendorFolder &amp; "\" &amp; RFQ_FileLooseVendor,       UpdateLinks:=False
Application.DisplayAlerts = True

'vvvvvv%%%%%%%%%Copy and pasting operations%%%%%%%%%vvvvvv
</code></pre>

<p>Next File in loop within Folder</p>

<pre><code>Next


'#########close RFQ and loop to the next RFQ_FileLooseVendor#########

Application.DisplayAlerts = False
Workbooks(RFQ_FileLooseVendor).Close
Application.DisplayAlerts = True
RFQ_FileLooseVendor = Dir() '&lt;&lt;&lt;This clears my RFQ_FileLooseVendor string, which ends my Do While loop before getting to other files

Loop


End Sub
</code></pre>

<p>When I get to the RFQ_FileLooseVendor=dir() line, it clears that variable (makes it = ""). I have seen this on countless other forums and I can't understand how it does not immediately end the Do While loop for everyone else like it does for me.</p>