• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste filtered cells into notepad

AntoPath

AntoPath

New Member
Hi everybody !
I'm trying to make my old 2003 Excel at my hospital, to copy-paste the content of CA49-CA80 non empty cells into notepad in order to get the text without all the formattings and then copy-paste it again into my LIS.
Everything works fine ... the data are properly selected and copied into the clipboard, notepad is opened and taken to the front but the clipboard content isn't pasted into it and I have to force Excel by hitting ctrl-v.
The script is:
Application.ScreenUpdating = False
Range("CA49:CA80").Select
Selection.AutoFilter
ActiveSheet.Range("$CA$49:$CA$80").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
End Sub

Please be patient, I'm a novice to VBA and all these things sound magic to me !.
Antonio
 
Hi Anto

I have been playing around with your code. You hare applying the autofilter then applying it again. Also no need to select anything with vb. Just go straight in there and tell it what to do.

Give this a try.

Code: 
Sub aaa()
[Ca49:ca80].AutoFilter 1, "<>"
[Ca49:ca80].Copy
Shell "notepad.exe", 3
SendKeys "^V"
End Sub


Take care

Smallman
 
For an unknown reason the script doesn't paste the copied cells to notepad, so I have to hit ctrl-v to complete the operation. Guess why ?
o_O
 
Hi, AntoPath!
How are you running the macro? If from the VB editor in debug mode, step by step with F8, it won't work since after executing the Shell instruction Excel gets the focus again and in fact your copying the filtered range over itself. If you run it with F5 or calling the macro from the immediate window pane or from Excel at the Programmer tab, Code group, Macro icon, then it works fine.
Regards!
 
Hi SirJB7
the macro works fine at home (Excel 2013) but still stucks on my PC at the Hospital where I have an older version (xls 2003). I suspect I have inadvertitely changed some stupid setting of the program which halts the execution of the ctrl-v command. Could it be possible ?
Thanks a lot for the suggestions
 
Yup ! The issue has been solved adding a Wait line before the paste cmd.
So instead of
Selection.Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
 
Thanks for sharing your solution AntoPath. It will help others in years to come who search for your problem.

Take care

Smallman
 
The wait line has solved the problem, but one has to keep in mind that the lag must be tailored according to the complexity of the macro and the computational power of the computer.
The older is the computer (as mine at the hospital, for example) and complex the macro, and longer must be the lag between the completion of the previous commands and the beginng of the next.
All the best !
 
Hi, AntoPath!
Thanks for sharing the tip and quoting the source. Have you tried replacing the wait line by a DoEvents?
Maybe it works.
Regards!
 
Sendkeys can prove unreliable. You can try following approach:
Code: 
Public Sub CopyDataToNotepad()
'Define a suitable path where this file will be written
Const csFlNm As String = "C:\Temp\Messagefile.txt"
Dim strContent As String
strContent = Join(Filter(Application.Transpose([IF(CA49:CA80<>"",CA49:CA80,"~")]), "~", False), vbCrLf)
Open csFlNm For Output As #1
Print #1, strContent
Close #1
Shell "Notepad.exe " & csFlNm, vbNormalFocus
End Sub
 
Hi, AntoPath!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 
Sendkeys sends the commands to active window whichever it might be.

The route I suggested creates a text file with the same content at the specified location and then we use Shell to open it.

I have put comments in the code which should explain what it does.
Code: 
Public Sub CopyDataToNotepad()
'Define a suitable path where this file will be written
Const csFlNm As String = "C:\Temp\Messagefile.txt"
Dim strContent As String
'We use Evaluation followed by array filter and then Join Array function to create single string
strContent = Join(Filter(Application.Transpose([IF(CA49:CA80<>"",CA49:CA80,"~")]), "~", False), vbCrLf)
'Open text file for writing
Open csFlNm For Output As #1
'Write the content written in one go
Print #1, strContent
'Close the text file
Close #1
'Invoke Shell to open the file we have created
Shell "Notepad.exe " & csFlNm, vbNormalFocus
End Sub
 
Hi Shrivallabha, I need to ask you unvaluable help again. So please excuse me but I can't understand, surely due for my deep ignorance, why this code (the one you suggested me earlier) works fine on all the PCs of my lab except one where it stucks and gives a runtime error 75 .....
 

Attachments

  • error.jpg
    error.jpg
    91.5 KB · Views: 5
You must log in or register to reply here.
Back
Top