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