amitcohen
Board Regular
- Joined
- Jan 14, 2010
- Messages
- 118
Hi
I'm using this macro to import txt files into excel.
Id like to replace the folder URL string ("C:\Articles") and refer it to the cell "E5" instead.
(So I don't have to open the code and change the folder every time)
Any ideas as how to do that?
Thanks,
Amit
I'm using this macro to import txt files into excel.
Code:
Sub Bring_Articles_Into_The_File()
Dim sPath As String
Dim iRow As Long
Dim strString As String
Dim fso As FileSystemObject
Dim xFile As File
Dim xFolder As Folder
Sheets("ArticleList").Select
Set fso = CreateObject("Scripting.FileSystemObject")
[B] Set xFolder = fso.GetFolder("[COLOR=Red]C:\Articles[/COLOR]")[/B]
iRow = 1 ' Row to start inserting data
For Each xFile In xFolder.Files
If InStr(1, xFile.Name, ".txt") <> 0 Then
Dim lFile As Long
Dim szLine As String
lFile = FreeFile()
Open xFile.Path For Input As lFile
strString = ""
While Not EOF(lFile)
Line Input #lFile, szLine
' Concatenete lines from text file
strString = strString & szLine & vbCrLf
Wend
' Add to cell
Cells(iRow, 1).Value = strString
iRow = iRow + 1
' Close the file
Close lFile
Application.ScreenUpdating = True
End If
Next ' End of LOOP
Sheets("Bulk Upload Script").Select
MsgBox "Completed!"
'End Sub
(So I don't have to open the code and change the folder every time)
Any ideas as how to do that?
Thanks,
Amit