Need help - Copy Excel Data and paste to notepad

[santoshbhagade]

Hi,

I am Completetly new to Excel Macro, need your help to automate a step.
I have Data in Excel file in column A, i want to automate it by using excel macro to follow the below steps

1. copy all the data in column A
2. Open a new notepad file
3. paste the data in notepad file
4. save the notepad file in specific path and name the file as cell value A1 in the excel file
5. Close the notepad file.

Thanks awaiting your assistance on the same.

Thanks.

 

[Chihiro]

Something like below. Replace "ThisWorkbook.Path" with specific path you want the txt file to be saved.

Sub txtSave()
Dim sWs As Worksheet
Dim tWb As Workbook
Dim fName As String
Dim lRow As Long

Set sWs = ThisWorkbook.Sheets("Sheet1")

'set target workbook as new workbook
Set tWb = Workbooks.Add

'get last used row for Col A
lRow = sWs.Range("A" & Rows.Count).End(xlUp).Row

'copy used range of Col A
sWs.Range("A1:A" & lRow).Copy

'paste Value & Number format only to target workbook sheet 1, A1~
tWb.Worksheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = Fasle

'set path and file name string
fName = ThisWorkbook.Path & "\" & sWs.Cells(1, 1).Text

'save as text file and close the target workbook
tWb.SaveAs fName, xlText
tWb.Close SaveChanges:=True

End Sub

Edit: Attached, sample workbook. Hit Control + F8 and run txtSave macro.

 

[santoshbhagade]

Hi Chihiro,

Thank you by explaining me step by step, it is really helpful.

however I have a data in the excel sheet which is space in it and i want to keep that space in the notepad file. however if i do it in your way then space are converted in " this format. But when i do it in copy from excel and paste it into notepad then its working fine.

Could you please assist me with the same.

Thanks.
Santosh

 

[shrivallabha]

See if following work for you.

Public Sub CopyToNotepad()
Dim strPath As String: strPath = "C:\Temp\" & Range("A1").Value & ".txt"
Dim intFF As Integer: intFF = FreeFile()
Open strPath For Output As #intFF
Print #intFF, Join(Application.Transpose(Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value), vbCrLf)
Close #intFF
End Sub

 

[santoshbhagade]

thanks Shrivallabha, that works perfectly fine.

 

[Chihiro]

@shrivallabha

Awsome. Never used Freefile() before and learned new concept, processor command (#). Now to go and read up on it.

 

[shrivallabha]

@shrivallabha

Awsome. Never used Freefile() before and learned new concept, processor command (#). Now to go and read up on it.

Hi Chihiro,
Thank you When we open/read/write files they have file-handle assigned to it. It is possible to use file-handle explicitly like.

Open strPath For Output As #1

But if this file-handle is already being used by some other process it will give error. Freefile() finds the free-handle integer dynamically and assigns to the variable.

 

[Kavish Sekhri]

@Chihiro @shrivallabha

Thank you for this VBA code.

I'm working on a similar utility like this, where I'm trying to save the notepad file in UTF-8 encoding format. How can I mention the encoding format of the notepad file in the variables and parameters of this code?

Thanks

 

[shrivallabha]

@Chihiro @shrivallabha

Thank you for this VBA code.

I'm working on a similar utility like this, where I'm trying to save the notepad file in UTF-8 encoding format. How can I mention the encoding format of the notepad file in the variables and parameters of this code?

Thanks

Hi Kavish

VBA open file method does not have any option for setting encoding so it will (probably) pick the default encoding on the system it is being run. If you want to enforce it then you will have to use different technique. See if below thread helps you with it:

https://stackoverflow.com/questions/2524703/save-text-file-utf-8-encoded-with-vba

 

[Kavish Sekhri]

@shrivallabha Thanks!

 

[Marc L]

Hi !​

​

Doable via ADODB.Stream (must see on Ms Docs) ...​

​