1

This is my first time using VBs. I used to it to automate Excel macro but I failed due to the spacing between filenames. It works fine if the space was removed. I read this post and used the double quote method but did not work.

This is how I used it objExcel.Application.Run "'"C:\Users\account\Desktop\test Folder\vbsExcel.xlsm"'!test.getValue" but getting

expected end of statement

as error.I think I used it the wrong way.

Example path

objExcel.Application.Run "'C:\Users\account\Desktop\test Folder\vbsExcel.xlsm'!test.getValue"

Path that work - remove space in folder name

objExcel.Application.Run "'"C:\Users\account\Desktop\testFolder\vbsExcel.xlsm"'!test.getValue"

Tried

objExcel.Application.Run "'C:\Users\account\Desktop\test" + " " + "Folder\vbsExcel.xlsm'!test.getValue"

For what I tried, it partially works because the vbs does run the macro. However, the Excel file is being opened and only works when macro is being enabled. (same thing happens if run using Example Path)

My ultimate goal is to run macro in the Excel without opening the excel file using the Example Path. (simply means allowing space in filenames)

Full Script

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\account\Desktop\testFolder\vbsExcel.xlsm'!test.getValue"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
Community
  • 1
  • 1
Max
  • 429
  • 1
  • 8
  • 25
  • 1
    `My ultimate goal is to run macro in the Excel without opening the excel file using the Example Path.` I think that is not supported. You really have to open the workbook to run the macro. – L42 Jan 02 '18 at 01:47
  • @L42 Hi, I was following this [tutorial](https://wellsr.com/vba/2015/excel/run-macro-without-opening-excel-using-vbscript/). It actually works fine if the file/folder name does not contain spaces. However, mine file/folder name have spaces. An workaround I can think of is replace ` ` to `_` or just remove the space but that would be my last choice. – Max Jan 02 '18 at 01:50
  • The tutorial mentioned something about the double quote as well **Keep in mind, if your file name or file path has spaces, you’ll need to surround the path with double quotes.** But I failed to implement it. – Max Jan 02 '18 at 01:52
  • I still think it will somehow open the file. Haven't tested though. If you need to surround it with `"` then I guess it should be like this: `"'""C:\Users\Desktop\test Folder\vbsExcel.xlsm""'!test.getValue"` since you need to enclose the entire path. – L42 Jan 02 '18 at 02:48
  • I was able to test it now, it is working even if you don't enclose it with quotes. – L42 Jan 02 '18 at 03:01
  • @L42 Yes, I had tried your suggestion but it does not behave the desire way. I also found out that if I replace space with `_` it would also open the excel. If without spaces or `_` it works fine. Fine in terms of no need to open then excel. Have you tried without the spaces? – Max Jan 02 '18 at 03:12
  • With and without spaces, it works. Make sure that you have `objExcel.Application.DisplayAlerts = False` on your code. Also I noticed that on the first try, it will still show Excel and ask to enable the macro. On the second run, it will not. But if you open your `Task Manager`, Excel is still open on the background. And try running your `VBS` while the target file is open, it fill prompt the `Open as read-only message` which proves that it is still opening the file but just on the background. Try adding this line `objExcel.Application.Visible = True` before run. – L42 Jan 02 '18 at 03:19
  • @L42 Yes, as running in the background, you're right, but that's fine with me. I will include the VBs script, which is the same as the tutorial except file path. – Max Jan 02 '18 at 05:08
  • Probably easier to open the file, call that Sub and then close it? `Set oWB = Workbooks.Open ""C:\Users\Desktop\test Folder\vbsExcel.xlsm"" : Application.Run "'vbsExcel.xlsm'!test.getValue" : oWS.Close` – PatricK Jan 02 '18 at 06:41
  • @PatricK Sorry mate, I was really new to VBs. I'm assuming your `:` as next line and simply replace mine code with yours. I'm receiving unexpected end of statement at line 1. `Set oWB = Workbooks.Open ""C:\Users\Desktop\test Folder\vbsExcel.xlsm""` – Max Jan 02 '18 at 07:05
  • Yes you are correct with my assumption. `Set oWB = Workbooks.Open(""C:\Users\Desktop\test Folder\vbsExcel.xlsm"") : Application.Run "'vbsExcel.xlsm'!test.getValue" : oWS.Close` – PatricK Jan 02 '18 at 07:30
  • @PatricK New error this time. `Expected ')'` when I clearly placed a ) at the back of line 1... This is the full code, I checked thrice. `Set oWB = Workbooks.Open(""C:\Users\account\Desktop\test Folder\vbs Excel.xlsm"") Application.Run "'vbsExcel.xlsm'!test.getValue" oWB.Close ` And I think you had a typo. Should be `oWB.Close` right? – Max Jan 02 '18 at 07:45
  • How many times do we have to have umpteen variations of this same question? Search for the answer, you’re not the first to make this mistake and you won’t be the last. – user692942 Jan 02 '18 at 08:34
  • Possible duplicate of [VB Script and filename with space](https://stackoverflow.com/questions/2781995/vb-script-and-filename-with-space) – user692942 Jan 02 '18 at 08:36

2 Answers2

1

There's nothing wrong with the spacing. I managed to solved my problem by adding Application.DisplayAlerts = False to my macro.

This will off the Warning message Enabled Macro when running the vbS.

This is from msdn

True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean

Max
  • 429
  • 1
  • 8
  • 25
  • @L42 Yes, I misunderstood and though you was talking about the `Application.DisplayAlerts = False` in the VBs. – Max Jan 19 '18 at 01:50
-1

Reading the page you refer to, try the following options:

.Run """'C:\Users\account\Desktop\testFolder\vbsExcel.xlsm'""" & "!test.getValue"

.Run """C:\Path\Filename""" & "!test.getValue"

.Run """'C:\Path\Filename'!test.getValue"""

They could be what they are talking about with “surround the Path in double quotes”...as vba uses the following:

MsgBox "String" 'Will display: String
MsgBox ""String"" 'Will be in error
MsgBox "" & "String" & "" 'Will display String
MsgBox """String""" 'Will display "String"
MsgBox """"String"""" 'Will be in error
MsgBox """" & "String" & """" 'Will display "String"
Sercho
  • 305
  • 2
  • 9
  • Nice try. First and third option failed to locate file and macro. Second successfully located the file and macro but requires to open excel. – Max Jan 02 '18 at 06:19
  • @Max I don’t think there is a way to run the macro without Excel opening the file. I would recommend adding a workbook.close event in the last line of the Macro in the other file. – Sercho Jan 02 '18 at 06:20
  • Nope. If you try with the code in my question(edit accordingly to your own path and names), you will found out that it actually can be done. Without spaces in the file naming. The macro runs without opening the Excel. I don't know why it is so difficult with spaces in the file naming :( – Max Jan 02 '18 at 06:23
  • @Max ok, I didn’t have time to try your code, but wasn’t aware that was possible. Have you thought about getting in touch with the person who wrote the Tutorial you were following? Also, just a thought, but what about "'" & """Path""" & "Filename'!test.getValue" ?? – Sercho Jan 02 '18 at 06:27
  • Tried to, but no reply so far. Maybe different timezone. I just tried the your forth suggestion, same error unable to locate the file. – Max Jan 02 '18 at 06:31
  • @Max yeah, I thought so...It seems weird that it would not work for spaces in the File path but would otherwise. Give me a chance to try it out. – Sercho Jan 02 '18 at 06:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162313/discussion-between-sercho-and-max). – Sercho Jan 02 '18 at 06:39