Consulting

Results 1 to 12 of 12

Thread: Solved: Save as a Plain Text file on a Mac

  1. #1

    Solved: Save as a Plain Text file on a Mac

    Hello!

    I'm trying to bring a macro from the PC to the Mac. For some reason it works fine in excel 2010 on the PC, but comes up as an error in excel 2011 on the Mac.

    The offending code:

    [VBA] output_filename = Application.GetSaveAsFilename( _
    FileFilter:="Plain Text, *.txt", _
    Title:="Save As File Name")[/VBA]

    Thanks!


    P.S. If you need more information about the rest of the macro or what I'm actually trying to do, let me know. It's kind of a long story and I didn't want to over-complicate anything. Cheers!

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    What is the code that actually saves the file?
    Tags (.txt) are optional on Macs and so should be added when the file is saved, not during the creation of the name of the file.

  3. #3
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    filefilter is not supported on the mac, so removing that should get the code working. If you need to restrict the dialog to display only certain file types, you can use applescript on the mac to do that...Mike would know that code much better than I though.

  4. #4
    Hello! Sorry for the late reply!

    This macro was used to create a batch file on the PC which would rename and delete photoshop files based on data in the excel sheet. I didn't actually write the code and I'm not much of a programmer, but I'm trying to wade through and transfer it over.

    Actually, I'm not sure if it's possible but I'd love to skip a step and create an applescript file. I was just going to manually create the applescript from the text file, but if you guys know the VBA to save as an applescript file that would be amazing!

    In any case, here's the entire original macro (when it made the batch file instead of the txt file). All I did was replace "Batch File" with "Plain Text" way down at the bottom, in hopes it would work on the mac.

    Thanks for all the help!



    [VBA]Sub process_renames()

    Dim numOfActivities, col, overall_counter, individual_counter, addl_counter(7), graphicType_lookup(33) As Integer
    Dim graphicTypes(33), output, output_filename, graphicTypesAbr(33) As String

    Dim renameType As String
    renameType = "SP"

    Dim renameOutType As String
    renameOutType = "EM"

    output = ""

    graphicTypes(1) = "AS1"
    graphicTypes(2) = "AS2"
    graphicTypes(3) = "AS3"
    graphicTypes(4) = "TT1"
    graphicTypes(5) = "TT2"
    graphicTypes(6) = "SN1"
    graphicTypes(7) = "SN2"
    graphicTypes(8) = "OBJ1"
    graphicTypes(9) = "OBJ2"
    graphicTypes(10) = "OBJ3"
    graphicTypes(11) = "VOC1"
    graphicTypes(12) = "KM1"
    graphicTypes(13) = "KM2"
    graphicTypes(14) = "KM3"
    graphicTypes(15) = "KM4"
    graphicTypes(16) = "KM5"
    graphicTypes(17) = "KM6"
    graphicTypes(18) = "KM7"
    graphicTypes(19) = "KM8"
    graphicTypes(20) = "KM9"
    graphicTypes(21) = "KM10"
    graphicTypes(22) = "KM11"
    graphicTypes(23) = "TP1"
    graphicTypes(24) = "TP2"
    graphicTypes(25) = "TP3"
    graphicTypes(26) = "TP4"
    graphicTypes(27) = "TP5"
    graphicTypes(28) = "TP6"
    graphicTypes(29) = "TP7"
    graphicTypes(30) = "TP8"
    graphicTypes(31) = "TP9"
    graphicTypes(32) = "TP10"
    graphicTypes(33) = "TP11"

    graphicTypesAbr(1) = "AS"
    graphicTypesAbr(2) = "AS"
    graphicTypesAbr(3) = "AS"
    graphicTypesAbr(4) = "TT"
    graphicTypesAbr(5) = "TT"
    graphicTypesAbr(6) = "SN"
    graphicTypesAbr(7) = "SN"
    graphicTypesAbr(8) = "OBJ"
    graphicTypesAbr(9) = "OBJ"
    graphicTypesAbr(10) = "OBJ"
    graphicTypesAbr(11) = "VOC"
    graphicTypesAbr(12) = "KM"
    graphicTypesAbr(13) = "KM"
    graphicTypesAbr(14) = "KM"
    graphicTypesAbr(15) = "KM"
    graphicTypesAbr(16) = "KM"
    graphicTypesAbr(17) = "KM"
    graphicTypesAbr(18) = "KM"
    graphicTypesAbr(19) = "KM"
    graphicTypesAbr(20) = "KM"
    graphicTypesAbr(21) = "KM"
    graphicTypesAbr(22) = "KM"
    graphicTypesAbr(23) = "TP"
    graphicTypesAbr(24) = "TP"
    graphicTypesAbr(25) = "TP"
    graphicTypesAbr(26) = "TP"
    graphicTypesAbr(27) = "TP"
    graphicTypesAbr(28) = "TP"
    graphicTypesAbr(29) = "TP"
    graphicTypesAbr(30) = "TP"
    graphicTypesAbr(31) = "TP"
    graphicTypesAbr(32) = "TP"
    graphicTypesAbr(33) = "TP"

    addl_counter(1) = 1
    addl_counter(2) = 1
    addl_counter(3) = 1
    addl_counter(4) = 1
    addl_counter(5) = 1
    addl_counter(6) = 1
    addl_counter(7) = 1

    graphicType_lookup(1) = 1
    graphicType_lookup(2) = 1
    graphicType_lookup(3) = 1
    graphicType_lookup(4) = 2
    graphicType_lookup(5) = 2
    graphicType_lookup(6) = 3
    graphicType_lookup(7) = 3
    graphicType_lookup(8) = 4
    graphicType_lookup(9) = 4
    graphicType_lookup(10) = 4
    graphicType_lookup(11) = 5
    graphicType_lookup(12) = 6
    graphicType_lookup(13) = 6
    graphicType_lookup(14) = 6
    graphicType_lookup(15) = 6
    graphicType_lookup(16) = 6
    graphicType_lookup(17) = 6
    graphicType_lookup(18) = 6
    graphicType_lookup(19) = 6
    graphicType_lookup(20) = 6
    graphicType_lookup(21) = 6
    graphicType_lookup(22) = 6
    graphicType_lookup(23) = 7
    graphicType_lookup(24) = 7
    graphicType_lookup(25) = 7
    graphicType_lookup(26) = 7
    graphicType_lookup(27) = 7
    graphicType_lookup(28) = 7
    graphicType_lookup(29) = 7
    graphicType_lookup(30) = 7
    graphicType_lookup(31) = 7
    graphicType_lookup(32) = 7
    graphicType_lookup(33) = 7

    Dim perActivity_counter(13, 7) As String
    Dim i, k As Integer

    For i = 1 To 13 Step 1
    For k = 1 To 7 Step 1
    perActivity_counter(i, k) = 1
    Next
    Next


    For col = 1 To 33 Step 1
    overall_counter = 1
    For numOfActivities = 1 To 13 Step 1
    For individual_counter = 1 To Application.Sheets(1).Cells(numOfActivities, col) Step 1
    output = output + "rename DSM_" + renameType + "_G_" + graphicTypes(col) + "_" + VBA.Trim(VBA.Str(overall_counter)) + ".psd DSM_" + renameOutType + "_" + VBA.Trim(VBA.Str(numOfActivities)) + "_G_" + graphicTypesAbr(col) + "_" + IIf(Len(VBA.Trim(VBA.Str(perActivity_counter(numOfActivities, graphicType_lookup(col))))) < 2, "0" + VBA.Trim(VBA.Str(perActivity_counter(numOfActivities, graphicType_lookup(col)))), VBA.Trim(VBA.Str(perActivity_counter(numOfActivities, graphicType_lookup(col))))) + ".psd" + VBA.vbCrLf
    perActivity_counter(numOfActivities, graphicType_lookup(col)) = perActivity_counter(numOfActivities, graphicType_lookup(col)) + 1
    overall_counter = overall_counter + 1
    addl_counter(graphicType_lookup(col)) = addl_counter(graphicType_lookup(col)) + 1
    Next
    Next
    Next

    output = output + "del DSM_" + renameType + "_G_*" + VBA.vbCrLf

    ' Get the file name.
    output_filename = Application.GetSaveAsFilename( _
    FileFilter:="Batch Files, *.bat", _
    Title:="Save As File Name")

    ' Save the file with the new name.
    intOutFile = FreeFile
    Open output_filename For Output As intOutFile
    Write #intOutFile, output
    Close intOutFile
    End Sub[/VBA]

  5. #5
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location

    ' Save the file with the new name.

    give this a shot, replacing below with what you have there now for selecting the file

    [vba]
    ' Get the file name.
    On Error Resume Next
    output_filename = MacScript("choose file of type {""txt""} with prompt ""Browse to select a text file"" ")
    On Error GoTo 0

    ' Save the file with the new name.
    [/vba]

  6. #6
    I tried swapping in that code, but instead of saving as a text file, it was looking for me to choose a text file, as if to open one.

    Just for kicks I created a text file and selected it, but after that I got an error. When opening the debugger it highlighted this line:

    [VBA]Open output_filename For Output As intOutFile[/VBA]

    So it seems that the whole last part of the code starting with the "Get the file name" comment is unhappy. Again, I don't even know if this is even possible on the mac. Maybe there is a way for me to write the commands back into excel instead of creating a file? Then I can manually create the applescript from there?

    Or the best case scenario would be to write an applescript file. If possible.

    Thanks for the help and let me know if you have any other ideas!

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    It looks like the OP code is using GetSaveAsFileName to find the name of an existing file. (Or does Open output_file For Output.... create a new file?)

    If you are asking for the file path for a new file, the .txt restriction isn't very important on a Mac.

    If you are asking for the file path to an existing file, the MacScript command will meet your needs.

  8. #8
    I'm not sure which command does it, but I do know that this macro opened up a "Save As" box which created a new file, and by changing [VBA]FileFilter:="Batch Files, *.bat"[/VBA] to [VBA]FileFilter:="Plain Text, *.txt"[/VBA] I was able to create a text file instead of a batch file.

    If you opened the new file (whether it was a batch file or a text file) in a simple text editor, it was just a long list of rename commands.

    For example: "rename DSM_SP_G_AS1_1.psd DSM_EM_1_G_AS_01.psd"

    Then at the very bottom a delete commend. (del DSM_SP_G_*). Which would delete anything that wasn't renamed.

    So using the data in the excel sheet, we could rename the photoshop files we wanted to keep and delete the extra ones.

    All the photoshop files and this excel sheet were generated from other macros and actions if you guys were wondering why I'm doing all this. We generate thousands of graphics at a time. I'm sure it sounds kind of crazy haha.

    The Batch File is a simple program that we put in the same folder as all the photoshop files and double click it and it does its thing. Doesn't work on the Mac though, so I'm assuming I'd just do it with applescript. All I need is to get the data from excel to applescript. I figured making a text file was the easiest way.

    Again thanks for bearing with me! I know I'm probably not making a lot of sense!

  9. #9
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    sorry I led you astray! changing to
    [vba]
    ' Get the file name.
    output_filename = Application.GetSaveAsFilename(Title:="Save As File Name")
    ' Save the file with the new name.
    [/vba]

    should get it working, although you'll need to change the file type from xlsx to txt in the save as dialog yourself.

    I dont know how to get macscript to display a 'save as' dialog, but I'd be interested in knowing!

  10. #10
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Both of these test programs work for me.

    [VBA]Sub Trial()
    Dim uiFilePath As String, FileParts() As String

    uiFilePath = Application.GetSaveAsFilename(initialfilename:="myVBAFile")
    If uiFilePath = "False" Then MsgBox "cancel pressed": Exit Sub

    Rem force input to .txt extention
    FileParts = Split(uiFilePath, ".")
    FileParts(UBound(FileParts)) = "txt"
    uiFilePath = Join(FileParts, ".")

    Rem write to file
    Open uiFilePath For Output Access Write As #1
    Print #1, "test line all VBA" & vbCr & Now()
    Close 1
    End Sub

    Sub Test()
    Dim uiFilePath As String

    uiFilePath = "False"
    On Error Resume Next
    uiFilePath = MacScript("choose file name with prompt ""Prompt"" " _
    & "default name ""myAS_File"" " _
    & "default location ""Macintosh HD:Users:mericksonesktop"" as alias")
    On Error GoTo 0

    If uiFilePath = "False" Then MsgBox "cancel pressed": Exit Sub

    Rem force input to .txt extention
    uiFilePath = uiFilePath & ".txt"

    Rem write to file
    Open uiFilePath For Output Access Write As #1
    Print #1, "test line Apple Script" & vbCr & Now()
    Close 1
    End Sub[/VBA]

  11. #11
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    Thanks for explaining/providing code Mike!

  12. #12
    Can't thank you guys enough! Works great!

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •