Code vba to save a range as text in a notepad

[MedExcels]

Hello,

I have to make this task on work every day this is how
Slecet the range A1:K23 (the range is variable in number of rows but not in number of column i.e. A1:K39 or A1:K89)
Paste the selection in notepad
Replace the tabulation with" ; " (like in the image in the workbook)
Save the notepad in the adress or location of the workbook
Is there any code vba to do this??? Thankssssss The file is joined to this Question http://www.cjoint.com/c/GJop5ZE85NB

<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Kenneth Hobson]

Sub Main()
  Dim fn$, r As Range
  
  fn = "C:\Temp\AKrange - " & Format(Date, "yyyymmdd") & ".txt"
  
  Set r = Range("A1:K" & Range("A1").End(xlDown).Row)
  r.Copy
  
  'https://msdn.microsoft.com/en-us/subscriptions/312a5kbt(v=vs.84).aspx
  CreateObject("Scripting.FileSystemObject").CreateTextFile(fn).Write _
    Replace(getClipboard, vbTab, ";")
  
  Application.CutCopyMode = False
  
  Shell "cmd /c " & """" & fn & """", vbNormalFocus
End Sub

'Tools > References... > Microsoft Forms 2.0 Object
Function getClipboard()
    Dim MyData As DataObject
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

 

[MedExcels]

Sub Main()
  Dim fn$, r As Range
  
  fn = "C:\Temp\AKrange - " & Format(Date, "yyyymmdd") & ".txt"
  
  Set r = Range("A1:K" & Range("A1").End(xlDown).Row)
  r.Copy
  
  'https://msdn.microsoft.com/en-us/subscriptions/312a5kbt(v=vs.84).aspx
  CreateObject("Scripting.FileSystemObject").CreateTextFile(fn).Write _
    Replace(getClipboard, vbTab, ";")
  
  Application.CutCopyMode = False
  
  Shell "cmd /c " & """" & fn & """", vbNormalFocus
End Sub

'Tools > References... > Microsoft Forms 2.0 Object
Function getClipboard()
    Dim MyData As DataObject
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

Thanks kenneth
I am still beginner in Macro, I couldn't run this macro, when I use the macro, he gives me an error msg in "Function getClipboard()" and in CreateObject("Scripting.FileSystemObject").CreateTextFile(fn).Write _ Replace(getClipboard, vbTab, ";")

Could you please insert the code in the file and link it for me Thankssssssssssssssssssssss

 

[Kenneth Hobson]

Sure, I could do that very basic task for you. I would prefer that you learn it yourself though. That is what the comment about the function was all about. Give it a try. As you learn, you will run into that issue many times

You might want to read this first if you don't understand how to click the menu items in the Visual Basic Editor (VBE) that I explained for adding the object reference.
https://msdn.microsoft.com/en-us/vb...cles/check-or-add-an-object-library-reference

 

[MedExcels]

Yes Kenneth you are right . I have a lot of work and my schedule is very busy so I don't have much time to concentrate on vba now.

It will be very kind of you to help me there

 

[MedExcels]

Sure, I could do that very basic task for you. I would prefer that you learn it yourself though. That is what the comment about the function was all about. Give it a try. As you learn, you will run into that issue many times

You might want to read this first if you don't understand how to click the menu items in the Visual Basic Editor (VBE) that I explained for adding the object reference.
https://msdn.microsoft.com/en-us/vb...cles/check-or-add-an-object-library-reference

Yes Kenneth you are right . I have a lot of work and my schedule is very busy so I don't have much time to concentrate on vba now.

It will be very kind of you to help me there

 

[Kenneth Hobson]

4 mouse clicks in the VBE does not seem hard to me. Replace the function in post #2 with the one here if you like. Then you won't need to add the reference.

You will see many posts using late bound object methods for new users. It takes effort to explain early binding to new users. I choose to teach when I post most times. You will learn more by early binding as it will allow intellisense to work for that object, if so coded. Many coders will code early bound as it is more helpful and then do a few modifications for the production version using late binding. Of course there are trade-offs for both methods. Here are 2 examples when you have time to learn more about the two binding methods.

1. https://support.microsoft.com/en-us/help/245115/using-early-binding-and-late-binding-in-automation
2. https://peltiertech.com/Excel/EarlyLateBinding.html

Function GetClipboard()
  'Early bound MSForms.DataObject
  'Tools > References > Microsoft Forms 2.0 Object Library
  'Dim MyData As DataObject
  'Set MyData = New DataObject
  
  'Late bound MSForms.DataObject
  Dim MyData As Object
  MyData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  On Error Resume Next
  MyData.GetFromClipboard
  GetClipboard = MyData.GetText
End Function

 

[MedExcels]

When I paste the code and run it he gives me this message of errror (in this link

http://www.cjoint.com/c/GJplbnHJck6 )

How can I fix that?

 

[Kenneth Hobson]

Oops. Late night and 3 family member deaths in 3 weeks is not a good combination...

Add the word Set.

  Set MyData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

 

[MedExcels]

Oops. Late night and 3 family member deaths in 3 weeks is not a good combination...

Add the word Set.

  Set MyData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

Thanks for your effort and lessons

I posted this same question in another forum a I had this code to fix my problem

I post it here if anyone need it

Sub saveTextCustomized()
Dim filename As String, lineText As String
Dim myrng As Range, i, j

filename = ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"

Open filename For Output As #1

Set myrng = Selection

For i = 1 To myrng.Rows.Count
For j = 1 To myrng.Columns.Count
lineText = IIf(j = 1, "", lineText & ";") & myrng.Cells(i, j)
Next j
Print #1 , lineText
Next i

Close #1
End Sub

Thanks for every one helps the others without breaking them their heads