So, if I understand Post #1 correctly, you have a list of filenames in a particular column of a spreadsheet, and you want to take that filename, open it, and transfer the contents of that file, and paste it into the column adjacent to the filename?
I do believe you are much closer than you realize.
Now, one really easy way to do this would be to link the spreadsheet to your databse, to treat it as a table. However, if there are mutiple spreasheets, this would defeat the purpose of automating here. Also, based on your code provided, it appears that your Spreadsheet has already been opened and you are able to write to specific cells already? Again, I think you are very close.
If you are able to access the filenames, you simply need to keep track of which cell the name came from and then insert the text into the next column over. You must be sure that you take into account the full path and filename, so if the cell contains the filename "A0001.txt", at some point you must know that the filename you will use for your purposes will have to become: "C:\Text\A0001.txt".
There are some differences in your two listed sets of code, so let me try this:
-
Sub filetext(FileName as String, CellRange as String)
-
Dim fso As New FileSystemObject
-
Dim ts As TextStream
-
Set ts = fso.OpenTextFile("C:\Text\" & FileName)
-
ActiveSheet.Range(CellRange).Value = ts.ReadAll
-
ts.Close
-
End Sub
The key here will be establishing the correct CellRange. If your Filenames are in column A and you want to write into column B, then CellRange will be "B1", "B2"... etc., based on the source of the FileName.
You may need to play with the code and validate that you are pulling the proper filenames, but I think you are almost there!