Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice to speed up process
I have a file index, which inputs the paths in various columns for
Autofiltering and makes the files into hyperlinks. It takes a while to process (22 rows takes approx 2 mins to process). There will be more files added as work proceeds. I want to know if there is a way to speed up the process, I've tried Application.Screenupdating = True/False, but it seems to take longer using this. == If oFolder.Files.Count 0 Then For Each oFile In oFolder.Files If oFile.Type = filetype Then sFile = Mid(oFile.Path, 1) 'Start of first value iPos1 = InStr(InStr(InStr(InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") 'End of first value and start of second value iPos2 = InStr(InStr(InStr(InStr(InStr(InStr(InStr(sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") 'End of second value and start of third value iPos3 = InStr(InStr(InStr(InStr(InStr(InStr(InStr(InStr(sF ile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") + 1, sFile, "\") 'End of third value iPos4 = InStrRev(sFile, "\") 'first string Cells(iFile, iPathColA).Value = Mid(sFile, iPos1 + 1, iPos2 - iPos1) Cells(iFile, iPathColB).Value = Mid(sFile, iPos2 + 1, iPos3 - iPos2) Cells(iFile, iPathColC).Value = Mid(sFile, iPos3 + 1, iPos4 - iPos3) Cells(iFile, iFileCol) = oFile.Name ' And a link in the Hyperlink column Cells(iFile, iLinkCol).FormulaR1C1 = "=HYPERLINK(root & RC[-4] & RC[-3]& RC[-2] & RC[-1] ,""HERE"")" iFile = iFile + 1 End If Next oFile End If |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speed up Program Process | Excel Programming | |||
Speed up the process | Charts and Charting in Excel | |||
How to count process running time ( process not finished) | Excel Programming | |||
How to count process running time ( process not finished) | Excel Programming | |||
Question to speed up a process - | Excel Programming |