pawasthi
05-03-2011, 02:35 PM
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
05-03-2011, 02:49 PM
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
05-03-2011, 05:45 PM
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
05-03-2011, 05:46 PM
attached Destination file
Kenneth Hobs
05-03-2011, 06:26 PM
Delete the code from your workbook object Questionaire. Insert a Module or add to the workbook object:
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
pawasthi
05-07-2011, 05:59 PM
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
05-07-2011, 06:25 PM
It worked fine for me. You can post your source file with what fails if you like.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.