How do i get the shell function to read the values within a variable instead of the variable name in VBA?

user_14184538 0
Viewed 170

The problem relates to a macro I'm trying to implement in Excel, specifically the shell function in the code. What the code does is that it executes chrome.exe opening a PDF file in a specific page of the document, the code is not mine it is from this post:

(Open a PDF from Excel with VBA in Google Chrome on a specific page)

and this is where I have the problem:

Dim arch As String
arch = "file:///C:\Users\user\Downloads\Trabajo.pdf#page=6"

If Not chromePath = "" Then
    Shell ("""C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"" ""arch""")
End If

what i really need it to do is that the shell function opens chrome.exe and go to the path that is stored in the variable arch.

in the code that was posted it works this way:

    Shell ("""C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"" ""file:///C:\Users\user\Downloads\Trabajo.pdf#page=6""")

I turned the PDF path into a variable since it will change on each PC I deliver the folder containing the excel document and the PDF

i have already managed how to get the PDF path to be read an put into the variable arch. I feel a bit embarrassed because this might be a really silly question. Thank you very much in advance.

vba excel variables
1 Answers

Surrounding your variable within quotes turns it into a string literal instead. You can tell just by looking at it within half a second that your variable won't work, because you aren't even concatenating the variable with the string literal (such as "MyString" & MyVar).

Also, you do not need to enclose your Shell argument within parenthesis since you are not using it to return a value, doing so is generally not good practice in VBA.

Anyways, there are a couple of methods I will show you here. First would be your style of surrounding with multiple double-quotes:

Shell """C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"" " & """" & arch & """"

Notice I had to combine the variable to the string with &.

And the easier-to-read using the Chr$() function:

Shell Chr$(34) & "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe " & _
                                                     Chr$(34) & arch & Chr$(34)

Chr$(34) is the character code for a double-quote character. This can make it easier to surround strings that contain actual double-quote characters within them.

Something to remember when using double-quotes:

  1. A single " will open or close a string literal
  2. Double "" will represent an 'Empty' string (x = "")
  3. Now within a string literal, any quotes you want to be part of the string needs to be escaped with another double-quotation character.

Let's look closer at point # 3

MsgBox """This is a string"""
'      ^^                 ^ ^
'      ||_                | |_ This closes (terminates) the string literal
'      |  |               |
'      |  |               |_This one is acting as an escape to the next "
'      |  |
'      |  |_This is escaping the next character
'      |
'      |_This is the start of the string literal

This can be validated because you would be able to change the above string by adding a space after the first " and before the last, such as:

MsgBox " ""This is a string"" "

While this wouldn't work without giving you a syntax error:

MsgBox "" "This is a string" ""

This is because the first opens the string the 2nd is the escape character. But it's escaping nothing but a space (same in reverse is true for the end).

Then you get to using 4 """", as with & """" & arch & """"

& """" & arch & """"
' ^^ ^
' || |_ Ends the string
' ||
' ||_ Escapes the next "
' |
' |_ Starts the string

Now you can visually see why it takes 4 " just to put a single " into a string literal by itself.

user_57817450
Related