[Excel & VBA] VBA parses the local file directory to read text data

Sometimes Excel VBA is used to deal with some things. After all, I am a lazy person, and I always like to try to hand over repetitive work to the computer for processing, so that I can prove that my brain is still running, summarize some methods for reading and parsing local file directories in VBA.

 

1' this function is used to obtain the names of all folders in the specified directory. 2 sub readfile () 3 Path = "D: \" 4 sonpath = Dir (path, vbdirectory) 5 do while sonpath <> "" 6 "skips the current directory and the upper directory 7 if sonpath <> ". "And S <> ".. "Then 8' uses bitwise comparison to determine that myname represents a directory 9 if (getattr (Path & sonpath) and vbdirectory) = vbdirectory then10 msgbox sonpath' if it is a directory, print the name 11' and then open the file 12 end if13 end if14 sonpath = dir' under these directories to find the next directory 15 loop16 end sub

View code

 

1' this function is used to obtain all the file names in the specified directory. 2' comments are not included if the comments are added ". "and ".. "3 'otherwise, the obtained file name includes the two 4 sub readfile1 () 5 Path =" D: \ video \ "6 sonpath = Dir (path, vbdirectory) listed above) 7 do while sonpath <> "" 8' if sonpath <> ". "And sonpath <> ".. "Then 9 msgbox sonpath10'end if11 sonpath = dir12 loop13 end sub

View code

Note:

Path refers to the path, Where "d: \ video \" and "D: \ video" are different. The former can be resolved to the sub-directory of video, the latter only parses the directory of the video layer;

 

1' read the file content, and copy each line to column A in sequence. 2'path indicates the absolute address of the file, and linenum indicates the row number. 3 function readfile2 (path, linenum) 4': handle the error, can be removed. If there is an error, it will automatically jump to the error tag and execute 5 on error goto Error 6 7' to open the file, and use the symbol "1" instead of the file stream 8 open path for input as #1 9 10' to determine whether the file stream "1" is read to the end of the file 11 do while not EOF (1) 12 13 'read a row in the file stream "1" and assign it to the variable "A" 14 line Input #1, A $ 15' copy the variable "a" to 16 range ("A" + CSTR (linenum) in the specified cell )). value = a $17 'row number + 1 18 linenum = linenum + 1 19 loop 20 close #1 21 exit function 22 error: msgbox err. description 23 close #1 24 end Function

View code

1' read all the TXT files in the specified directory, and copy the contents to column A of sheet1, column 2 sub readtxts () 3 'parent path settings, and finally "\", note that 4 Path = "D: \ data \" 5 'sets line number 6 linenum = 1 7 8' to retrieve all file names in this directory (only TXT files are placed, other formats have not been determined) 9 sonpath = Dir (path, vbdirectory) 10 do while sonpath <> "" 11 if sonpath <> ". "And sonpath <> ".. "Then 12 msgbox" reading "+ sonpath 13 14 'Call the function for reading the file (absolute path of the TXT file, row number) 15 result = readfile2 (path + sonpath, linenum) 16 end if 17 sonpath = dir 18 loop 19 end sub

View code

The above two functions are used to read local text files and read the data in the text to an Excel table.

[Excel & VBA] VBA parses the local file directory to read text data