Code vba to save a range as text in a notepad

[MedExcels]

Hello,

[TABLE="width: 744"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 6"]I have to make this task on work every day this is how[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 8"]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)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Paste the selection in notepad[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Replace the tabulation with" ; " (like in the image in the workbook)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 5"]Save the notepad in the adress or location of the workbook [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Is there any code vba to do this??? Thankssssss

The file is joined to this Question http://www.cjoint.com/c/GJop5ZE85NB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Thanks for your effort and lessons

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

First off, if you post to more than one forum, you should then post in each forum the fact that you posted to more than one forum AND include a link to those other forums. You should do this to be kind to the volunteers you asked to help you... those links allow a volunteer to be able to see if someone has already posted the method they were thinking of developing (if they see someone has, then they won't end up wasting their time re-developing the same solution that has already been posted).

Now, as to your question, I believe this macro will be faster than the code you posted (noticeably so, I would think, if you had a huge number of rows to process). There is no need to select any cells with my method... it will determine where the last data row in Columns A:K is and use it automatically.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub saveTextCustomized()
  Dim R As Long, FF As Long, Filename As String, Result As String, Data As Variant

  Filename = ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
  
  Data = Range("A1:K" & Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row)
  For R = 1 To UBound(Data)
    Result = Result & vbCrLf & Join(Application.Index(Data, R, 0), ";")
  Next
  
  FF = FreeFile
  Open Filename For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
  Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , Mid(Result, 3)
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[MedExcels]

Thank you rick

Yes it is faster than my code and i like to code it by slection not by range A:K because i want to convert each time a specific range.

Thanks

 

[Kenneth Hobson]

Cross-posters are generally referred to this: https://www.excelguru.ca/content.php?184

Be careful when evaluating speed tests. A small dataset may seem fast but when talking less than a second, times don't matter much to humans. The three methods discussed here for the short dataset, were all less than one second. For a dataset columns A-F with 5,000 rows, my method and yours were less than one second. Rick's method was just over two minutes. String concatenation is a big timer killer. I am surprised that Rick used it.

I tried to make the routines act the same for some parts. If you find an error, please let me know. If you want to look at how I ran the tests and a table of times, see: https://www.dropbox.com/s/o2wvaiwf9...le and Translate vbTab to Semicolon.xlsm?dl=0

My routine might be a bit faster if I used a Regular Expression replacement method rather than VBA.Replace. Still, I follow the one second rule before I worry about run-time.

As usual, there are a few other methods one can use.