Solved: Chr(34) returning ""

[soldtoscienc]

Hello!

For some reason Chr(34) in my excel macro is returning "" instead of ". I also tried "" but that is also returning "". Here is the line.

Does anybody know how to return a single "?

output = output + "tell application " + Chr(34) + "Finder" + Chr(34) + VBA.vbCrLf

[soldtoscienc]

Or how to write an escape character in VBA?

[Kenneth Hobs]

Works fine for me in the Immediate window and press Enter:
?"tell application " + Chr(34) + "Finder" + Chr(34) + VBA.vbCrLf

[Bob Phillips]

I get the same as Kenneth.

[CodeNinja]

You can use 2 quotes in vba strings to represent a single double quote... IE

msgbox("Send ""This"" message.")

should send a message box stating: Send "This" message.

Also, you are better using ampersand (&) than the Plus (+) symbol because depending on the variable type, VBA could think it should add the values of the strings instead of concatenate.

so for your circumstances, use:
Output = Output & "tell application ""Finder""" & vbCrLf

[soldtoscienc]

Ah, I tried another test and the "" works fine.

So it's something else in the macro causing "" and Chr(34) to return "".

I'm going to risk the scorn of all and post the whole thing, because I for the life of me can't figure out why I can't print a "

The line is right below the Dims after all the "graphicType_lookup(31) = 7" stuff, towards the bottom, sorry for the long VB!

And thanks for the & instead of + tip!

Sub applescript_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

output = output + "tell application " + Chr(34) + "Finder" + Chr(34) + VBA.vbCrLf + " activate" & VBA.vbCrLf + " open folder " + Chr(34) + "DSM Graphics PSD Output" + Chr(34) + VBA.vbCrLf

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 + "set name of document file " + Chr(34) + "DSM_" + renameType + "_G_" + graphicTypes(col) + "_" + VBA.Trim(VBA.Str(overall_counter)) + ".psd" + Chr(34) + " of folder " + Chr(34) + "DSM Graphics PSD Output" + Chr(34) + " to " + Chr(34) + "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" + Chr(34) + 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 & "end tell" & VBA.vbCrLf


' Get the file name.
output_filename = Application.GetSaveAsFilename(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

[Kenneth Hobs]

When making variables, do not use reserved words that VBA uses, i.e. Output.

Use Print rather than Write.

Sub test()
Dim sOutput As String, intOutFile As Integer, output_filename As Variant

sOutput = "tell application " & Q("Finder") & vbCrLf

' Get the file name.
output_filename = Application.GetSaveAsFilename(Title:="Save As File Name")

' Save the file with the new name.
intOutFile = FreeFile
Open output_filename For Output As intOutFile
'Write #intOutFile, sOutput 'adds quotes
Print #intOutFile, sOutput
Close intOutFile

MsgBox sOutput
Shell "cmd /c " & output_filename, vbNormalFocus
End Sub

Function Q(str As String) As String
If Left(str, 1) = """" Then
Q = str
Else: Q = """" & str & """"
End If
End Function

[CodeNinja]

CHR(34) is correct. It should give you one double quote. I think it may actually be correct, but the writing of the file might be reading it funky (although this is a total guess). Why not put a stop in the code before you get to that point and step through checking output in the immediate window to see what it really is as it steps through... this might help you find where it is going wonky.

Kenneth has a great point above... that could be screwing with the code as well.

[soldtoscienc]

Thanks for all your help!! I'll clean it up and try to figure out where it's getting wonky.

I'm gonna get back on this when I get back to work on Monday.

Until then.. Cheers! and have a good weekend!

[soldtoscienc]

Wait a min! Simply changing Write to Print got rid of all the double quotes!!

Strange... but Solved!!!!

Thanks!!!!!

[mikerickson]

I'm puzzled.
Why are you using VBA to write an applescript file?
I could see writing an apple script and using the VBA MacScript command like
MsgBox MacScript("choose file")

But if you are writting to a file, why not Apple Script all the way?

[shrivallabha]

Wait a min! Simply changing Write to Print got rid of all the double quotes!!

Strange... but Solved!!!!

Thanks!!!!!
See VBA Developer Help for "Write" and "Print" statements to understand the difference. You'll see Write interprets data and Print simply prints string supplied.

[soldtoscienc]

@mikerickson

I know it's pretty silly, this is the last step in a long process of macros and photoshop actions that copies words highlighted using different styles in Word into an excel sheet, there they're cleaned up and exported as a csv, loaded into photoshop and exported as layer comps.

Photoshop can't handle empty cells in the csv, so we add the word "null" into all the empty spaces. This macro creates an applescript that renames the keeper files and extra "null" files are deleted. This lets us process thousands of graphics at a time really quickly.

My company used to do this on an old windows laptop and all the macros were written by a programmer who isn't here anymore. This last rename and delete step used to be done with a batch file.

As you can probably tell I'm not much of a programmer or I'd probably find a better way to do all this while rewriting the process to work on macs. :) I bet applescript could do most if not all of this?

But I'm happy it's working, however jury-rigged it is!

Thanks for all your help guys!!!