Mario62
08-24-2009, 06:23 AM
Hi,
I'm a newbie... and I need some help.
I'm looking for several days to find a solution. Some are close but not exactely what I am looking for.
I'm using Excel 2007. I have several folder with powerpoint-names. I would love to retrieve all file names (with extension pps and pptx) from a folder on a worksheet. I would love to choose the folder name by clicking in a kind of dialog window. In the column besides the name of the presention i would like to see the full name (including the path), next column the size ans last column the date.
So for example:
Beautiful.pps D:\powerpoint\Beautiful.pps 1,520 7/7/2009
What i found till now - probably on this forum - but i do not know how to adapt it:
Private Sub CommandButton1_Click()
Sheets("powerpoint").Select
wispowerpoint
firstfile = Application.GetOpenFilename("Powerpoint Files (*.pps), *.pps")
If firstfile = False Then Exit Sub
'navigates to target directory and selects a file
mynewstring = firstfile
'next strip off filename to get just the directory name
ConditionIsMet = False
Do Until ConditionIsMet
y = X
On Error GoTo Err
X = Application.WorksheetFunction.Find("\", mynewstring)
mynewstring = Right(mynewstring, Len(mynewstring) - X)
Loop
myFileLen = Len(mynewstring)
directory = Left(firstfile, Len(firstfile) - myFileLen)
myRow = 1
Cells(myRow, 1) = "powerpoint"
Cells(myRow, 5) = "Grootte"
Cells(myRow, 6) = "Dag"
myRow = myRow + 1
'get first file info
mynewfile = Dir(directory, 7)
Cells(myRow, 1) = mynewfile
Cells(myRow, 5) = FileLen(directory & mynewfile) / 1000000
Cells(myRow, 6) = FileDateTime(directory & mynewfile)
'get remaining files' info
Do While mynewfile <> ""
mynewfile = Dir
If mynewfile <> "" And Right(mynewfile, 3) = "pps" Then
myRow = myRow + 1
Cells(myRow, 1) = mynewfile
Cells(myRow, 5) = FileLen(directory & mynewfile) / 1000000
Cells(myRow, 6) = FileDateTime(directory & mynewfile)
End If
Loop
MsgBox "alle namen zijn opgehaald"
Unload namenophalen
Exit Sub
Err:
ConditionIsMet = True
X = 0
Resume Next
End sub
Problem with this solution: I do not have the full path, and second - but that is minor - i have to choose one presentation to retrieve all. It would be more nice to choose a folder name.
Can someone be so kind to help me?
Thx in advance
Mario
I'm a newbie... and I need some help.
I'm looking for several days to find a solution. Some are close but not exactely what I am looking for.
I'm using Excel 2007. I have several folder with powerpoint-names. I would love to retrieve all file names (with extension pps and pptx) from a folder on a worksheet. I would love to choose the folder name by clicking in a kind of dialog window. In the column besides the name of the presention i would like to see the full name (including the path), next column the size ans last column the date.
So for example:
Beautiful.pps D:\powerpoint\Beautiful.pps 1,520 7/7/2009
What i found till now - probably on this forum - but i do not know how to adapt it:
Private Sub CommandButton1_Click()
Sheets("powerpoint").Select
wispowerpoint
firstfile = Application.GetOpenFilename("Powerpoint Files (*.pps), *.pps")
If firstfile = False Then Exit Sub
'navigates to target directory and selects a file
mynewstring = firstfile
'next strip off filename to get just the directory name
ConditionIsMet = False
Do Until ConditionIsMet
y = X
On Error GoTo Err
X = Application.WorksheetFunction.Find("\", mynewstring)
mynewstring = Right(mynewstring, Len(mynewstring) - X)
Loop
myFileLen = Len(mynewstring)
directory = Left(firstfile, Len(firstfile) - myFileLen)
myRow = 1
Cells(myRow, 1) = "powerpoint"
Cells(myRow, 5) = "Grootte"
Cells(myRow, 6) = "Dag"
myRow = myRow + 1
'get first file info
mynewfile = Dir(directory, 7)
Cells(myRow, 1) = mynewfile
Cells(myRow, 5) = FileLen(directory & mynewfile) / 1000000
Cells(myRow, 6) = FileDateTime(directory & mynewfile)
'get remaining files' info
Do While mynewfile <> ""
mynewfile = Dir
If mynewfile <> "" And Right(mynewfile, 3) = "pps" Then
myRow = myRow + 1
Cells(myRow, 1) = mynewfile
Cells(myRow, 5) = FileLen(directory & mynewfile) / 1000000
Cells(myRow, 6) = FileDateTime(directory & mynewfile)
End If
Loop
MsgBox "alle namen zijn opgehaald"
Unload namenophalen
Exit Sub
Err:
ConditionIsMet = True
X = 0
Resume Next
End sub
Problem with this solution: I do not have the full path, and second - but that is minor - i have to choose one presentation to retrieve all. It would be more nice to choose a folder name.
Can someone be so kind to help me?
Thx in advance
Mario