Retrieving folder list from a directpry

[Guest]

Hi,

I would likle to get the count and list of the folders in a directory using
VBA given the path of the directory....Also I would like to enumerate each
folder and export all the excel files to a corresponding tables in access 97
database based on the folder in which the file is. Basically I am trying to
automate the process of exporting a bunch of excel files . How can I do this.

Thanks,
Jyothsna.

 

[Allen Browne]

See:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

The code in the article lists the files.
Use TransferSpreadsheet to export to Excel.

 

[Alp Bekisoglu]

Hi Allen,

Sorry for jumping in like this but I am trying to use the code and getting:
Compile Error: Method or data member not found
pointing to: lst.AddItem

When I check by typing in a new line as lst. the options do not include
AddItem!

Its an A2K db, references to VBA, MS A9 OL, DAO3.6, OLE Automation

Any hope for me to fix this?

Thanks in advance,

Alp

 

[Douglas J. Steele]

From the site:

"Output can be listed to the immediate window, or (in Access 2002 or later)
added to a list box."

The AddItem method for list boxes wasn't added until Access 2002.

If you haven't got too many folders, try changing:

For Each varItem In colDirList
lst.AddItem varItem
Next

to

Dim strList As String

For Each varItem In colDirList
strList = strList & varItem & ";"
Next
If Len(strList) > 0 Then
strList = Left$(strList, Len(strList) - 1)
lst.RowSourceType = "Value List"
lst.RowSource = strList
End If

 

[Alp Bekisoglu]

Thanks Doug,

Actually I was only trying in the immediate window (after commenting the
part you suggested to change) but still couldn't get to anywhere. But the
code change made the difference and now it works in the immediate window.
I'm yet to try the listbox part.

Thanks a lot again.

Have a great Sunday!

Alp