Open Notepad and Find/Replace (1 Viewer)

RevJeff

Registered User.
Local time
Today, 13:47
Joined
Sep 18, 2002
Messages
125
Hello,

Let me start by saying, I know I don't have to open Notepad to edit a text file, but in this case I need to. So I receive a text file from a main frame. The first character of every line is an unrecognizable character, see below. I need to be able to remove this character. The problem is that if I try to edit the text file using VBA it see this character and thinks it's at EOF and exits. But if I manually open the file in Notepad, I can find/replace this character and it removes it.

So my question, is there a way to open Notepad or UltraEdit and run the find/replace feature using VBA? Thanks in advance.

1648823252233.png
 

Mike Krailo

Well-known member
Local time
Today, 13:47
Joined
Mar 28, 2020
Messages
1,030
Could you post a copy of that file with the special characters in it so we can all take a look at it. I'm interested in finding out exactly what character it is.

If you have some unix/linux utilities, the sed command would work but I think there is an api to do regex's inside VBA as well.
Bash:
cat input.txt | sed 's/^.//g' > result.txt
 
Last edited:

RevJeff

Registered User.
Local time
Today, 13:47
Joined
Sep 18, 2002
Messages
125
Could you post a copy of that file with the special characters in it so we can all take a look at it. I'm interested in finding out exactly what character it is.
 

Attachments

  • Test.txt
    1.6 KB · Views: 203

SHANEMAC51

Active member
Local time
Today, 20:47
Joined
Jan 28, 2022
Messages
310
So my question, is there a way to open Notepad or UltraEdit and run the find/replace feature using VBA? Thanks in advance.
symbol 26(1A)
It is put in place of characters whose values were lost during transmission. In CP/M and MS-DOS, it was used to indicate the end of text files and the end of data entered from the console (although the characters ^C and ^D were intended for this).
 

Mike Krailo

Well-known member
Local time
Today, 13:47
Joined
Mar 28, 2020
Messages
1,030
I made a form that reads in the contents of your file to a string variable and using the simple function I created, puts the corrected text in the forms txtResult box. You must set up the path to your text file in the calling function on the buttons click event for it to work. I had the file directly on the C drive and it worked fine.
 

Attachments

  • StripFirstCharacter.zip
    60.8 KB · Views: 202

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
26,999
To answer the more direct question, NOTEPAD does not expose any COM content or infrastructure to Access, so the answer is NO, you cannot open NOTEPAD from Access. Other utilities do; NOTEPAD does not.
 

isladogs

MVP / VIP
Local time
Today, 17:47
Joined
Jan 14, 2017
Messages
18,186
Yes of course you can open Notepad from Access ... :)
This opens a blank text file in Notepad
Code:
Shell "NOTEPAD.EXE", 1

This opens a named text file:
Code:
Shell "NOTEPAD.EXE c:\foldername\filename.txt", 1

Or you can use file handling code to open a specific text file:

Code:
Option Compare Database
Option Explicit

Dim fso As Object

'Original code courtesy of Dev Ashish
'###############################################
'updated for 64-bit Access by Colin Riddington (Mendip Data Systems) - 06/03/2019
#If VBA7 Then
    Private Declare PtrSafe Function apiShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" _
        (ByVal hWnd As LongPtr, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) _
        As Long
#Else '32-bit Office
    Private Declare Function apiShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" _
        (ByVal hWnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) _
        As Long
#End If
'###############################################

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Function fHandleFile(stFile As String, lShowHow As Long)

On Error GoTo Err_Handler

Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
          
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
  
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
              
Exit_Handler:
    Exit Function
  
Err_Handler:
    MsgBox "Error " & Err.number & " in fHandleFile procedure : " & Err.description, vbOKOnly + vbCritical
    Resume Exit_Handler

End Function

Example usage:
Code:
fHandleFile "c:\foldername\filename.txt", WIN_NORMAL

The above code will open any file in its default file handler
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 17:47
Joined
Jan 14, 2017
Messages
18,186
Why make it so difficult?
Open a text file in MS Word - process it there - and that's it!
That was just an alternative method that works for any file type

But why open in Word when you can open the text file direct in Notepad using Shell as shown above
 

Eugene-LS

Registered User.
Local time
Today, 20:47
Joined
Dec 7, 2018
Messages
481
That was just an alternative method that works for any file type
And let me insert my "two cents" please:
Code:
'----------------------------------------------------------------------------------------------
' Module    : modNotepad_API
' Author    : es
' Date      : 21.04.2019 LE 03.10.2021 v004
' Purpose   : Вствка текста из VBA в notepad (API)
              Insert text from VBA into notepad (API)
'----------------------------------------------------------------------------------------------
' http://www.cyberforum.ru/vba/thread881953.html
' https://www.sql.ru/forum/213772-2/nazhat-knopku-menu-v-drugoy-programme
'----------------------------------------------------------------------------------------------
#If VBA7 Then 'Office x64 = PtrSafe
    Private Declare PtrSafe Function FindWindow Lib "User32" Alias "FindWindowA" _
        (ByVal strClassName As String, ByVal lpWindowName As Any) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "User32" Alias "SendMessageA" _
        (ByVal hWnd As LongPtr, ByVal wMsg As Long, _
        ByVal wParam As Long, ByVal lParam As Long) As LongPtr
    Private Declare PtrSafe Function Sleep Lib "kernel32" _
        (ByVal dwMilliseconds As Long) As LongPtr
#Else
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    'Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
        ( ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
        ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
        (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) As Long

#End If
Public Sub PasteTextToNotepad(vTextToPaste As Variant)
#If VBA7 Then
    Dim hWnd As LongPtr
#Else
    Dim hWnd As Long
#End If
Dim vVal As Variant

    vVal = Shell("notepad.exe", vbNormalFocus) ' открываем блокнот - opening Notepad
    hWnd = FindWindow("Notepad", vbNullString)

    SetClipboard CStr(vTextToPaste)

    Sleep 100

    If hWnd <> 0 Then 'вставка текста из буфера обмена - pasting text from the clipboard
        'Debug.Print hWnd
        vVal = SendMessage(hWnd, &H111, &H302, 0)  'WM_COMMAND = &H111, WM_PASTE = &H302 (Ctrl+V by SendKeys does not work proper)
    End If
End Sub

Private Sub runTEST001()
    PasteTextToNotepad "TEST001 123 END OF TEST"
End Sub
 

KitaYama

Well-known member
Local time
Tomorrow, 02:47
Joined
Jan 6, 2022
Messages
1,489
So many suggested code, but none of them can find/replace text per OP's request :)

I was looking forward to see a solution for this....but .... none up to now.
 

Eugene-LS

Registered User.
Local time
Today, 20:47
Joined
Dec 7, 2018
Messages
481
So many suggested code, but none of them can find/replace text per OP's request
Don't worry - be happy!
Programmers are special, they are lonely among "ordinary" people, so when they get together - love to chat off-topic ...
Give them time to think please ...

I going to see your text file ...
 

KitaYama

Well-known member
Local time
Tomorrow, 02:47
Joined
Jan 6, 2022
Messages
1,489
I don't have any text file. I was interested in suggested code.

If it was me, I would read the text file and use replace function and write it back to the text file.
I was eager to see how professionals do it.
 

Eugene-LS

Registered User.
Local time
Today, 20:47
Joined
Dec 7, 2018
Messages
481
I don't have any text file. I was interested in suggested code.
when I'll see a problem, I will start writing code to solve it
I have already solved something like this ... = Copy - Pasting from internet sites
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
26,999
My original comment was incomplete. You can open anything you want from the SHELL command but your problem is that once that text file is open, Access can do nothing else because NOTEPAD does not appear to have a COM library that exposes the commands. Word does.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
26,999
I agree, Eugene-LS - you did. Credit where credit is due. However, I felt the need to qualify my own previous comments.
 

Eugene-LS

Registered User.
Local time
Today, 20:47
Joined
Dec 7, 2018
Messages
481
I was interested in suggested code.
I wrote the code that writes such a file - will this solution suit you?

I'm not showing the code yet (there's a lot of Russian comments) - I'm waiting for your approval
 

Attachments

  • TestResult.txt
    1.6 KB · Views: 162
Last edited:

Users who are viewing this thread

Top Bottom