Directory listing manipulation

[txgizmo]

I have an Excel file that has a listing of files from multiple directories that was created by importing a
DOS directory listing. it's format is:

Path

File1
File2
File3

for >50,000 files - all in column A.

Can someone write a macro to copy each path to column B in the appropriate row.

The keystrokes are:

The keystrokes are (starting in A1):
edit copy 'Copy the path
right
down 2 'to the file info
Shift-left-end-down-right 'get the whole section
edit paste 'paste the path info
end down 'go to the bottom of the path info
down 2 'down 2 more
left 'left to get to the next path

where it will be repeated.

Thank you in advance

 

[Bob Phillips]

I'm lost. Apart from moving down across and every which way, what are you
doing? What is the objective, the format of the data?

--

HTH

RP
(remove nothere from the email address if mailing direct)

I have an Excel file that has a listing of files from multiple directories

that was created by importing a

 

[txgizmo]

I'm trying to copy the path to the appropriate row so that I can look up the location of a file after I sort
the file names.

example before sort:

Path 1

File8 Path 1
File3 Path 1
File61 Path 1

Path2

File4 Path 2
File9 Path 2
File33 Path 2

after sort
File3 Path 1
File4 Path 2
File8 Path 1
File9 Path 2
File33 Path 2
File61 Path 1

 

[Bob Phillips]

OK, I understand better now. How do you know what is a path row and what is
a file row?

--

HTH

RP
(remove nothere from the email address if mailing direct)

I'm trying to copy the path to the appropriate row so that I can look up

the location of a file after I sort

the file names.

example before sort:

Path 1

File8 Path 1
File3 Path 1
File61 Path 1

Path2

File4 Path 2
File9 Path 2
File33 Path 2

after sort
File3 Path 1
File4 Path 2
File8 Path 1
File9 Path 2
File33 Path 2
File61 Path 1

 

[txgizmo]

The format is always:

Path
Blank row
files (from 0-many)
totals
blank row

Then this repeats for 275,000 rows
example follows

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blitz

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 73,322 BLITZ.ELM
11/16/1998 10:10p 302 BLITZ.INF
2 File(s) 73,624 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blueprnt

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 30,264 BLUEPRNT.ELM
11/16/1998 10:10p 317 BLUEPRNT.INF
2 File(s) 30,581 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\boldstri

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 33,556 BOLDSTRI.ELM
11/16/1998 10:10p 343 BOLDSTRI.INF
2 File(s) 33,899 bytes

 

[Bob Phillips]

Here's a shot

Sub TidyUp()
Dim cLastrow As Long
Dim i As Long, j As Long
Dim fBlank As Boolean
Dim sDir As String

Range("A1").EntireRow.Insert
cLastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range(Cells(1, 1), Cells(cLastrow, 1))
.AutoFilter Field:=1, _
Criteria1:="=*<DIR>*", _
Operator:=xlOr, _
Criteria2:="=*File(s)*"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Columns("A:A").AutoFilter

cLastrow = Cells(Rows.Count, "A").End(xlUp).Row
fBlank = True
For i = 1 To cLastrow
If fBlank Then
sDir = Right(Cells(i, "A").Value, Len(Cells(i, "A").Value) - 13)
i = i + 1 ' skip the blank
fBlank = False
Else
fBlank = Replace(Replace(Cells(i, "A").Value, " ", ""),
Chr(160), "") = ""
If Not fBlank Then
j = j + 1
Cells(j, "B").Value = Right(Cells(i, "A").Value, Len(Cells(i,
"A").Value) - _
InStrRev(Cells(i, "A").Value, " "))
& _
" " & sDir
End If
End If
Next i
Range("E18").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

The format is always:

Path
Blank row
files (from 0-many)
totals
blank row

Then this repeats for 275,000 rows
example follows

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blitz

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 73,322 BLITZ.ELM
11/16/1998 10:10p 302 BLITZ.INF
2 File(s) 73,624 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\blueprnt

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 30,264 BLUEPRNT.ELM
11/16/1998 10:10p 317 BLUEPRNT.INF
2 File(s) 30,581 bytes

Directory of C:\O2Kstd\PFiles\Common\MSShared\Themes\boldstri

02/13/2002 04:04p <DIR> .
02/13/2002 04:04p <DIR> ..
10/01/1998 03:07p 33,556 BOLDSTRI.ELM
11/16/1998 10:10p 343 BOLDSTRI.INF
2 File(s) 33,899 bytes