Macro to open Notepad

[gavindowding]

Hi, i'm trying to write a macro that will open notepad and then copy column A into it then save as a certain filename

But i dont now where to start!

Thanks

 

[Richard Schollar]

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

 

[gavindowding]

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

 

[PureFire]

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

Sub Macro1()
Dim i As Integer

    Open "C:\Temp\gavin.txt" For Output Access Write As #1 '***** Change Directory name and file name to suit
    Worksheets("Sheet1").Select '**** Change sheet name to where column of formulas are
    i = 1
    Do While Not IsEmpty(Cells(i, 1))
        Print #1, Cells(i, 1)
        i = i + 1
    Loop
    Close #1
End Sub

 

[gavindowding]

Hi still cant seem to get this to work!

When i open the text file it is empty?

 

[Richard Schollar]

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

Richard

 

[gavindowding]

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.

 

[Richard Schollar]

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

Sub Macro1() 
Dim i As Integer 

    Open "C:\Temp\gavin.txt" For Output Access Write As #1 '***** Change Directory name and file name to suit 
    Worksheets("Sheet1").Select '**** Change sheet name to where column of formulas are 
    i = 3
    Do While Not IsEmpty(Cells(i, 1)) 
        Print #1, Cells(i, 1) 
        i = i + 1 
    Loop 
    Close #1 
End Sub

Best regards

Richard

 

[Joe Was]

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

 

[gavindowding]

Thats fantastic works a treat.

Thank you