Using ExecuteExcel4Macro() opens the file browser

[elprey]

Hi everyone,

I'm a bit new to VBA. I'm attempting to create an excel 2010 VBA script which grabs a the value of a single cell from a closed existing workbook.

[LIST]
[*][COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]

[*][COLOR=#333333] [/COLOR]

[*][COLOR=#333333]    Application.ScreenUpdating = False[/COLOR]

[*][COLOR=#333333] [/COLOR]

[*][COLOR=#333333]    p = "U:\path"[/COLOR]

[*][COLOR=#333333]    f = "File Name.xlsm"[/COLOR]

[*][COLOR=#333333]    s = "Sheet Name"[/COLOR]

[*][COLOR=#333333]    a = "A1"[/COLOR]

[*][COLOR=#333333]   [/COLOR]

[*][COLOR=#333333]    ret = "'" & p & "[" & f & "]" & s & "'!" & Range(a).Address(True, True, -4150)[/COLOR]

[*][COLOR=#333333]   [/COLOR]

[*][COLOR=#333333]    CellValue = ExecuteExcel4Macro(ret)[/COLOR]

[*][COLOR=#333333]    Range("A1").Value = CellValue[/COLOR]

[*][COLOR=#333333]   [/COLOR]

[*][COLOR=#333333]End Sub[/COLOR]
[/LIST]

The code works just as expected other than the fact that when I run it, it opens up the file browser. From there, I have found that if I select a file, it will read the cell value for that file. If I hit cancel, it will read the cell value of the variable I've selected.

I'd like this script to read many files automatically, so it cannot be opening the file browser for each cell value it attempts to get. Has anyone see this problem before, and do you know any solutions?

 

[elprey]

Thanks anyway, found the solution.

 

[Netsurfer]

Hello,
I'm stuck with the same problem of file browser opening unexpectedly.
Could you please share your solution
BR/

 

[jmtces]

Hello,
I'm stuck with the same problem of file browser opening unexpectedly.
Could you please share your solution
BR/

This answer is from a similar question on StackOverflow
answered Feb 13 '12 at 14:07


Siddharth Rout


The open file dialog box is being showed to you because the path variable "p" doesn't have a "" in the end

 

[jmtces]

The slash is missing...
The open file dialog box is being showed to you because the path variable "p" doesn't have a "" in the end :wink: