How to store large complex data in array

[pawasthi]

Hi All,

I am facing a problem with arrays in VBA. I have below data to store in Array and then has to write this data into another sheet.

Data:

relative path:fdgdfgdfgdgd/
absolute path : \\asfasdfsfdsdf\ssfsdfdsfsdfsdf\ssfgsfsf\05345346536\billing\usgfgdfgd
relative path:gfdgdfgdgdg/
absolute path : \\dfgdgdggd\dgdgdfgdgdp\dfgdfgdgd\34536436\dfgdfgdg\gdfgdgfd
relative dfgdgdfgd/
absolute path : \\dgd\dfgdfgd\dgd\64643634\dfgdfg\ddfdf

The above data in of one cell.

[mikerickson]

An example workbook with before and after would help us understand the problem.

Also, what have you tried and what problem(s) are you having with that approach?

[pawasthi]

Code:

sub test()

soValue8 = GetInfoFromClosedFile("XYZ", "Source.xlsx", "Sheet1", A2)

sourceData(1) = soValue8



Cells(5, 3).Formula = sourceData(1)


End sub


Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

[pawasthi]

attached Destination file

[Kenneth Hobs]

Delete the code from your workbook object Questionaire. Insert a Module or add to the workbook object:
[VBA]Sub test()
Dim soValue8 As Variant
Dim sourceData(1) As Variant
soValue8 = GetInfoFromClosedFile(Questionaire.Path, "Source.xlsx", "Sheet1", "B2")
sourceData(1) = soValue8
Questionaire.Worksheets("Sheet1").Range("C5").Value2 = sourceData(1)
End Sub


Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
[/VBA]

[pawasthi]

Hi Kenneth,

I have tried the code and it worked for few data but it again failed for few data. It filled the cell with "#VALUE!"

Failed Data was:

CMC<YYYYMMDD><X>.ele, CMC<YYYYMMDD-1><X>.ele, CMC<YYYYMMDD-2><X>.ele, CMC<YYYYMMDD-3><X>.ele, CMC<YYYYMMDD-4><X>.ele
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD><X>.pap, CMC<YYYYMMDD-1><X>.pap, CMC<YYYYMMDD-2><X>.pap, CMC<YYYYMMDD-3><X>.pap, CMC<YYYYMMDD-4><X>.pap
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD>A<X>.CNT, CMC<YYYYMMDD-1>A<X>.CNT, CMC<YYYYMMDD-2>A<X>.CNT, CMC<YYYYMMDD-3>A<X>.CNT, CMC<YYYYMMDD-4>A<X>.CNT
X: X can be {A, B, C, D, E , F, G, H, I, J}

CMC<YYYYMMDD>.RJN, CMC<YYYYMMDD-1>.RJN, CMC<YYYYMMDD-2>.RJN, CMC<YYYYMMDD-3>.RJN, CMC<YYYYMMDD-4>.RJN

CMC<YYYYMMDD><X>.BNT, CMC<YYYYMMDD-1><X>.BNT, CMC<YYYYMMDD-2><X>.BNT, CMC<YYYYMMDD-3><X>.BNT, CMC<YYYYMMDD-4><X>.BNT
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD><X>.pnt, CMC<YYYYMMDD-1><X>.pnt, CMC<YYYYMMDD-2><X>.pnt, CMC<YYYYMMDD-3><X>.pnt, CMC<YYYYMMDD-4><X>.pnt
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD><X>.smn, CMC<YYYYMMDD-1><X>.smn, CMC<YYYYMMDD-2><X>.smn, CMC<YYYYMMDD-3><X>.smn, CMC<YYYYMMDD-4><X>.smn
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD><X>.mhn, CMC<YYYYMMDD-1><X>.mhn, CMC<YYYYMMDD-2><X>.mhn, CMC<YYYYMMDD-3><X>.mhn, CMC<YYYYMMDD-4><X>.mhn
X: X can be {A, B, C, D, E , F}

CMC<YYYYMMDD><X>.atn, CMC<YYYYMMDD-1><X>.atn, CMC<YYYYMMDD-2><X>.atn, CMC<YYYYMMDD-3><X>.atn, CMC<YYYYMMDD-4><X>.atn
X: X can be {A, B, C, D, E , F}

[Kenneth Hobs]

It worked fine for me. You can post your source file with what fails if you like.