Export to notepad?

[Dave Punky]

Hi all,

This is going to sound like a strange request, but I want a macro to basically copy some text into a blank notepad document.

So for example, if I wanted to copy cells A1 to A12 it would literally just copy the text and paste it into a new notepad document.

The reason for doing this is due to my ongoing lazyness I'm gonna make a spreadsheet that basically writes all my notes for me now, but copying it into notepad will remove any formatting (cell layout) and font formatting before I copy it into the database I have to enter this information into. I've chosen notepad too because I know as it's a base windows component (mostly) it'll be installed on any machine I use, while Word probably won't be.

I'm pretty sure this is possible as I've seen data copied to Word no problem, just wondering what the best way for doing this is?

 

[Dave Punky]

That code actually looks like it would be really useful for something else I want to do in the future.

In regards to this request though it really needs to be in .txt format to copy it over as the database in question is not the coventional type like Access, it's more a web based pile of cack with a text editor built into a webpage which updates the "database". If you copy stuff direct from Excel all kinds of crazyness happens and basically you have to start again, but if you copy from notepad it's fine. It's hard to explain really without being able to demostrate it.

It's not me being daft and adding an extra unnessicery step, it's more of an advantage to do it this way without adding more time to my day. I hope that makes sense, because reading it back it seems like I'm crazy.

 

[Blade Hunter]

Nope, it makes perfect sense. Sometimes we have to work with what we have and if you only have a web interface for uploading information then thats what you have to work with .

That code actually looks like it would be really useful for something else I want to do in the future.

In regards to this request though it really needs to be in .txt format to copy it over as the database in question is not the coventional type like Access, it's more a web based pile of cack with a text editor built into a webpage which updates the "database". If you copy stuff direct from Excel all kinds of crazyness happens and basically you have to start again, but if you copy from notepad it's fine. It's hard to explain really without being able to demostrate it.

It's not me being daft and adding an extra unnessicery step, it's more of an advantage to do it this way without adding more time to my day. I hope that makes sense, because reading it back it seems like I'm crazy.

 

[Kenneth Hobson]

Here is a method that I have used to replace tab characters with commas to separate cells on the same row. Be sure to set the Reference as noted.

Sub test()
    Dim rc As Variant
    Dim s As String, s2 As String
     
    s = ActiveWorkbook.Path & "\Fruits.txt"
    
    [A1] = "Fruit"
    [A2] = "Apple"
    [A3] = "Grape"
    [A4] = "Orange"
    [B1] = "Color"
    [B2] = "Red/Golden"
    [B3] = "Red/Green"
    [B4] = "Orange"
    
    Range("A1:B4").Copy
    s2 = Replace(getClipboard(), vbTab, ",")
    Application.CutCopyMode = False
    MakeTXTFile s, s2
    
    rc = Shell("notepad " & s, vbNormalFocus)
    'Kill s
End Sub

Sub MakeTXTFile(filePath As String, str As String)
    Dim hFile As Integer
    If Dir(FolderPart(filePath), vbDirectory) = "" Then
         MsgBox filePath, vbCritical, "Missing Folder"
        Exit Sub
    End If
     
    hFile = FreeFile
    Open filePath For Output As #hFile
    If str <> "" Then Print #hFile, str
    Close hFile
End Sub
 
 Function FolderPart(sPath As String) As String
    FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function
 
Function getClipboard()
'Add Reference:   'Reference: Microsoft Forms xx Object
    Dim MyData As DataObject
     
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function