AutoDetect the path of the file?

C

ccl28

Hi,

Anyone can help to auto detect the path of the file in
"=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6" ?

The user will key in the name of the file in C4 but not the path.

I have to click 6 times to locate the path. It is very troublesome.

Any suggestion? or ask the user to key in the path for 1 times?

Thank you. Attached please find the code.



Sub Macro1()
Application.ScreenUpdating = False
Dim i As Long
Dim aryW(1 To 60)
Dim intNoFiles As Integer

intNoFiles = Sheet1.Range("C4").CurrentRegion.Rows.Count - 1

answer = MsgBox("Before you start:" & Chr$(13) _
& "Please make sure the Parts Count File Name is filled in cel
C4." _
& Chr$(13) & Chr$(13) & "Do you want to continue?", vbYesNo
"")
If answer = vbNo Then
End
End If
'Count how many rows in the block by C4


Windows("SummaryLeakFreq").Activate
Sheet1.Select
aryW(1) = Range("C4").Value
aryW(2) = Range("C5").Value
aryW(3) = Range("C6").Value
aryW(4) = Range("C7").Value
aryW(5) = Range("C8").Value
aryW(6) = Range("C9").Value
aryW(7) = Range("C10").Value
aryW(8) = Range("C11").Value
aryW(9) = Range("C12").Value
aryW(10) = Range("C13").Value
aryW(11) = Range("C14").Value
aryW(12) = Range("C15").Value
aryW(13) = Range("C16").Value
aryW(14) = Range("C17").Value
aryW(15) = Range("C18").Value
aryW(16) = Range("C19").Value
aryW(17) = Range("C20").Value
aryW(18) = Range("C21").Value
aryW(19) = Range("C22").Value
aryW(20) = Range("C23").Value
aryW(21) = Range("C24").Value
aryW(22) = Range("C25").Value
aryW(23) = Range("C26").Value
aryW(24) = Range("C27").Value
aryW(25) = Range("C28").Value
aryW(26) = Range("C29").Value
aryW(27) = Range("C30").Value
aryW(28) = Range("C31").Value
aryW(29) = Range("C32").Value
aryW(30) = Range("C33").Value
aryW(31) = Range("C34").Value
aryW(32) = Range("C35").Value
aryW(33) = Range("C36").Value
aryW(34) = Range("C37").Value
aryW(35) = Range("C38").Value
aryW(36) = Range("C39").Value
aryW(37) = Range("C40").Value
aryW(38) = Range("C41").Value
aryW(39) = Range("C42").Value
aryW(40) = Range("C43").Value
aryW(41) = Range("C44").Value
aryW(42) = Range("C45").Value
aryW(44) = Range("C46").Value
aryW(44) = Range("C47").Value
aryW(45) = Range("C48").Value
aryW(46) = Range("C49").Value
aryW(47) = Range("C50").Value
aryW(48) = Range("C51").Value
aryW(49) = Range("C52").Value
aryW(50) = Range("C53").Value
aryW(51) = Range("C54").Value
aryW(52) = Range("C55").Value
aryW(53) = Range("C56").Value
aryW(54) = Range("C57").Value
aryW(55) = Range("C58").Value
aryW(56) = Range("C59").Value
aryW(57) = Range("C60").Value
aryW(58) = Range("C61").Value
aryW(59) = Range("C62").Value
aryW(60) = Range("C63").Value


With Sheets("LeakFrequencySummary")
For j = 1 To intNoFiles
For i = 7 To j + 6

'Failure Cases
.Range("C" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6"
'Freq
.Range("D" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$D$39"
'Pin
.Range("E" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$E$39"
'Small
.Range("F" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$F$39"
'Medium
.Range("G" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$G$39"
'Large
.Range("H" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$H$39"
Next i
Next j

End With
Application.ScreenUpdating = True
End Su
 
N

NickHK

What is the .Value of aryW(i - 6) ?
Just a file name ?

Also for the array, you can make it much simpler:
Dim aryW()
Dim arry As Variant
Dim i As Long

arry = Range("C4:C63").Value
'....
,Note ath added "1" in the array index
Range("C" & i).Formula = "=[" & aryW(i - 6,1) & ".xls]LeakFreq!$B$6"

NickHK

ccl28 said:
Hi,

Anyone can help to auto detect the path of the file in
"=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6" ?

The user will key in the name of the file in C4 but not the path.

I have to click 6 times to locate the path. It is very troublesome.

Any suggestion? or ask the user to key in the path for 1 times?

Thank you. Attached please find the code.



Sub Macro1()
Application.ScreenUpdating = False
Dim i As Long
Dim aryW(1 To 60)
Dim intNoFiles As Integer

intNoFiles = Sheet1.Range("C4").CurrentRegion.Rows.Count - 1

answer = MsgBox("Before you start:" & Chr$(13) _
& "Please make sure the Parts Count File Name is filled in cell
C4." _
& Chr$(13) & Chr$(13) & "Do you want to continue?", vbYesNo,
"")
If answer = vbNo Then
End
End If
'Count how many rows in the block by C4


Windows("SummaryLeakFreq").Activate
Sheet1.Select
aryW(1) = Range("C4").Value
aryW(2) = Range("C5").Value
aryW(3) = Range("C6").Value
aryW(4) = Range("C7").Value
aryW(5) = Range("C8").Value
aryW(6) = Range("C9").Value
aryW(7) = Range("C10").Value
aryW(8) = Range("C11").Value
aryW(9) = Range("C12").Value
aryW(10) = Range("C13").Value
aryW(11) = Range("C14").Value
aryW(12) = Range("C15").Value
aryW(13) = Range("C16").Value
aryW(14) = Range("C17").Value
aryW(15) = Range("C18").Value
aryW(16) = Range("C19").Value
aryW(17) = Range("C20").Value
aryW(18) = Range("C21").Value
aryW(19) = Range("C22").Value
aryW(20) = Range("C23").Value
aryW(21) = Range("C24").Value
aryW(22) = Range("C25").Value
aryW(23) = Range("C26").Value
aryW(24) = Range("C27").Value
aryW(25) = Range("C28").Value
aryW(26) = Range("C29").Value
aryW(27) = Range("C30").Value
aryW(28) = Range("C31").Value
aryW(29) = Range("C32").Value
aryW(30) = Range("C33").Value
aryW(31) = Range("C34").Value
aryW(32) = Range("C35").Value
aryW(33) = Range("C36").Value
aryW(34) = Range("C37").Value
aryW(35) = Range("C38").Value
aryW(36) = Range("C39").Value
aryW(37) = Range("C40").Value
aryW(38) = Range("C41").Value
aryW(39) = Range("C42").Value
aryW(40) = Range("C43").Value
aryW(41) = Range("C44").Value
aryW(42) = Range("C45").Value
aryW(44) = Range("C46").Value
aryW(44) = Range("C47").Value
aryW(45) = Range("C48").Value
aryW(46) = Range("C49").Value
aryW(47) = Range("C50").Value
aryW(48) = Range("C51").Value
aryW(49) = Range("C52").Value
aryW(50) = Range("C53").Value
aryW(51) = Range("C54").Value
aryW(52) = Range("C55").Value
aryW(53) = Range("C56").Value
aryW(54) = Range("C57").Value
aryW(55) = Range("C58").Value
aryW(56) = Range("C59").Value
aryW(57) = Range("C60").Value
aryW(58) = Range("C61").Value
aryW(59) = Range("C62").Value
aryW(60) = Range("C63").Value


With Sheets("LeakFrequencySummary")
For j = 1 To intNoFiles
For i = 7 To j + 6

'Failure Cases
Range("C" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6"
'Freq
Range("D" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$D$39"
'Pin
Range("E" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$E$39"
'Small
Range("F" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$F$39"
'Medium
Range("G" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$G$39"
'Large
Range("H" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$H$39"
Next i
Next j

End With
Application.ScreenUpdating = True
End Sub
Click to expand...
 
C

ccl28

The .Value of aryW(i - 6) is just a file name.

I can not work with the example that you attached.

Error = Subscript out of range

Thank you
 
N

NickHK

So you want to find the path to the file by searching the file system ?

For the error, did you read the note about adding the ",1" to the array
index ?

NickHK
 
C

ccl28

I have copied your sample but still return error.

Yes, I want to auto detect the path of the file key in by user.

Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simplify the Macro of Looping 1
Inaccurate count with code 6
My Never ending ARRAY code problems 18
Code copies ONLY last j from sheet 9
Help needed to writing a macro to put the year in column B of a tableusing Table nomenclature & the 7
Save As and new workbook new name used in existing code 8
Excel VBA 1
Post my named ranges in column A 10

Top