Results 1 to 4 of 4

Thread: Insert contents of .txt file into cell

  1. #1

    Insert contents of .txt file into cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there, I am trying to use a macro where I prompt the user to select a .txt file from a given file path and after he/she selected a .txt file insert the contents of said file to a cell (A7)

    I got the following code:

    Sub test()
    Dim FName As String
    MsgBox "Choose Pointsec Report to import"
    ChDir "C:\Users\Public\Documents"
    FName = Application.GetOpenFilename
    If FName <> False Then
    End If
    End Sub

    What I got to so far is having the folder for the file path pop up and allow the user to select a .txt file in folder, however after a file is selected I don't know how to "get" excel to improve it's contents into a defined cell on Sheet1 (Destination:=Range("$A$7")

    Any help would be greatly appreciated!
    Last edited by Rhyfelwr; 2017-09-13 at 03:11 PM.

  2. #2
    Acolyte retired007geek's Avatar
    Join Date
    Jun 2017
    Location
    South Carolina, USA
    Posts
    86
    Articles
    0
    Rhyfelwr,

    This code should do the trick:
    Code:
    Option Explicit
    
    '*** Code to test the GetFileToOpen Function ***
    
    Sub Main()
    
       Dim zFileItems()   As String
       Dim zSelectedFiles As String
       Dim zAllowedExts   As String
       Dim lNoFiles       As Long
       Dim lCnt           As Long
       Dim zFileText      As String
       Dim zFileLine      As String
    
       ReDim zFileItems(1)
       zAllowedExts = "*.txt"
       lNoFiles = GetFileToOpen(zFileItems, zAllowedExts, False, "*.txt*")
       
       If lNoFiles > 0 Then
         Open zFileItems(1) For Input As #1
         Input #1, zFileText
         
         Do While Not EOF(1)
           Input #1, zFileLine
           zFileText = zFileText + " " + zFileLine
         Loop
           
         Close #1
         [A7] = zFileText
         
       Else
         MsgBox "User pressed Cancel or X (Close Box).", vbOKOnly, _
                "No Files Selected:"
       End If
       
    End Sub 'Main()
    
    '                        +--------------------+                 +----------+
    '------------------------|   GetFileToOpen()  |-----------------| 01/15/14 |
    '                        +--------------------+                 +----------+
    'Called by  :
    'Arguments  : zSelected - a String array declared empty & ReDimed to 1
    '             zExts     - a list of allowed extensions for the filter
    '                         Ex: "*.xlsx, *.xls, *.xlsm, *.xlsb"
    '                         Note: Only Excel filetypes as function is written!
    '             zMulti    - True allows multi select, False allows single select.
    '             zFileFilter - Optional - used to limit the files shown by name
    '                         pattern, EX: "CA*.xls*" if ommited "*.xls*" will
    '                         be used. Note: using "*.*" will over ride the zExts
    '                         filter! You can also specify a drive/path to set
    '                         the initial folder displayed.
    'Notes      : You can uncomment the .Title line and supply your own
    '             dialog box title and add an argument if you want to pass it!
    '             You can uncomment the .ButtonName to supply a custom OK button
    '             caption which can also be passed by argument if desired.
    
    Function GetFileToOpen(ByRef zSelected, zExts As String, bMulti As Boolean, _
                           Optional zFileFilter As Variant) As Long
    
        Dim fd             As FileDialog
        Dim lCnt           As Long
        
        If IsMissing(zFileFilter) Then zFileFilter = "*.xls*"
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        With fd
            .Filters.Clear   '*** Clear old filters just precautionary ***
            .Filters.Add "Spreadsheets", zExts, 1
            .InitialFileName = zFileFilter  '*** File Name Filter control. ***
    '        .Title = "You're Dialog Box Title Here"
    '        .ButtonName = "OK button caption"
            .AllowMultiSelect = bMulti 'Note: if not specified defaults to True!
            
            '.Show  Returns: -1 if Open button or 0 if Cancel button is pushed!
            If .Show = -1 Then
            
              ReDim zSelected(.SelectedItems.Count) 'Make array the proper size.
              
              For lCnt = 1 To .SelectedItems.Count 'Load the array with selections.
                 zSelected(lCnt) = .SelectedItems.Item(lCnt)
              Next lCnt
            
            End If
            
            GetFileToOpen = .SelectedItems.Count
            
        End With   'fd
        
    End Function 'GetFileToOpen
    Of course you may have to adjust how the lines from the file are put together to meet your needs, I just placed a space between them.

    Test File: ReadTextFile.zip

    HTH

  3. #3
    Code:
    Sub M_snb()
       with application.filedialog(1)
           .initialfilename="C:\Users\Public\Documents\*.txt"
           if .show then cells(7,1)=createobject("scripting.filesystemobject").opentextfile(.selecteditems(1)).readall
       end with
    End Sub

  4. #4
    Wows thank you. The first one looks very intimidating haha at first glance I only understand a few parts of it so the comments are super helpful. I (foolishly) thought that it'd be a simple task. I couldn't test it yet but I'll try it out. Thank you for providing the codes guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •