Macro for save as as TXT

Hi,

@6StringJazzer: That excel will generate a code that will be useful for me to use in another platform. That is why I need the character " ( ). In the generated macro using excel, vba does not accept " ( ) in the vba code, it has to be char(x)...isnt it?

@Tinbendr : Generating directly from excel will create those quote around field with comma, and I though I wud have complication about the vbtab but you even add it too. Thank you very much! You really help me!! Thanks again and very very much!

Hi,

The macro works perfectly, however, I would like to know the possibility to add more $Temp from other cell to the line.
If I have data in cell B, C, D, E, and I want them to be include in some parameter of the result, how should I process it?
Here is an example of result that I want it to be..

HELP.xlsmHELP.xlsm.txt

Thanks.

Ur fantastic!! Thank you so much!!

Hi,

Is it possible to add more field to the script? or can you explain how this really work on adding more Temp$?
I want to add more detail to the scripting such as name, etc. But whenever I need it, I will just associate a Temp#$ to the script. I have tried to change so it starts by A5 because my new data started by A5 but it doesnt work.
Here is the example:

Example
HELP.xlsm
Result required
HELP.txt

Also, this file will be saved in the same folder, but what if I want to force it to be save in C:\FILE (if no folder if FILE, it will make one)

A really big thanks for the help..

I guess I figure it out

            Temp3$ = (.Range("B2"))

Sorry, I did not clearly explain the result. I want it to be included in each line of the generated result.

Sub Generate_Code()
Dim WS As Worksheet
Dim I As Long
Dim LastRow As Long
Dim FF As Integer
Dim Temp$, Temp$2, Temp$3

    Set WS = ActiveSheet

    FF = FreeFile

    Open ActiveWorkbook.Path & "\" & ActiveWorkbook.NAME & ".mac" For Output As #FF

    Print #FF, "[PCOMM SCRIPT HEADER]"
    Print #FF, "LANGUAGE=VBSCRIPT"
    Print #FF, "DESCRIPTION="
    Print #FF, "[PCOMM SCRIPT SOURCE]"
    Print #FF, "OPTION EXPLICIT"
    Print #FF, "autECLSession.SetConnectionByName" & Chr(40) & "ThisSessionName" & Chr(41)
    Print #FF, ""
    Print #FF, "REM This line calls the macro subroutine"
    Print #FF, "subSub1_"
    Print #FF, ""
    Print #FF, "sub subSub1_" & Chr(40) & Chr(41)
    Print #FF, ""
    Print #FF, ""

    With WS
        For I = 5 To .Range("A" & .Rows.Count).End(3)(1).Row
            LCol = .Cells(I, .Columns.Count).End(xlToLeft).Column
            Temp2$ = ""
            If LCol > 1 Then
                For J = 2 To .Cells(I, .Columns.Count).End(xlToLeft).Column
                    Temp2$ = Temp2$ & .Cells(I, J)
                Next
            End If

            Temp$ = Left(.Range("A" & I) & "_________", 9)
            Print #FF, "   autECLSession.autECLPS.SendKeys " & Chr(34) & Temp$ & Chr(34) & ", 23,47"
            Print #FF, "   autECLSession.autECLPS.SendKeys " & Chr(34) & "[enter]" & Temp2$ & Chr(34)
            Print #FF, "   autECLSession.autECLPS.SendKeys " & Chr(34) & "[printps]" & Temp3$ & Chr(34)
            Print #FF, "   autECLSession.autECLPS.Wait 700"
        Next I
    End With
    Print #FF, ""
    Print #FF, "end sub "
    Print #FF, ""
    Close #FF
End Sub

But the Temp3$, I want to associate it to the cell B2.
So I can add Temp4$ to B3, and so on..

Thanks.

Hi,

Another question about using this macro is about date.

I am currently adding a date to a cell, and I would like to know when exporting to text, it becomes:
28/03/2014, but I would like to make it become: 28MAR2014 instead. I have formatted the cell itself, but when exporting to text, it's still in 28/03/2014. How to force the cell to be in that DDMMMYYYY?

I am using this code:

            PROCESS_DATE$ = (.Range("B3"))

What should I add to this code to force the format date?

Thanks.

I did try this, but I didnt get any result, but now it's working. However, the date in B3 is:
28mar2014, but when exporting to text, it become 28Mar1487?? Why is that?

Sorry, nevermind, was inputting the wrong format code MMDDDYYYY instead of DDMMMYYYY. My bad..

Hi Tinbendr,

I have changed the save location of the script to be save in C:\MACRO, but when the folder does not exist, the macro won't work. How can I mod this part to make it prompt up to ask where to save if the designate folder cannot be found?

Thanks.

Hi,

Sorry for asking again, but what if this time I want to do this way, what should I modify in the code to associate each cell right beside the Temp1$ separately?

Sub GenerateCode()
Dim WS As Worksheet
Dim I As Long
Dim LastRow As Long
Dim FF As Integer
Dim Temp$

    Set WS = ActiveSheet

    FF = FreeFile

    Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ".txt" For Output As #FF

    Print #FF, "[HEADER]"
    Print #FF, "LANGUAGE=SCRIPT"
    Print #FF, "DESCRIPTION="
    Print #FF, "[SOURCE]"
    Print #FF, "OPTION"
    Print #FF, "autoadd.Name" & Chr(40) & "Work" & Chr(41)
    Print #FF, ""
    Print #FF, "REC Macro starts here"
    Print #FF, "sub_"
    Print #FF, ""
    Print #FF, "sub sub_" & Chr(40) & Chr(41)
    Print #FF, ""
    Print #FF, ""

    With WS
        For I = 1 To .Range("A" & .Rows.Count).End(3)(1).Row
            Temp$ = " " & Left(.Range("A" & I) & "_________", 9)
            Print #FF, "   autoadd.syntax.key " & Chr(34) & Temp$ & vbTab & Chr(34) & ", 23,47"
            Print #FF, "   autoadd.syntax.key " & Chr(34) & "[press]Temp2$" & Chr(34) & vbTab & vbTab
            Print #FF, "   autoadd.syntax.key " & Chr(34) & "[word]Temp3$" & Chr(34) & vbTab & vbTab
            Print #FF, "   autoadd.syntax.key " & Chr(34) & "Temp4$" & Chr(34) & vbTab & vbTab
            Print #FF, "   autoadd.syntax.Wait 700" & vbTab & vbTab
        Next I
    End With
    Print #FF, ""
    Print #FF, "end sub "
    Print #FF, ""
    Close #FF
End Sub

HELP.xlsmHELP.xlsm.txt

Thanks..

Hi. Exactly. I want to breakout the data across each line. Because I realise that instead of concatenate, I can just out them all together in 1 cell instead.

I want to breakout exactly as you stated in ur topic.

Thanks!

It is possible to not use IF in this function?

My data will all have Column A, B, C, D and E, so I think we can eliminate that IF function no?
A B C D E
334 DOG CAT COX CHILD
243 CAT DOG CHILD COX
322 COX CAT CHILD DOG

What do you think?

I mod it to this way and it seems to work, but can you check if it's good still?

Sub GenerateCode2()
Dim WS As Worksheet
Dim I As Long, J As Long
Dim LastRow As Long, LCol As Long
Dim FF As Integer
Dim Temp$

    Set WS = ActiveSheet

    FF = FreeFile

    Open ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ".txt" For Output As #FF


    Print #FF, "[HEADER]"
    Print #FF, "LANGUAGE=SCRIPT"
    Print #FF, "DESCRIPTION="
    Print #FF, "[SOURCE]"
    Print #FF, "OPTION"
    Print #FF, "autoadd.Name" & Chr(40) & "Work" & Chr(41)
    Print #FF, ""
    Print #FF, "REC Macro starts here"
    Print #FF, "sub_"
    Print #FF, ""
    Print #FF, "sub sub_" & Chr(40) & Chr(41)
    Print #FF, ""
    Print #FF, ""

    With WS
        For I = 1 To .Range("A" & .Rows.Count).End(3)(1).Row
            Temp$ = " " & Left(.Range("A" & I) & "_________", 9)
            Temp2$ = .Range("B" & I)
            Temp3$ = .Range("C" & I)
            Temp4$ = .Range("D" & I)           
                Print #FF, "   autoadd.syntax.key " & Chr(34) & "[press]" & Temp2$ & Chr(34) & vbTab & vbTab
                Print #FF, "   autoadd.syntax.key " & Chr(34) & "[word]" & Temp3$ & Chr(34) & vbTab & vbTab
                Print #FF, "   autoadd.syntax.key " & Chr(34) & Temp4$ & Chr(34) & vbTab & vbTab
                Print #FF, "   autoadd.syntax.Wait 700" & vbTab & vbTab
        Next I
    End With

    Close #FF
End Sub