Con Can(')t enate (A tough one)

Posted by Joe C on January 22, 2002 1:00 PM

I wrote a macro to create Concatenate statememnts to bring a whole bunch of specified rows from xls files.
It works great except for the fact that some people used (') in their file names.
What can I do???
Is there a way around this??

Posted by Barrie Davidson on January 22, 2002 1:09 PM

i need the macro to run when i open a specific excel file.
Private Sub Workbook_AutoOpen ()
UserForm1.Show
End Sub

Does not work
what am i doing wrong?

Posted by Barrie Davidson on January 22, 2002 1:09 PM

Assuming you are concatenating cells A1 & B1, try this:

=SUBSTITUTE(A1,"'","_")&SUBSTITUTE(B1,"'","_")

Of course, you'll have to change "_" to whatever you want to replace ' with.

Regards,
BarrieBarrie Davidson

Posted by Mark W. on January 22, 2002 2:50 PM

Notice how the apostrophe is handled when
concatenating cell A1 on a worksheet named
Sheet2's...

="cat "&'Sheet2''s'!A1

A double apostrophe replaces the single apostrophe
and the entire sheet name is enclosed by single
apostrophes. Make sure your macro emulates this
behavior.