EXCEL macro : How to get all the file name from a folder, list the file name into a file then import the file list inside current workbook

This below code is a modification from my previous post.
In the previous post I output it into a .txt file and open the file for viewing.
This post will show how to code it to import the file from
txt file to current workbook sheet. In the example I will import it to "data" sheet.

 ====================================================
 Sub getfilename()  
 Dim fso, folder, files, OutputFile  
 Dim strPath  
 Dim wbI As Workbook, wbO As Workbook  
 Dim wsI As Worksheet  
   Myuser = Environ("userprofile")  
   Myuserdesktop = Myuser & "\Desktop"  
 ' Create a FileSystemObject  
   Set fso = CreateObject("Scripting.FileSystemObject")  
 ' Define folder we want to list files from  
   strPath = "C:\Users\Public\Pictures\Sample Pictures"  
   Set folder = fso.GetFolder(strPath)  
   Set files = folder.files  
 ' Create text file to output test data  
   Set OutputFile = fso.CreateTextFile(Myuserdesktop & "\ScriptOutput.txt", True)  
 ' Loop through each file  
   For Each Item In files  
  ' Output file properties to a text file  
  OutputFile.WriteLine (Item.Name)  
   Next  
 ' Close text file  
   OutputFile.Close  
 ' import to data sheet  
   Set wbI = ThisWorkbook  
   Set wsI = wbI.Sheets("data") '<~~ Sheet where you want to import  
   Set wbO = Workbooks.Open(Myuserdesktop & "\ScriptOutput.txt")  
   wbO.Sheets(1).Cells.Copy wsI.Cells  
   wbO.Close SaveChanges:=False  
   Sheets("data").Select  
 End Sub  
 ====================================================

No comments:

Post a Comment