Syntax. Assigning a string value from a filename by using a formula

[gvaltat]

Hi,

I am discovering VBA, and I have what is certainly a very basic question.

My goal is to add to a WorkBook the unique sheet "Review" from another WorkBook "nwWbName", and to rename it.

1- I have succeeded for this part, browsing the folders, selecting the file, and adding the sheet.

2- For the second part, I want to rename the sheet based on the name (nwWbName) of the original file. I know that it contains one and only one hyphen (-) and I intend to use a formula to extract a string like "xx-yy" and use it as the sheet's name.

In the code below, there is only the code to display the result of the first step of the formula. That is why "LangReview" is for now an integer.
Could somebody tell me how to assign a value to the integer "LangReview"? Once I will have that, I am confident that I will be able to finish the sub

Thank you in advance!

Please Login or Register  to view this content.

[ajryan88]

Hi,

Try:

Please Login or Register  to view this content.

I hope this helps

[gvaltat]

Thank you, I will try that this evening and will let you know.
But is there a way to use the FIND formula? I am much more comfortable using the "regular" formula, and I will need them somewhere else.
Thanks again.

[ajryan88]

No problems

InStr is the VBA equivalent of the Excel FIND function. If you really want to use the find formula, you would have to write (from VBA) a formula to a cell on a worksheet to return the location of the hyphen:

Please Login or Register  to view this content.

Does this work for you?

[gvaltat]

Sorry for not answering earlier, I didn't have time to check your proposals before.

I have started to use other VBA equivalents, and I am feeling more comfortable now. Thank you for the example!

Regarding the other option with the "regular" formulas, I have not been able to make it work, even after experimenting around; I get a run-time error 13, type mismatch.
LangReview is a Dim Integer
Do you have any idea what I am doing wrong?

Thank you

[ajryan88]

Ah yes, sorry, I got caught by the double-double quotes trick in VBA. Try this:

Please Login or Register  to view this content.

Sorry about that

[gvaltat]

Thank you, it works now.
But I am at a loss too. Why these triple " ?

[ajryan88]

In VBA (or pretty much any other programming language), when you want to insert a double-quote inside a string, you have to enter it as 2 double quotes, so that the program doesn't read it as the end of the string.
So the triple quotes say insert a double-quote, and then exit the string (so we can enter the variable nwWbName), and similarly for the second triple ".

I hope this is clear, I'm generally not as good at explaining things as I am just providing answers :| This website might explain it a little bit better, along with some other useful rules and tips

Glad I could help anyway

Please don't forget to mark this thread as solved, and please click on the * next to my post to say thanks

[gvaltat]

Thank you for the explanations and for the link. Even if I don't understand everything, I will now know that it is something to remember.
I am also sure that I will give you some other opportunities to help me in a close future

Actually, stop helping me because I can't add to your reputation for the moment

You must spread some Reputation around before giving it to ajryan88 again.

[ajryan88]

Hahaha well that's what the forum is here for