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?

 

[Ruddles]

Try this:-

Option Explicit
 
Public Sub XLtoText()
 
  Dim iPtr As Integer
  Dim sFileName As String
  Dim intFH As Integer
  Dim aRange As Range
  Dim oCell As Range
  Dim iRec As Long
  
  iPtr = InStrRev(ActiveWorkbook.FullName, ".")
  sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".txt"
  sFileName = Application.GetSaveAsFilename(InitialFileName:=sFileName, FileFilter:="Text Files (*.txt), *.txt")
  If sFileName = "False" Then Exit Sub
  
  Set aRange = Application.InputBox("Select a range:-", , Selection.Address, , , , , Type:=8)
  
  Close
  intFH = FreeFile()
  Open sFileName For Output As intFH
  iRec = 0
  
  For Each oCell In aRange
    Print #intFH, oCell.Value
    iRec = iRec + 1
  Next oCell
  
  Close intFH
  
  MsgBox "Finished: " & CStr(iRec) & " records written to " & sFileName & Space(10), vbOKOnly + vbInformation
 
End Sub

Select a range of cells and run the macro or just run it and it will prompt you for a range of cells. Let me know if it doesn't do exactly what you want.

 

[Dave Punky]

Ok the final product of that macro is what I need, although I already know the range of cells and I don't need to save the file anywhere really, essentially just needs to open notepad and copy the text in there. However that macro you've provided might be useful in another one of my crazy thoughts so much appreciated for that!

Is it possible to modify that script so essentially what it does is:
Copy Range (A1:A12) > Open New Notepad Document > Paste to Notepad

And something I've thought of as well, if I don't fill up the whole area with notes (so say I get to A8 and then the rest is blank) is there a way for it to ignore copying the rest (so A9 to A12). That amount may vary so I'm not sure how it would determine the last line as 0 before ignoring the rest, so say one time I reach A12, the next time I reach A8 and the time after that I only get to A3. However if that's not possible then it doesn't matter too much, just a thought that would be nice.

 

[Ruddles]

Try this:-

Option Explicit
 
Public Sub XLtoNotepad()
 
  Dim TimeStamp As String
  Dim sTimeStamp As String
  Dim sFileName As String
  Dim intFH As Integer
  Dim aRange As Range
  Dim oCell As Range
  Dim iRec As Long
  
  TimeStamp = Now()
  TimeStamp = Mid(TimeStamp, 7, 4) & Mid(TimeStamp, 4, 2) & Mid(TimeStamp, 1, 2) _
            & Mid(TimeStamp, 12, 2) & Mid(TimeStamp, 15, 2) & Mid(TimeStamp, 18, 2)
  sFileName = Environ("TEMP") & "\" & "temp" & TimeStamp & ".txt"
  If Dir(sFileName) <> "" Then Kill sFileName
  
  Set aRange = Application.InputBox("Select a range:-", , Selection.Address, , , , , Type:=8)
  
  Close
  intFH = FreeFile()
  Open sFileName For Output As intFH
  iRec = 0
  
  For Each oCell In aRange
[COLOR=red][B]    If Len(oCell) <> 0 Then[/B][/COLOR] Print #intFH, oCell.Value
    iRec = iRec + 1
  Next oCell
  
  Close intFH
  
  Shell "notepad " & sFileName, vbNormalFocus
  Kill sFileName
  
End Sub

As you can probably make out, I've just modified the previous code; it still creates a file but instead of prompting you for a location, it places it in your TEMP directory, opens it in Notepad and then deletes it. It's a bit of a kludge but I'm a bit busy at the moment busy and anyway, it works fine.

The bit in red ensures that empty cells aren't copied: you can paste this into the corresponding place in the previous code if you want to.

 

[Dave Punky]

I really appreciate the help Ruddles, that works exactly how I want it to except for the application box. Is there a way of setting the range without the application box appearing?

I know it's all down to this bit:

Set aRange = Application.InputBox("Select a range:-", , Selection.Address, , , , , Type:=8)

But every modification I've attempted to do to just not bother with an application box it just doesn't seem to like. I'm not the most confident when it gets to the more advanced stages of VBA though so I could be missing something obvious.

If say I just want it to select cells A1:A12 every time regardless and bypass the need for the "Select a range", how would I go about doing this?

 

[Ruddles]

The statement Set aRange = Application.InputBox selects a range of cells which the program then writes to Notepad, so if you remove it you have to replace it with something else which selects a range of cells, For example, if you've already selected a range of cells in the worksheet and you want to write those to Notepad, replace the Set aRange = Application.InputBox statement with:-

Set aRange = Selection

If you know you're only ever going to want to select A1:A12, replace it with:-

Set aRange = Range("A1:A12")

Try that.

 

[Blade Hunter]

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.

Why the extra step? Why not upload straight to the DB from Excel?

 

[Dave Punky]

That's done it, I forgot to put Range when I was putting the cells I wanted to copy into that area.

Thanks Ruddles, I really appreciate the help!

 

[Dave Punky]

Why the extra step? Why not upload straight to the DB from Excel?

That's a good question. The database I'm using does not like cells at all when copied out of Excel and basically goes a bit screwy. Also in notepad I can edit anything I've missed before copying it over.

It sounds like it doesn't make sense, but if you were me you'd seriously understand the reasoning for it!

 

[Blade Hunter]

Sorry I don't think I explained properly, upload using code.

Here is one of my routines which uploads, then reads it back and will shade it green if it matches (verification)

Sub UploadSMECodes()
    For X = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Application.StatusBar = "Processing row " & X & " of " & Range("A" & Rows.Count).End(xlUp).Row
        ProdID = Range("A" & X).Text
        UPC = Range("B" & X).Text
        Call UploadData
        Verified = False
        Call CheckData
        If Verified Then
            Range("A" & X & ":B" & X).Interior.Color = 5296274
        Else
            Range("A" & X & ":B" & X).Interior.Color = 255
        End If
    Next
    Application.StatusBar = False
End Sub
 
Sub UploadData()
NoRefresh = True
sqlText = "Insert into banditstaging.dbo.DSP_SME_PRODUCT_UPC values('" & UPC & "','" & ProdID & "')"
Call Main
End Sub
 
Sub CheckData()
NoRefresh = False
sqlText = "Select VCH_IPID from banditstaging.dbo.DSP_SME_PRODUCT_UPC Where VCH_UPC = '" & UPC & "'"
Call Main
End Sub
 
Sub Main()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
On Error Resume Next
Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=removed<REMOVED>;USER ID=removed<REMOVED>;PASSWORD=removed<REMOVED>"
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.CommandText = sqlText
Set RS = Cmd.Execute
If Not NoRefresh Then
    If RS.Fields(0).Value = ProdID Then Verified = True
End If
End Sub