Convert A String To A "real" String (vba)

    Hi. How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example:


    Code
    Dim my_book_macro As String
my_book_macro = "Book No.1!my_macro"
Application.Run my_book_macro


    This code gives a runtime error 1004, because the Blank and the Dot are "not allowed" in the string here. If the code is changed to the following, it runs fine (sure both times the correct workbook is open when the code runs).


    Code
    my_book_macro = "Book_No1!my_macro"
Application.Run my_book_macro


    Thanks for any suggestion to solve this.
    Dasha.

    Re: Convert A String To A "real" String (vba)


    I think you need brackets when you have spaces in the name, such as

    Code
    Dim my_book_macro As String
my_book_macro = "[Book No.1]!my_macro"
Application.Run my_book_macro

    Re: Convert A String To A "real" String (vba)


    try puttings the name of the file in



    EDIT: how did you manage to save a workbook with a space in front of it?

    Re: Convert A String To A "real" String (vba)


    I have not tried this with your specific example but generally you need single quotes if you have embedded spaces in a workbook or worksheet name, such as


    Code
    Dim my_book_macro As String
my_book_macro = "'Book No.1'!my_macro"
Application.Run my_book_macro


    Zimitry's post also looks correct, but I think you need both.

    Re: Convert A String To A "real" String (vba)


    Hi. Thanks for several suggestions.


    - Neither brackets nor quotes nor CStr work so far.


    Dasha.

    Re: Convert A String To A "real" String (vba)


    Sorry zimitry, the space in front crept in while copying the code, so please forget this. The problem as such remains.

    Re: Convert A String To A "real" String (vba)


    Do you have the file extension?

    Code
    Dim my_book_macro As String
my_book_macro = "[Book No.1.xls]!my_macro"
Application.Run my_book_macro

    Re: Convert A String To A "real" String (vba)


    this works for me? :confused:


    Code
    sub test()


Dim my_book_macro As String
my_book_macro = Cstr(["Book No.1.[B]xls[/B]!my_macro"])
Application.Run my_book_macro


end sub


    if you do have spaces in your excel filename between the start and end wrap ' marks around them like this


    Code
    sub test()


Dim my_book_macro As String
my_book_macro = Cstr(["[COLOR="Red"][B]'[/B][/COLOR]Book No.1.[B]xls[/B][B][I][COLOR="red"]'[/COLOR][/I][/B]!my_macro"])
Application.Run my_book_macro


end sub

    Re: Convert A String To A "real" String (vba)


    try


    Code
    Sub test()
Application.run "'Book No.1.xls'!my_macro"
End Sub


    filippo

    Re: Convert A String To A "real" String (vba)


    Hi everybody and THANKS.


    It needs file extension AND quotes, then it works.


    Now I can even do it with the name of the workbook in a variable, which I was after originally:


    Code
    Book_Name = "Book No.1.xls"
my_book_macro = "'" & Book_Name & "'!my_macro"
Application.Run my_book_macro


    Thanks again.
    Dasha.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login