Solved: Save as a Plain Text file on a Mac

[soldtoscienc]

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!

[mikerickson]

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.

[tpoynton]

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.

[soldtoscienc]

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]

[tpoynton]

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]

[soldtoscienc]

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!

[mikerickson]

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.

[soldtoscienc]

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!

[tpoynton]

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!

[mikerickson]

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]

[tpoynton]

Thanks for explaining/providing code Mike!

[soldtoscienc]

Can't thank you guys enough! Works great!

Thanks!