Macro to open Notepad

Hi

If you want to save down a text file from Excel with the contents of the A column, then I think you're best suited to either copy the A column to a fresh sheet then save as a text file or to write the data to a text file directly from VBA. Neither of these two approaches involves Notepad - was there a specific reason you wanted to use Notepad?

Best regards

Richard

Hi, what i am trying to do is save the following formula as a text file:

=IF(Sheet1!A3="","","00"&","&Sheet1!A3&","&"FLOOR"&","&TEXT(Sheet1!B3,"DD/MM/YYY")&","&TEXT(Sheet1!B3,"DD/MM/YYY")&","&Sheet1!D3&","&Sheet1!E3&",")

But when I do it comes out like this:

"00,18111,FLOOR,12/05/2006,12/05/2006,0900,0103,"

Rather than like this:

00,18111,FLOOR,12/05/2006,12/05/2006,0900,0103,

Which is exactly how i need it becasuse the program i am trying import it into will only read it with a comma on the end and without the "

I have found that if i copy the contents of the cell into notepad first and then save it as a text file the program will import it with no problem.

Any ideas?!

Cheers

Just change the macro on the lines where ive put ****

Hi still cant seem to get this to work!

When i open the text file it is empty?

Where on your sheet does the data (formulas) begin? Is it in cell A1 or another? Do you have header rows?

Richard

Hi,

There are no header rows so at the moment the data begins in A3 and will continue for around 500 rows but this will vary.

Try PF's code as adjusted thus, in that case:


Best regards

Richard

I think you want to replace "Output" with "Append" to attach the new information to the bottom of the file, in the code above?

You can also do it with a scripting object, like this:


Sub OpenTextFile()
'Standard Module Code, Like: Module1.
'Will append file if needed!

'ForReading 1 Open a file for reading only. You can't write to this file.
'ForAppending 8 Open a file and write to the end of the file.

'TristateUseDefault –2 Opens the file using the system default.
'TristateTrue –1 Opens the file as Unicode.
'TristateFalse 0 Opens the file as ASCII.

Const ForReading = 1, ForWriting = 2, ForAppending = 3
'Dim fs, f
Dim myFile As String
Dim NPOPFile As String
Dim myText As String
Dim MessageT, TitleT, DefaultT
Dim Msg, Style, Title, Help, Ctxt, Response

Msg = "You will append your new text to any in the file you select if you continue!" _
& vbCr & vbCr & "Do you want to continue?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Caution!" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
GoTo myContinue
Else ' User chose No.
GoTo myEnd
End If

'Get text to write to file!
myContinue:
MessageT = "Enter your text here:" ' Set prompt.
TitleT = "Append Text File with Data!" ' Set title.
DefaultT = "Add Data Here!" ' Set default.
' Display message, title, and default value.
myText = InputBox(MessageT, TitleT, DefaultT)

On Error GoTo Err_OpenTextFile
'Use this Text file!
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If fileToOpen <> False Then
End If

myFile = fileToOpen
NPOFile = "NotePad.exe " & myFile

'Work with file [Append Text to File].
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(myFile, 8, TristateUseDefault)
f.Write Chr(9) & myText
f.Close

'Open NotePad with a data file!
ActiveSheet.Select
Call Shell(NPOFile, 1)

Exit_OpenTextFile:
Exit Sub

Err_OpenTextFile:
MsgBox Err.Description
Resume Exit_OpenTextFile
myEnd:
End Sub

Thats fantastic works a treat.

Thank you