Results 1 to 12 of 12

Thread: Placing Text From A .txt File At The Cursor Position

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Placing Text From A .txt File At The Cursor Position

    Using VB.net to create a custom Ribbon in Excel. I am trying to get the text content from a .txt file, and place it in a an Excel cell, without losing any existing content of that cell. So, I think I want to place the text from the .txt file, at the cursor position...I'm not sure how to do this.

    Here is the code I have at the moment. While it places the text into the active cell, it unfortunately results in overwriting any existing content of that cell, instead of simply adding the .txt content to what is already in the cell.
    Code:
            Dim NotesFilePath As String
            NotesFilePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
    
            Dim NoteRng As Range
            NoteRng = Globals.ThisAddIn.Application.ActiveCell
    
            'Read the .txt file
            Dim fileReader As String
            fileReader = My.Computer.FileSystem.ReadAllText(NotesFilePath & "\NotesFolder\" & btn.Label & ".txt")
    
            'Place the text content of the .txt file in the active cell
            NoteRng.Value = fileReader
    So, how do I get the text from the .txt file, into an Excel cell, and make sure it is placed at the cursor position?

  2. #2
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Placing Text From A .txt File At The Cursor Position

    Code:
    Dim NotesFilePath As String
            NotesFilePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)
    
            Dim NoteRng As Range
            NoteRng = Globals.ThisAddIn.Application.ActiveCell
    
            'Read the .txt file
            Dim fileReader As String
            fileReader = My.Computer.FileSystem.ReadAllText(NotesFilePath & "\NotesFolder\" & btn.Label & ".txt")
    
            'Place the text content of the .txt file in the active cell
            NoteRng.Value = NoteRng.Value & fileReader

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Placing Text From A .txt File At The Cursor Position

    Thank you for that suggestion...

    That code modification is placing the text in the active cell, and it will place the text immediately after any content of the cell, which is certainly an improvement over what I had. But, it will only do this when the cell is selected...not when the cell is double clicked, and the cursor is active. I'm trying to get the text to be placed wherever the cursor is flashing, not necessarily after the entire contents of the cell...I realize I didn't make that entirely clear in my original post...Sorry!
    Last edited by fdegree; Apr 2nd, 2014 at 07:45 PM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Placing Text From A .txt File At The Cursor Position

    i do not believe you can do as requested, excel can not be automated whilst in edit mode
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Placing Text From A .txt File At The Cursor Position

    Thank you for that information. That's unfortunate, I was hoping to make this feature more customizable for the user.

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Placing Text From A .txt File At The Cursor Position

    You could simulate it maybe. Like in the "before double click" event, read the cell contents, then display it in an "input box." The thing I still don't think you could do, though, is to read exactly where, within the cell contents, they clicked. Maybe that renders it pointless for you?

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Placing Text From A .txt File At The Cursor Position

    Thanks for that suggestion. Maybe I'll play around with this idea.

  8. #8
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Placing Text From A .txt File At The Cursor Position

    I did a bit of playing using the SendMessage API. I was able to paste a string to the edit bar, but it did not want to respond to WM_CHAR messages. I really dislike using the clipboard as a transport mechanism, but if you're interested I can post the code.

  9. #9
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Placing Text From A .txt File At The Cursor Position

    Tin,

    Are you able to pinpoint where the cursor is (was) when double clicked?

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Placing Text From A .txt File At The Cursor Position

    Yes, I would be curious to see the code...THANKS!

  11. #11
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Placing Text From A .txt File At The Cursor Position

    Bryce, no joy on EM_GETSEL.

    fdegree, here is that code:
    VB.Net Code:
    1. Private Shared Function FindWindowEx(ByVal parentHandle As IntPtr, _
    2.                          ByVal childAfter As IntPtr, _
    3.                          ByVal lclassName As String, _
    4.                          ByVal windowTitle As String) As IntPtr
    5.    End Function
    6.  
    7.    <DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    8.    Private Shared Function SendMessage(ByVal hWnd As IntPtr, ByVal Msg As Int32, ByVal wParam As IntPtr, ByVal lParam As IntPtr) As Int32
    9.    End Function
    10.  
    11.    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    12.       Const WM_PASTE As Int32 = &H302
    13.       Dim app As New Excel.Application
    14.  
    15.       app.Visible = True
    16.       Dim books As Excel.Workbooks = app.Workbooks
    17.       Dim wb As Excel.Workbook = books.Add()
    18.  
    19.       Dim hndEdit As IntPtr = FindWindowEx(New IntPtr(app.Hwnd), Nothing, "EXCEL<", String.Empty)
    20.  
    21.       Dim pasteThis As String = "something to paste"
    22.       Clipboard.SetText(pasteThis)
    23.       SendMessage(hndEdit, WM_PASTE, IntPtr.Zero, IntPtr.Zero)
    24.       Clipboard.Clear()
    25.       Stop ' goto Excel and verify paste
    26.       wb.Close(False)
    27.       ReleaseCOM(wb)
    28.       ReleaseCOM(books)
    29.       app.Quit()
    30.       ReleaseCOM(app, True)
    31.    End Sub
    32.  
    33.    Private Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
    34.       Try
    35.          System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
    36.       Finally
    37.          COMObj = Nothing
    38.          If GCCollect Then
    39.             GC.WaitForPendingFinalizers()
    40.             GC.Collect()
    41.          End If
    42.       End Try
    43.    End Sub
    Be advised that this will paste into the edit window, but you still need to have the cursor there and press Enter to commit it. Appending a VBCRLF to the string will not work.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Placing Text From A .txt File At The Cursor Position

    Thanks for sharing...I sincerely appreciate it.

Posting Permissions

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



Click Here to Expand Forum to Full Width