Excel VBA How to Copy excel columns into NotePad

J

jceast7

New Member
Joined
Sep 7, 2019
Messages
5
I have 3 column in and excel vba file that I want to copy into NotPad, which I can do but since the text content in the cells of each column vary in length, NotPad does not maintain the format as copied, but the copy results are altered and not in line as desired. Is there a way to ix this??

Thanks

jceast7
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Re: Excel VBA How to Coply excel columns into NotePad

Try this

Code: 
Sub Copy_into_NotePad()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:C" & lr).Copy
  Shell "notepad.exe", vbNormalFocus
  SendKeys "^V"
End Sub
 
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

DanteAmor said:
Try this

Code: 
Sub Copy_into_NotePad()
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Range("A1:C" & lr).Copy
  Shell "notepad.exe", vbNormalFocus
  SendKeys "^V"
End Sub
Click to expand...

Thank you for your response. This is your code modified for my sheet and ranges

[/Sub Copy_into_NotePad()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).row
Sheets("Copied Summary").Range("A1:C" & lr).Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"]
End Sub​


I run the code and most of the 3 columns are copied - but not to the end of the data in the columns. I does open NotePad, but it is blank?? No Paste of data.
It copies to row 60 and the data ends at row 153 for this case. But each case varies. Note sure why it does not work. Any suggestions would be greatly appreciated.

A sample of the Data is below:

[TABLE="width: 255"]
<tbody>[TR]
[TD]Customer:[/TD]
[TD][/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]Botavia Ref:[/TD]
[TD][/TD]
[TD]123456[/TD]
[/TR]
[TR]
[TD]Customer Ref:[/TD]
[TD][/TD]
[TD]PO123456[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Railcar:[/TD]
[TD][/TD]
[TD]123458[/TD]
[/TR]
[TR]
[TD]O.G. , inn:[/TD]
[TD][/TD]
[TD]19.00[/TD]
[/TR]
[TR]
[TD]B.G. , inn:[/TD]
[TD][/TD]
[TD]113.50[/TD]
[/TR]
[TR]
[TD]TOV, GAL:[/TD]
[TD][/TD]
[TD]28,297.[/TD]
[/TR]
[TR]
[TD]TOV, BBL:[/TD]
[TD][/TD]
[TD]674.[/TD]
[/TR]
[TR]
[TD]H2O Allow, %:[/TD]
[TD][/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]H2O Vol. , %:[/TD]
[TD][/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]Ded, Vol:[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]GOV, BBL:[/TD]
[TD][/TD]
[TD]28,297.[/TD]
[/TR]
[TR]
[TD]Obsr. Temp, Deg-F:[/TD]
[TD][/TD]
[TD]75.00[/TD]
[/TR]
[TR]
[TD]API @ 60-F:[/TD]
[TD][/TD]
[TD]30.00[/TD]
[/TR]
[TR]
[TD]VCF - Table 6:[/TD]
[TD]B[/TD]
[TD]0.99332[/TD]
[/TR]
[TR]
[TD]GSV, GAL:[/TD]
[TD][/TD]
[TD]28,108.[/TD]
[/TR]
[TR]
[TD]GSV, BBL:[/TD]
[TD][/TD]
[TD]669.24[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

Try this please:

Code: 
Sub Copy_into_NotePad()
  Dim lr As Long
  lr = [COLOR=#333333][FONT=Verdana]Sheets("Copied Summary").[/FONT][/COLOR]Range("A" & Rows.Count).End(xlUp).Row
  [COLOR=#333333][FONT=Verdana]Sheets("Copied Summary").[/FONT][/COLOR]Range("A1:C" & lr).Copy
  Shell "notepad.exe", vbNormalFocus
  SendKeys "^V"
End Sub

Note: Run with F5 or put a button on the sheet to run the macro, but don't run the macro with F8 (debug)
 
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

I woudn't use SendKeys as it doesn't work consistently and depends on focus plus it turns off the keyboard Numeric pad.

A more reilable way is by using the Sendmessage API :
Code: 
Option Explicit
 
 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName 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 GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
 [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private Const WM_PASTE = &H302


Sub Copy_into_NotePad()

  Dim lr As Long
  
  lr = Sheets("Copied Summary").Range("A" & Rows.Count).End(xlUp).Row
  Sheets("Copied Summary").Range("A1:C" & lr).Copy
  Shell "notepad.exe", vbMinimizedNoFocus 'vbNormalFocus
  
  Call SendMessage(GetNextWindow(FindWindow("Notepad", vbNullString), 5), WM_PASTE, True, 0)
  
End Sub
 
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

Here is a much bulletproof method that takes into account the possible scenario where there are more than on Notepad window opened .

Code: 
Option Explicit 

 [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
    Private Declare PtrSafe Function GetParent Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetShellWindow Lib "user32" () As LongPtr

 [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    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 GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
    Private Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function GetShellWindow Lib "user32" () As Long
 [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const WM_PASTE = &H302
Private Const GW_HWNDPREV = 3



Sub Copy_into_NotePad()

    Dim lr As Long, PID As Long
    
    lr = Sheets("Copied Summary").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Copied Summary").Range("A1:D" & lr).Copy
    PID = Shell("notepad.exe", vbMinimizedNoFocus)  'vbNormalFocus)
    
    Call SendMessage(GetNextWindow(HwndFromPID(PID), 5), WM_PASTE, True, 0)
    
End Sub



[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Function HwndFromPID(ByVal PID As Long) As LongPtr
        Dim lTempHwnd As LongPtr
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Function HwndFromPID(ByVal PID As Long) As Long
        Dim lTempHwnd As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

        Dim lTempPid As Long
        
        lTempHwnd = GetShellWindow
        Do While lTempHwnd
            If GetParent(lTempHwnd) = 0 Then
                Call GetWindowThreadProcessId(lTempHwnd, lTempPid)
                If lTempPid = PID Then
                    HwndFromPID = lTempHwnd
                    Exit Do
                End If
            End If
            lTempHwnd = GetNextWindow(lTempHwnd, GW_HWNDPREV)
            DoEvents
        Loop
    
End Function
 
Last edited:
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

If you only need the 3 columns in a txt file:

Code: 
Sub Macro2()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Columns("A:C").Copy
  Workbooks.Add
  Range("A1").PasteSpecial Paste:=xlPasteValues
  ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "book1.txt", FileFormat:=xlText
  ActiveWorkbook.Close False
End Sub
 
Upvote 0
Re: Excel VBA How to Coply excel columns into NotePad

Hello - I would like to post my code that I generated from your input but I do not know how. Can you please tell me hoe to do this?
I did get the first code you provided to work well, however it does disable the NUMLOCK key. Is the a way arounf this???

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
158
Candyman8019
C
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
856
Micron
M
C
  • Solved
Entering formula in A1 mode works but in R1C1 mode fails
Replies
5
Views
347
Alex Blakenburg
A
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
272
Peter_SSs
Peter_SSs
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
328
Dan_W
D

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top