Cannot access named Range contained value after Workbook.open

[med123]

Hello everyone,

I am trying to execute a macro from another workbook after opening it with Workbook.open.
The macro works fine when I execute it after opening the workbook manually but whenever I open it from another workbook, I become the following error:

"VBA Runtime Error 1004 “Application-defined or Object-defined error" when I try to Access a named range.

[CODE]
dim srcwb as Workbook, str as String
Set srcwb = Workbooks.Open(linkToWorkbook)

str = srcwb.Sheets(1).Range(rngName).value 'error 1004
[/CODE]

Could someone give me some hints for the issue?

Thank you!

 

[daverunt]

Does it work if you remove 'Dim str as String'?

 

[med123]

Hello daverunt,
thank you for the reply!
Sorry I am afraid that I didn’t explain myself very well
In fact I have two macros say wb1.macro and wb2.macro in two different workbooks say wb1 and wb2
wb1.macro opens wb2 and starts wb2.macro
After opening wb1 I get an error whenever I try to access a named range from wb2
The thing is that the wb2.macro works just fine when I open wb2 manually (per double click)
So I thought that the problem resides in the wb.open function but I didn't find a way to modify it

Here again more complete code snippets

'[FONT=Arial][SIZE=3]Wb1[/SIZE][/FONT]
[COLOR=#000000][FONT=Arial]sub macro()
'//

        Dim wb1 As Workbook, wb2 As Workbook, [COLOR=#000000][FONT=Arial]s as string[/FONT][/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]        set wb2 = open (path,true,true)[/FONT][/COLOR]
 
[COLOR=#000000][FONT=Arial]         s = wb2.Sheets(1).Range(name).value  '[/FONT][/COLOR][/FONT][/COLOR][COLOR=#FF0000][FONT=Arial][FONT=Arial] Error 1004[/FONT][/FONT][/COLOR][COLOR=#000000][FONT=Arial][COLOR=#000000][FONT=Arial][/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]'////[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]end Sub[/FONT][/COLOR]

[COLOR=#000000][FONT=Arial]'Wb2[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]sub macro()
[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]   s = thisworkbook.Sheets(1).Range(name).value ‘[/FONT][/COLOR][COLOR=#FF0000][FONT=Arial]Error 1004 but works fine when opening the workbook manually (double click) [/FONT][/COLOR][COLOR=#000000][FONT=Arial][/FONT][/COLOR]

 
[COLOR=#000000][FONT=Arial]'//[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]end Sub[/FONT][/COLOR]

 

[daverunt]

Hi,

yep, still confusing. I get the impression wb2 doesn't open and that is the issue?
Also where is any code to call the macro in the opened workbook?
Can you post the full code and you may get faster help.

See if this method works for you to open wb2 and reference range.

Sub macro_open()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim s As String

FNme = "wb2.xlsm"
Path = ThisWorkbook.Path & "\" & FNme 'Both workbooks on Desktop to test hence using ThisWorkbook.Path otherwise change for desired


        Workbooks.Open (Path)
        
        s = ActiveWorkbook.Names("Test").RefersToRange
        

End Sub

 

[med123]

Hello again,

well it does open and I can access the range value via its address

See the following code

'WB1
Sub macro_open()
Dim wb1 as Workbook
Dim wb2 as Workbook
Dim s as String

FNme = "wb2.xlsm"
Path = ThisWorkbook.PAth & "\" & FNme
Set wb2 = Workbooks.Open(Path)

 s = wb2.Names("name").RefersToRange ' Error 1004
 s = wb2.Sheets(1).Range("name").Value ' Error 1004
 s = wb2.sheets(1).Range("G7").Value 'works fine
Application.Run("'" & wb2.Name & "'!macro_open")

wb.close(False)

End Sub

'WB2

Sub macro_open()
[FONT=arial]
Dim s As String

'optimization Options
'Also tried to turn these options off but it didn't help
Application.Calculation = xlCalculationManual 
Application.ScreenUpdating = False[/FONT]
 
[LEFT][FONT=arial][COLOR=#222222] s = [COLOR=#222222]ThisWorkbook[/COLOR].Names("name").RefersToRange ' Error 1004[/COLOR]
[COLOR=#222222] s = [COLOR=#222222]ThisWorkbook[/COLOR].Sheets(1).Range("name").Value ' Error 1004 [/COLOR]
[COLOR=#222222] s = [COLOR=#222222]ThisWorkbook[/COLOR].sheets(1).Range("G7").Value 'works fine[/COLOR][/FONT][/LEFT]

End Sub

Thank you!

 

[daverunt]

Glad you got it to work.

 

[med123]

No, I still have an issue because the named range are not accessible.
In fact the second macro is inside multiple Workbooks (over 300), that I should start and it would be not very easy to modify the Code inside of each document.
And besides. as I mentioned in my first post, the macro works fine when I open the workbook manually.

I hope you could help this time!

 

[daverunt]

The code works for me on all 3 methods for getting 's' as per your 09:51 post.

The only time it fails with error 1004 is if there is no range called "name" in wb2.

Is name a variable or a string. It looks like a variable in the initial posts?
If a variable use it without quotes.

 

[med123]

Hello,

it is a string..
I noticed also that I can access the named range per index like this:

s = wb2.Names.item(idx).RefersToRange 'here is idx = 12

The named range reference are being correctly given, though.
I think that the problem resides somewhere else...

 

[daverunt]

Sorry. I'm out of ideas especially as the 3 ways of referencing are all working for me.