Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sun Nov 18, 2018 8:57 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Extract all Excel File Names from a Directory
PostPosted: Tue Jun 05, 2012 7:41 pm 
Offline
User avatar

Joined: Thu Dec 29, 2011 6:46 pm
Posts: 191
Location: Edmonton, AB, Canada
Any ideas how to find a Excel File.
Maybe a loop that goes through a specific Directory Path and read all the files on that dir and then identify what of those files are ".xls" or ".xlsx"

Well I did it in MS Excel 2003 few years ago and now I want to put it under REALbasic
Here is the code for VBA in MS Excel 2003

Sub GetFileNamesFromPath ()

Dim i as integer
Dim Directory_SOURCE as string

r = 1
Directory_SOURCE = "C:\"

Sheets("SOURCE").Cells(r, 2) = "FileName"

With Application.FileSearch
.NewSearch
.LookIn = Directory_SOURCE
.Filename = "*.xls"
.SearchSubFolders = True
.Execute

For i = 1 To .FoundFiles.Count

Sheets("SOURCE").Cells(r, 1) = i
Sheets("SOURCE").Cells(r, 2) = .FoundFiles(i)

r = r + 1

Next i

End With
End Sub


So it did a wonderful job, does any body recommend something or do I have to start from scratch ?

Regards,
Tomaz

_________________
Regards,
TomazVDaSilva


Clean Code: Robert C. Martin
It is not the language that makes programs appear simple.
It is the programmer that make the language appear simple!


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Tue Jun 05, 2012 10:08 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
loop thru the fileitems within the folderitem pointing to your directory.... check the extension of each item.....


here is some code that I use to find CSS files
f=App.ExecutableFile.Parent.parent.child("Resources")
For i=1 To f.count
If Right(f.Item(i).DisplayName,4)=".css" Then
css_f=css_p.child(f.item(i).DisplayName)
// do something with css_F folderitem
End If
Next i

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Tue Jun 05, 2012 11:50 pm 
Offline
User avatar

Joined: Thu Dec 29, 2011 6:46 pm
Posts: 191
Location: Edmonton, AB, Canada
DaveS, Thank you!

I end up with a workable code, you info regarding Right(source, count) function helped me.
However I had to drop the dot in the Extension string, so intead ".xlsx" I had to use "xlsx"
Because the "Right(<text>, 4)" or I could keep the dot and change count to 5.
But non of then is still a good solution, I could not find a function "FIND" which I could find the location of the dot
and then pass that location to the "Right(<text>, <count>) function.

I had the impression that the Type method would return the Item Type but it return nothing.

See the code below:

Dim f as FolderItem
Dim i as integer
Dim FileExtension As String

ListBox1.ColumnCount=3
ListBox1.ColumnWidths= "10%, 60%,30%"
ListBox1.HasHeading = true

f=GetFolderItem("")

For i=1 to f.Count

// I would like to find the "." position because future application
// which I may want to extract the extension instead

FileExtension = Right(f.Item(i).AbsolutePath,4)

// DisplayName and Name worked as well.
// FileExtension = Right(f.Item(i).DisplayName,4)
// FileExtension = Right(f.Item(i).Name,4)


if FileExtension="xlsx" then

ListBox1.AddRow Str(i)
ListBox1.Cell(ListBox1.LastIndex,1)=f.Item(i).AbsolutePath
ListBox1.Cell(ListBox1.LastIndex,2)=f.Item(i).Type // doesn't return a value

end if

Next


Regards,
Tomaz

_________________
Regards,
TomazVDaSilva


Clean Code: Robert C. Martin
It is not the language that makes programs appear simple.
It is the programmer that make the language appear simple!


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Wed Jun 06, 2012 12:18 am 
Offline
User avatar

Joined: Thu Dec 29, 2011 6:46 pm
Posts: 191
Location: Edmonton, AB, Canada
So instead of Right(<text>, 4) I am suggesting:

DotPosition = Len(f.Item(i).Name) -  InStr(f.Item(i).Name,".") + 1
FileExtension = Right(f.Item(i).Name,DotPosition)


It still have a problems, the file can have multiples "." and the InStr will count the first occurrence, missing the extension.
I still have to create a loop to check how many dot in the text and the position of the first from Right to Left or last from Left to Right.
Then check if the extension found is a valid extension from a list of valid extensions.

Regards,
Tomaz

_________________
Regards,
TomazVDaSilva


Clean Code: Robert C. Martin
It is not the language that makes programs appear simple.
It is the programmer that make the language appear simple!


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Wed Jun 06, 2012 12:27 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
if NthField(name,".",countfields(name,".")="XLS" then


you will need to verify the exact syntax... I typed that off the top of my head.... and I'm 1/2 asleep

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Wed Jun 06, 2012 1:43 pm 
Offline
User avatar

Joined: Thu Dec 29, 2011 6:46 pm
Posts: 191
Location: Edmonton, AB, Canada
DaveS wrote:
if NthField(name,".",countfields(name,".")="XLS" then


you will need to verify the exact syntax... I typed that off the top of my head.... and I'm 1/2 asleep



I will try somenthing when I get home, we probably will end up with a blueprint for a object class FileExtension

_________________
Regards,
TomazVDaSilva


Clean Code: Robert C. Martin
It is not the language that makes programs appear simple.
It is the programmer that make the language appear simple!


Top
 Profile  
Reply with quote  
 Post subject: Re: Extract all Excel File Names from a Directory
PostPosted: Thu Jun 07, 2012 10:35 pm 
Offline
User avatar

Joined: Thu Dec 29, 2011 6:46 pm
Posts: 191
Location: Edmonton, AB, Canada
DaveS wrote:
if NthField(name,".",countfields(name,".")="XLS" then


you will need to verify the exact syntax... I typed that off the top of my head.... and I'm 1/2 asleep



"Because one thing I have to two things"

I am really not happy, with the code I created based on your tip.

1) There are files extensions with 1,2,3,4 and five characters.
2) They those characters are always in the end of the string
3) There may be Files with a wrong name and the last characters that should represent a extension is not a extension at all

We need a function to extract and test if the extension is a valid extension.
However for simplification I will try to simply extract the extension for now.

So my first function is to count how many target characters the target Text has.
Or if parameter bLast = True the function returns the position of the last targeted character.

Example:
noChInAText(".", MyFile.xls, False) returns 1, because the text has one "."
noChInAText(".", MyFile.xls, True) returns 7, because the "."is in the position 7

Function noChInAtext ( sCh, sText, bLast )
Dim noChText As integer //NumberOfCharactersInTheText
Dim noChCounted As integer//NumberOfTargetCharactersCounted
Dim iChPosition As Integer //Character Position
Dim aCharacter() As String

// Lets loop through each character in the string
// Find the number of characters, go through each one
// Increment a counter for each character target found in the string

noChText = Len(sText)
ReDim aCharacter(noChText + 1) // I like to start from 1 not from zero

For iChPosition=1 to noChText

aCharacter(iChPosition) = Mid(sText, iChPosition,1)

If aCharacter(iChPosition) = sCharacter Then
noChCounted=noChCounted + 1

end if

Next iChPosition

If bLast=False Then
Return noChCounted
else
Return PositionLastCharacter
end if

End Function


Now I can know how many and where my character is.
Based on that I can use Left or Right function and get possibly what should be a extension.

If I am looking for a specific extension it is just a matter o check for it, Using a function to check extensions.
xls, doc, txt, pdf, zip, etc..

IsExtension( sExtractedExtension ), will return true or false if valid or not valid respectivaly

_________________
Regards,
TomazVDaSilva


Clean Code: Robert C. Martin
It is not the language that makes programs appear simple.
It is the programmer that make the language appear simple!


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group