Loop Through Files In A Folder Using VBA

[DocAElstein]

This is post number 16536
Links to here
https://excelfox.com/forum/showthrea...ll=1#post16536
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA?p=15633&viewfull=1#post16536
https://excelfox.com/forum/showthrea...age2#post16536
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA/page2#post16536

_................................................


I am wondering at this point if I might like to see all the files in some way in a forum post. A table is going to be very big. Putting it in a code window will help to get the scroll bars, but I expect it will still be way to big.
A simple text file is probably as best you can get. This looses no ease of visibility in the vertical because a line feed is a line feed.
The horizontal is a problem. Lets look at ways to get over that
Here are some interesting ideas to get over the problem of a typical forum editor “eating” spaces more than 1
http://www.eileenslounge.com/viewtopic.php?f=26&t=22603 - But that in itself produces a lot of extra text.
A nice coincidence associated with how Excel uses the clipboard gives us another possibility:
( Once again the vertical is not an issue. Mostly in computing we use the traditional “invisible” characters in a text string which historically were needed to tell a printer to feed another line ( Line Feed , vbLf ) and to make the printing carriage return to the left ( Carriage Return, vbCr ) )
When Excel copies to the clipboard, an Excel Clipboard appears to store links allowing a subsequent paste to transfer all formatting from a cell, but also, interesting to us, a windows clipboard holds
_ the values,
_the two characters to indicate a new line ( vbCr & vbLf )
_ and, most useful to us, a “cell wall” or cell separator is indicated by another “invisible” character , the tab ( vbTab )


We can clarify and demo all that with a simple macro example.
The following macro at line number 1, selects an arbitrary 3 row, 2 column range.
That range can be filed or empty, but for the sake of this demo, a line after the selection, line number 2 puts some arbitrary text info in, which is the item number of the cell , relative to the top left cell of the range. ( If that Item stuff confuses you, or does not work , then just 'comment out all the lines up to and including line 2, and the demo will continue for empty cells. )

The macro then does a .Copy in line 3
As far as I can tell, that .Copy , does 3 things related to the clipboard(s)
_(i) Excel Clipboard info is made/ held , related to linking to the cells to allow Excel pasting ( .PasteSpecial ), options to allow all formatting of cells to be copied within Excel
_(ii) Office Clipboard info is either doing a restricted version of what (i) does, or possibly stores a lot of information, allowing some of the formatting to be copied into other Office applications ,- a typical example would be allowing an Excel spreadsheet range, to be copied into Word as a table having similar looking table like formatting.
_(ii) The Windows Clipboard, what we are interested in, will be a simple text of both or cells values and the discussed “invisible” characters which help us to determine the row and column , or cell, to which the values come from.

Depending on how and where you view the returned text , ( which is in variable TxtOut ) , you may see some structured table like formatting such as this:

 Rng_Item(1, 1) and Rng_Item( 1 )    Rng_Item(1, 2) and Rng_Item( 2 )
 Rng_Item(2, 1) and Rng_Item( 3 )    Rng_Item(2, 2) and Rng_Item( 4 )
 Rng_Item(3, 1) and Rng_Item( 5 )    Rng_Item(3, 2) and Rng_Item( 6 )

( I can’t view that text by a simple paste into the forum, as the forum editor will “eat” spaces more than 1, reducing all spaces in between text to just 1. That makes it more difficult to see to which cell the text belongs )

Row\Col	C	D
7511	Rng_Item(1, 1) and Rng_Item( 1 )	Rng_Item(1, 2) and Rng_Item( 2 )
7512	Rng_Item(2, 1) and Rng_Item( 3 )	Rng_Item(2, 2) and Rng_Item( 4 )
7513	Rng_Item(3, 1) and Rng_Item( 5 )	Rng_Item(3, 2) and Rng_Item( 6 )

To get a more visual descriptive look at the exact characters there, I wrote a function to return me each character, including any “invisible” ones. The last line of the macro uses that function.

Here, a typical output from that function , ( This form is how you would create that text string in a VBA coding - indicivdual text bits and any “hidden” characters need to be joined with a & – that is just VBA syntax for creating a sting in a code line. )
_ for case of the added values in the cells

 "Rng" & "_" & "Item" & "(" & "1" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 1 " & ")" & vbTab & "Rng" & "_" & "Item" & "(" & "1" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 2 " & ")" & vbCr & vbLf & "Rng" & "_" & "Item" & "(" & "2" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 3 " & ")" & vbTab & "Rng" & "_" & "Item" & "(" & "2" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 4 " & ")" & vbCr & vbLf & "Rng" & "_" & "Item" & "(" & "3" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 5 " & ")" & vbTab & "Rng" & "_" & "Item" & "(" & "3" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 6 " & ")" & vbCr & vbLf

and finally
_ for case of empty cells

 vbTab & vbCr & vbLf & vbTab & vbCr & vbLf & vbTab & vbCr & vbLf

Comparing those, we see that we start with a cell’s value, then a “cell wall” , indicated by the “invisible” character vbTab , then the second cell’s value, then a new line start is indicated by the pair of “invisible” characters, vbCr & vbLf, and so on.

For the sake of clarity, the next view is another view given by my function in a text file, for the two cases

 "Rng" & "_" & "Item" & "(" & "1" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 1 " & ")" & vbTab &  "Rng" & "_" & "Item" & "(" & "1" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 2 " & ")" & vbCr & vbLf
 "Rng" & "_" & "Item" & "(" & "2" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 3 " & ")" & vbTab &  "Rng" & "_" & "Item" & "(" & "2" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 4 " & ")" &  vbCr & vbLf
 "Rng" & "_" & "Item" & "(" & "3" & "," & " 1" & ")" & " and Rng" & "_" & "Item" & "(" & " 5 " & ")" & vbTab &  "Rng" & "_" & "Item" & "(" & "3" & "," & " 2" & ")" & " and Rng" & "_" & "Item" & "(" & " 6 " & ")" & vbCr & vbLf

vbTab & vbCr & vbLf
vbTab & vbCr & vbLf
vbTab & vbCr & vbLf 

( Having the final vbCr & vbLf just seems to be a general computer characteristic when any text is displayed. – An empty text document would , using my function, typically return the two “invisible” characters, vbCr & vbLf )

Hopefully that “gives the picture” of what’s going on. Summary, macros, and working example in next post