Results of an array function referred to another workbook returns ERROR!

[stakar]

Hi!!
I have a problem with an array reference.
I m using the morefunc addin for excel and i have a array function the "Hsort" in the workbook 1, which sorts horizontally the cells GM4 to GP4 and it works excellent.
The array function is the following : {=HSORT(GM4:GP4;;1)}

Im referring the results to another workbook 2 BUT with the workbook 1 closed so i get the error #NAME?
I have made a vb6 code that i read everything from the close workbook 1 and i have problem ONLY with this array reference
Every other references works ok except this one.
Can someone help me?

Note, that when the workbook 1 is opened everything works ok!

Thanks in advance

 

[stakar]

Anyone???
Please if somebody help me i 'll appreciate!!

 

[RoryA]

I have made a vb6 code that i read everything from the close workbook 1 and i have problem ONLY with this array reference

How precisely are you reading this workbook?

 

[stakar]

How precisely are you reading this workbook?

like this.

http://www.mrexcel.com/forum/showthread.php?t=570921

By the way u help me to fix it !

 

[RoryA]

Strangely, I don't remember every post I make...

Try this:

Sub ResaveAllWBsInFolder()
   Dim appOther          As Excel.Application
   Dim wbkOld            As Excel.Workbook
   Dim PathDat           As String
   Dim BookName_1        As String
   Dim BookName_2        As String
   Dim objAddIn

   BookName_1 = "Main Source.xlsb"
   BookName_2 = "Joker SEK Source.xlsb"
   PathDat = ThisWorkbook.Path & "\"

   Set appOther = New Excel.Application

   With appOther
      .Enableevents = False
      .Visible = False
      .Workbooks.Add
      for each objAddin in .Addins
         if objaddin.installed then
            objaddin.installed = False
            objaddin.installed = True
         End If
       Next objAddin
      Set wbkOld = .Workbooks.Open(PathDat & BookName_1, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = .Workbooks.Open(PathDat & BookName_2, UpdateLinks:=True)
      .Calculate
      wbkOld.Close savechanges:=True

      Set wbkOld = Nothing
      .Enableevents = true
   End With
   appOther.Quit

End Sub

 

[stakar]

I ll try this at afternoon because im at work right now.
I notice the .objAddin.
Is this the problem ? Because the addin is installed in excel and it works for every workbook that it has to read it

 

[RoryA]

When you automate Excel, the addins are not actually loaded. If you toggle the Installed property off and then on, they get loaded.

 

[stakar]

When you automate Excel, the addins are not actually loaded. If you toggle the Installed property off and then on, they get loaded.

Sorry about the other code,

Now at home i realise that the link i sent you was refered to the other workbook and because i was at work i confused!
Sorry about that

This the code that im using to read the other closed workbook.
By the way i put the part of your code with the "objaddin" but it didnt work!!

---------------------------------------------------------------------------
Private Sub Workbook_Open()
Dim BookName As String
Dim PathDat As String
Dim arrLinks, i As Long

Application.ScreenUpdating = False

'update from links without to open the workbook and check if workbook exists

BookName = "Joker Main Source.xlsb"
PathDat = ThisWorkbook.Path & "\" & BookName
arrLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(arrLinks) Then
BookName = "Joker Main Source.xlsb"
PathDat = ThisWorkbook.Path & "\" & BookName
If FileExists(PathDat) = False Then
MsgBox "File doesnt exist " & PathDat, 16
Else
Application.StatusBar = "Updating from: " & BookName
For i = LBound(arrLinks) To UBound(arrLinks)
ActiveWorkbook.UpdateLink arrLinks(i)
Next i
End If
End If

Application.StatusBar = False
End Sub
---------------------------------------------------------------------------

Thanks and im sorry for the mistake
Im expecting your answer

 

[stakar]

Up!!

 

[RoryA]

Did you update the source workbook using the code you linked to before? If so, that would cause the error.