Using paths and sheet names with spaces in vba.

[jproffer]

Hi all,

I'm trying to use this argument (arg):

'T:\SUPERVISOR REPORTS\2018\[May 2018.xlsx]10-D!R7C2

like this:

GetValue = ExecuteExcel4Macro(arg)

BTW, arg is created like this:

arg = "'" & path & "[" & file & "]" & sheet & "!" & Range(ref).Range("A1").Address(, , xlR1C1)

(it was found online. I'm sure many of you have seen that exact line before )

It errors out. I think it's because of the space in the path "SUPERVISOR REPORTS" and possibly also the space in the file name "May 2018.xlsx". Am I correct about that? And how would I modify the variable creation line to account for possible spaces in path and filename?

Thanks in advance for any help I can get.

 

[hotabae]

Not really my wheelhouse here, but don't we usually use quotes on either sides of paths with spaces? To keep it all as one string?

I.E -

arg = [COLOR=#0000ff]""""[/COLOR] & "'" & path & "[" & file & "]" & sheet & "!" & Range(ref).Range("A1").Address(, , xlR1C1) & [COLOR=#0000ff]""""[/COLOR]

(Hoping this doesn't make me look stupid :P)

 

[jproffer]

Not really my wheelhouse here, but don't we usually use quotes on either sides of paths with spaces? To keep it all as one string?

I.E -

arg = [COLOR=#0000ff]""""[/COLOR] & "'" & path & "[" & file & "]" & sheet & "!" & Range(ref).Range("A1").Address(, , xlR1C1) & [COLOR=#0000ff]""""[/COLOR]

(Hoping this doesn't make me look stupid :P)

Could be...and nothing would make you look stupid, as far as I'm concerned. I'm the one who didn't know how to even begin making it work lol.

I'll try it and report back.

 

[jproffer]

Ok, with that change, it ran through (or I stepped it through) and it didn't error out, but then "GetValue" = the path/file/sheet/range name instead of whatever value is in that range.

So getting through without bugging out is progress, but unfortunately it's not returning the cell contents.

 

[hotabae]

I created a test version of this, and ExecuteExcel4Macro gave me the value I was looking for with this arg value:

arg = "'C:\Users\*******\Documents\test folder\[Book1.xlsm]Sheet1'!R1C1"

Maybe compare the structure of your arg to mine and see if you notice anything?

Edit: Apostrophe after Sheet name maybe?

 

[jproffer]

Ok, this is what that produced:

'T:\SUPERVISOR REPORTS\2018\[May 2018.xlsx]10-D!'R7C2

and no luck.

 

[hotabae]

It's totes mcGoats the apostrophe:

Flip the exclamation mark and the apostrophe.

See the difference between args here:

arg = "'" & path & "[" & file & "]" & sheet & "!" & Range(ref).Range("A1").Address(, , xlR1C1)

arg = "'" & path & "[" & file & "]" & sheet &[COLOR=#ff0000] "'!"[/COLOR] & Range(ref).Range("A1").Address(, , xlR1C1)

Edit: Hope you see this in time; Make sure not to use the """" stuff I recommended earlier

 

[jproffer]

Ok, I think we got it. I did a little messing around with a formula linking to the other workbook and finally stopped on this:

arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)

with the extra apostrophe before the "!" after the sheet name. I'm not sure if I somehow didn't copy that when I found it online, or removed it somehow...or if what I found didn't use that because it didn't have spaces. At any rate, it works now (as far as I can see so far). Thank you for your help!

EDIT: saw your last post after submitting this. Looks like you had it figured out all along. Thanks again

 

[hotabae]

No problem!

I might actually use the ExecuteExcel4Macro functionality in one of my projects now, so thanks yourself for showing me

Also... just want to note that it's frustrating how often code can be taken down by missing punctuation (I'm looking at you Java semicolons and brackets )

 

[jproffer]

Yes it is, lol. Or if you import something from a website and you're looking for a " " space, and it turns out they used non-breaking spaces instead. Many times I've been known to yell at my computer screen... "Don't tell me there's no space in that string, I'm looking RIGHT AT THE F-IN THING...see...SPACE...right THEREEEEEE"

I'm sure I asked here, or read another thread here about the non-breaking spaces used by websites. Can't remember the 2 CHAR numbers for the 2 diff types of spaces, but that was a life-saver.

But, yes, I feel your pain. Any little thing can drop a line of code flat on it's face . But that's a good thing, really...if it's not going to do what I want it to do, I'd rather it didn't assume it knew what I "meant"...I'd prefer it just throw an error and let me take care of it.