| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
AutoDetect the path of the file?
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 -- ccl2 ----------------------------------------------------------------------- ccl28's Profile: http://www.excelforum.com/member.php...fo&userid=3609 View this thread: http://www.excelforum.com/showthread.php?threadid=57064 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoDetect the path of the file?
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" wrote in message ... 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 -- ccl28 ------------------------------------------------------------------------ ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095 View this thread: http://www.excelforum.com/showthread...hreadid=570643 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoDetect the path of the file?
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 -- ccl2 ----------------------------------------------------------------------- ccl28's Profile: http://www.excelforum.com/member.php...fo&userid=3609 View this thread: http://www.excelforum.com/showthread.php?threadid=57064 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoDetect the path of the file?
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 "ccl28" wrote in message ... 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. -- ccl28 ------------------------------------------------------------------------ ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095 View this thread: http://www.excelforum.com/showthread...hreadid=570643 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
AutoDetect the path of the file?
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. -- ccl28 ------------------------------------------------------------------------ ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095 View this thread: http://www.excelforum.com/showthread...hreadid=570643 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| File Path Too Long? Not Anymore! Check out Long Path Tool | Excel Discussion (Misc queries) | |||
| http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
| Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
| Excel updating from XML file - file path specific? | Excel Discussion (Misc queries) | |||
| How set file open path to filepath of file opened with Explorer ? | Excel Programming | |||
![ccl28[_7_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
