LITTLEBLACKDOG.COM

Posted: Thu May 15, 2008 7:12 pm Post subject: Excel Macro

Is it possible to have a Macro output the contents of the currently selected Cell to a text file?

Posted: Fri May 16, 2008 12:33 am Post subject:

The short answer is "of course".

Code to do this would be something like

Dim Outfile, OutStr As String

Open Outfile For Output As #1

OutStr = ActiveCell.Value ' you might need to convert to string

Print #1, OutStr

Posted: Wed Jun 04, 2008 9:31 am Post subject:

This is what I ended up doing.

Code: Select all

Sub SendCellToTextFile2()


    Dim sFullName As String, sPath As String, iFileNum As Integer, iReply As Integer


    If Intersect(ActiveCell, Range("A:D")) Is Nothing Then


        MsgBox "You must select a cell in A or D!", vbExclamation, "ERROR!"


        Exit Sub


    End If


    If Cells(ActiveCell.Row, "D").Value = vbNullString Or Cells(ActiveCell.Row, "B").Value = vbNullString Or Cells(ActiveCell.Row, "A").Value = vbNullString Then


        MsgBox "You need to fill out both cells!", vbExclamation, "ERROR!"


        Exit Sub


    End If


    sPath = ThisWorkbook.Path & "\batch\"


    If Dir(sPath, vbDirectory) = "" Then MkDir sPath


    sFullName = sPath & "String_" & Cells(ActiveCell.Row, "A").Value & ".bat"


    iFileNum = FreeFile


    Open sFullName For Output Access Write As #iFileNum


    Print #iFileNum, Cells(ActiveCell.Row, "D").Value


    Close #iFileNum


    


    iReply = MsgBox(Prompt:="Do you wish to run String_" & Cells(ActiveCell.Row, "A").Value & ".bat", _


            Buttons:=vbYesNo, Title:="Run Batch File")


            


    If iReply = vbYes Then


        Shell sFullName 'Run the batch file


    Else 'They clicked No


        Exit Sub


    End If


End Sub