Refer the 'GetFolder' to a cell

amitcohen

Board Regular
Joined
Jan 14, 2010
Messages
118
Hi
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
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
 
Have you thought about using a dialogue box - you can then browse to your folder ?

Or you can have :-


Code:
Dim file_path as string

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

Thanks

Kaps
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top