Refer the 'GetFolder' to a cell

[amitcohen]

Hi
I'm using this macro to import txt files into excel.

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

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

 

[Kaps_mr2]

Have you thought about using a dialogue box - you can then browse to your folder ?

Or you can have :-

Dim file_path as string

file_path = thisworkbook.sheets("Sheet1").range("E5").value

Thanks

Kaps

 

[VoG]

Try

Set xFolder = fso.GetFolder(Range("E5").Value)

 

[amitcohen]

Thanks so much guys.
Problem solved!!