Populate an array from a closed WB

[vadius]

Hi all,

I am trying to populate a array(40,1) with text data in the first column of a closed book ("CAC40.xls"). I built something fromn some codes I found,, but I have a "mismatch" problem on the line myarray(x, y) = GetData(FilePath, FileName, SheetName, Address)

Has anyone an idea ?

Thanks

Sub GetDataDemo()

      Dim FilePath$, Row&, Column&, Address$
   
      
      'change constants & FilePath below to suit
      '***************************************
      Const FileName$ = "CAC40.xls"
      Const SheetName$ = "Sheet1"
      Const NumRows& = 40
      Const NumColumns& = 1
      FilePath = "H:\Outlook\"
      '***************************************
      
      DoEvents
      Excel.Application.ScreenUpdating = False
      If Dir(FilePath & FileName) = Empty Then
            MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
            Exit Sub
      End If
      
      
      
      Dim myarray(NumRows, NumColumns) As Long
      Dim x As Long
      Dim y As Long
      For x = 1 To NumRows
                For y = 1 To NumColumns
                Address = Cells(x, y).Address
                myarray(x, y) = GetData(FilePath, FileName, SheetName, Address)
                Next y
      Next x
      
      ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Address)
      Dim Data$
      Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
            Range(Address).Range("A1").Address(, , xlR1C1)
      GetData = ExecuteExcel4Macro(Data)
End Function

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Aug37
[COLOR="Navy"]Dim[/COLOR] FilePath$, Row&, Column&, Address$, Ray(1 To 20, 1 To 1)
Const FileName$ = "CAC40.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 20
Const NumColumns& = 1
'[COLOR="Green"][B]Change Filepath to suit[/B][/COLOR]
FilePath = "C:\Documents and Settings\test\Desktop\"
DoEvents
Application.ScreenUpdating = False
[COLOR="Navy"]If[/COLOR] Dir(FilePath & FileName) = Empty [COLOR="Navy"]Then[/COLOR]
MsgBox "The file " & FileName & " was not found", , "File Doesn'[COLOR="Green"][B]t Exist"[/B][/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]For[/COLOR] Row = 1 To NumRows
[COLOR="Navy"]For[/COLOR] Column = 1 To NumColumns
Address = Cells(Row, Column).Address
'[COLOR="Green"][B]Data sent to active sheet for test !!![/B][/COLOR]
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
'[COLOR="Green"][B]Data sent to array[/B][/COLOR]
Ray(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Ray(Row, Column) = IIf(Ray(Row, Column) = 0, "", Ray(Row, Column))
Columns.AutoFit
[COLOR="Navy"]Next[/COLOR] Column
[COLOR="Navy"]Next[/COLOR] Row
Sheets("Sheet3").Range("A1").Resize(20, 1) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private Function GetData(Path, File, Sheet, Address)
[COLOR="Navy"]Dim[/COLOR] Data$
Data = "'[COLOR="Green"][B]" & Path & "[" & File & "]" & Sheet & "'!" & _[/B][/COLOR]
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
[COLOR="Navy"]End[/COLOR] Function

Regards Mick