Catching special Characters in vba

[aidriiyan]

The Macro I made requires 2 open workbook. 1st workbook is where it will automatically be filled up based on the contents of the 2nd workbook.
The macro will ask the name of the 2nd workbook then after clicking "ok", the 1st workbook will be filled with the data.

the name of the workbook is acquired using this:

wname = InputBox(Prompt:="Workbook name(do not include .xls):", _
Title:="CRYSTAL REPORT", Default:="workbook name here")

then it will be put in this code:

ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],[" & wname & ".xls" & "]Sheet1!R2C3:R60000C11,9,0),"""")"


The Problem: If wname(workbook name) consist of special characters, specifically open/close parenthesis, hypen.... the macro crashes because of error...

I tried using char(40), chr(41) ... but error again.

 

[wigi]

You need to enclose it within single quotes:

    wname = InputBox(Prompt:="Workbook name(do not include .xls):", Title:="CRYSTAL REPORT", Default:="workbook name here")
    ActiveCell.Formula = "=IFERROR(VLOOKUP(RC[-1],'[" & wname & ".xls" & "]Sheet1'!R2C3:R60000C11,9,0),"""")"

 

[aidriiyan]

Its not working sir.
Whenever I input names like,

CV(0-1) , 02-25-2010, 02_25_2010

It doesnt work.

but whenever I input names like

CrystalViewer or any names without symbols it works fine.

 

[wigi]

This works perfectly.

The formula that comes out of it, is in my test setting:

=IFERROR(VLOOKUP(D5,'[CV(0-1).xls]Sheet1'!$C$2:$K$60000,9,0);"")

Wigi

 

[aidriiyan]

Before, even though I type that formula in excel(workbook names with special characters) I have no problem in executing it.

now ^ this is what happens.... That always opens...

 

[aidriiyan]

Anyway, for some reason... It finally works :D

The paranthesis, hypens and other special characters can now be catched.
Thank you very much :D

cool man