Extract values from text file into sharepoint excel sheet(.xlsx) through VBA

This code will give some shape to your project. Create a new workbook, paste the code to a standard code module (by default Module1 but I suggest you rename it TeachExcel), save in xlsm format, and run the code.

Sub ReadTXTfile()
    ' 127

    Dim SourceName  As String           ' name of file to be opened
    Dim Source      As Integer          ' ID is used by the computer
    Dim TxtLine     As String           ' one line read from the source
    Dim Txt()       As String           ' copy of Source as array
    Dim n           As Long             ' index to Txt()

    ReDim Txt(1 To 1000)                ' set a number much larger than you need
    SourceName = Application.GetOpenFilename()
    Source = FreeFile()
    Open SourceName For Input As Source
    Do Until EOF(Source)
        Line Input #Source, TxtLine
        n = n + 1
        Txt(n) = TxtLine
    Loop
    Close #Source

    If n Then
        ReDim Preserve Txt(1 To n)

        For n = 1 To UBound(Txt)
            Debug.Print n, Txt(n)
        Next n
    End If
End Sub

The code will ask you to open one of your text files, read its content into an array, close the source file without modifying it in any way, and then print the array. You will see an exact copy of your source file in the Immediate window.

The point is that you now have all of that file in memory to do with as you please. Of course, your intention is to distribute the data to the shared workbook and that thought gives you the structure first mentioned.

1. The code must be in a location other than the destination workbook.
2. It must open the text file and retrieve the data from it.
3. It must then open the destination workbook and write the data to it.

You can build all of this around and into the above procedure. Please don't overload this forum with requests that amount to finish the coding for you. You absolutely need to take ownership yourself and the only way to do that is to build everything with your own hands. So, divide the tasks into bite-sized questions and ask away. Then put the answers to work in and for your project.

One subject that shouldn't be brought up too late is access to the share. To be precise, what happens if another user accesses - and possibly modifies - the target workbook while your code is running? To write the data will take less than a second but if you have no provision for the unlikely occurrence just described it may bring your entire project down when it happens.