How to use double quote in macro

English
#1

I am trying have a macro that will place a function in a cell like follows:

in cell would like =if($B1=$A1,“correct”,“incorrect”)

So I tried using this without success. Single quotes just returns #NAME?

args2(0).Value = “=if($B1=$A1,“correct”,“incorrect”)”

How can you pass double quotes for result 1 and 2 of correct and incorrect?

Thanks,
Steve

#2
  1. Why? If you describe your final intention, you will probably get suggested a much better solution.
  2. What you call a function here is a formula.
  3. If you tried something “without success” you should always report in what way the attempt failed.

Double quotes get inserted into string literals by doubling them like in "=IF(testResult;"""correct""";"""bad""")". See correction below.
Once again; This isn’t a recommendation to actually try it this way.

What do you expect this to be good for? Are you tampering with a recorded macro?

= - = - = editing = - = - =
My bad: I posted the formula string above with some quotation marks too many. Correction: "=IF(testResult;""correct"";""bad"")"

#3

Lupp,
I’m not sure what my reason has to do with a solution, but I have a large csv file that I had to split into many due to size. So for each one, I need to go into multiple cells, enter a formula for each (sorry for saying function in the post), so I thought I could use a macro so with one click could populate all the cells with the formulas, then repeat for the next spreadsheet, etc. I was testing with one cell to make sure it worked before doing for all. Here is the test macro:

sub test_formula
 
 	dim document   as object
 	dim dispatcher as object
 	
 	document   = ThisComponent.CurrentController.Frame
 	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 	
 	dim args1(0) as new com.sun.star.beans.PropertyValue
 	dim args2(0) as new com.sun.star.beans.PropertyValue
 	
 	args1(0).Name = "ToPoint"
 	args1(0).Value = "$H$1"
 	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
 	
 	args2(0).Name = "StringName"
 	args2(0).Value = "=if($B1=$A1,"true","false")"
 	dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
 
end sub

Double quotes get inserted into string literals by doubling them like in "=IF(testResult;"""correct""";"""bad""")"
Does not work either.

#4

Did you ever hear of the so-called XY-problem?

Large csv files are widely used. There are solutions for common problems.
How large was the original csv? How did you split it? Why didn’t you use it as a (raw) database table.

There are many issues to consider before beginning to enter Calc formulas into spreadsheet cells by roughly post-processed recorded macros.

But surely you know everything except …

sub test_formula
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
args1(0).Name  = "ToPoint"
args1(0).Value = "$H$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
args2(0).Name  = "StringName"
args2(0).Value = "=IF($B1=$A1;""true"";""false"")"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
end sub

Be sure that I never will use this macro.

#5

Hi,

you could solve the CSV import by:

  • adding a new sheet from the CSV file (menu Sheet > Insert sheet from file)
  • having another sheet where you might have any preset formula that would take its data from the inserted sheet.
    As the inserted sheet will get its name from the csv file name, just name it uniformly beforehand (ex: MyFile.csv). This way the formulae on the other sheet-s will apply after each import process.
#6

Ooops, missed the important point, as noted by Lupp: the source CSV is big.

Go the Lupp suggestion: use a database. No need to import in Calc.

Base may treat a directory where the CSVs are stored as a data source. This works with big CSV files. Then, you may process the data, either in Base (through SQL queries) or by connecting the database to some Calc spreadsheet.

#8

I found my solution, but I must say you should really check your attitude. I asked a question, and I did not appreciate your answer. “But surely you know everything except …” was uncalled for. If I knew everything I wouldn’t ask a question. You learn by asking questions, And I was asking to learn for my own macro, and was simply a syntax question. I never asked you to use it, so again, your comment of “Be sure that I never will use this macro.” was again uncalled for. But since I found the answer which now seems to be so straightforward, apparently you don’t know everything either. And just if anyone else ever has this question, here is the answer and it works perfectly.
args2(0).Value = “=if($B2=$B1,”+CHR$(34)+“correct”+CHR(34)+","+CHR$(34)+“incorrect”+CHR(34)

#9

Note that, when it comes to strings, the “&” concatenation operator should be preferred to the “+” addition one.

As for the remainder, just keep in mind that many posters come here with a question which in fact is just the tip of the iceberg. Your original question seems indeed of this kind. This is why some other – and, hopefully, more accurate – solutions to the underlying problem are often suggested.

Best,

#10

Why do I feel sure that our questioner didn’t even visit the link (in my second comment) to the wikipedia article about the XY problem?

#7 
sheet = ThisComponent.CurrentController.ActiveSheet
cell = sheet.getCellRangeByName("H1")
cell.Formula = "=IF($B1=$A1;""correct"";""incorrect"")"
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.