-
Apr 2nd, 2014, 04:33 PM
#1
Thread Starter
Lively Member
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?
-
Apr 2nd, 2014, 04:54 PM
#2
Addicted Member
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
-
Apr 2nd, 2014, 06:05 PM
#3
Thread Starter
Lively Member
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.
-
Apr 3rd, 2014, 06:45 AM
#4
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
-
Apr 3rd, 2014, 07:27 AM
#5
Thread Starter
Lively Member
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.
-
Apr 3rd, 2014, 07:39 AM
#6
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?
-
Apr 3rd, 2014, 08:41 AM
#7
Thread Starter
Lively Member
Re: Placing Text From A .txt File At The Cursor Position
Thanks for that suggestion. Maybe I'll play around with this idea.
-
Apr 3rd, 2014, 10:07 AM
#8
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.
-
Apr 3rd, 2014, 10:13 AM
#9
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?
-
Apr 3rd, 2014, 10:42 AM
#10
Thread Starter
Lively Member
Re: Placing Text From A .txt File At The Cursor Position
Yes, I would be curious to see the code...THANKS!
-
Apr 3rd, 2014, 11:19 AM
#11
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:
Private Shared Function FindWindowEx(ByVal parentHandle As IntPtr, _
ByVal childAfter As IntPtr, _
ByVal lclassName As String, _
ByVal windowTitle As String) As IntPtr
End Function
<DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
Private Shared Function SendMessage(ByVal hWnd As IntPtr, ByVal Msg As Int32, ByVal wParam As IntPtr, ByVal lParam As IntPtr) As Int32
End Function
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Const WM_PASTE As Int32 = &H302
Dim app As New Excel.Application
app.Visible = True
Dim books As Excel.Workbooks = app.Workbooks
Dim wb As Excel.Workbook = books.Add()
Dim hndEdit As IntPtr = FindWindowEx(New IntPtr(app.Hwnd), Nothing, "EXCEL<", String.Empty)
Dim pasteThis As String = "something to paste"
Clipboard.SetText(pasteThis)
SendMessage(hndEdit, WM_PASTE, IntPtr.Zero, IntPtr.Zero)
Clipboard.Clear()
Stop ' goto Excel and verify paste
wb.Close(False)
ReleaseCOM(wb)
ReleaseCOM(books)
app.Quit()
ReleaseCOM(app, True)
End Sub
Private Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
Finally
COMObj = Nothing
If GCCollect Then
GC.WaitForPendingFinalizers()
GC.Collect()
End If
End Try
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.
-
Apr 3rd, 2014, 12:03 PM
#12
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|