export list of subdirectories to excel

[mrwonker]

Hi, I came across this script in a old thread which does nearly what i want it to do hoping it can be modified to do what i want but i dont know how the script below will export all .txt files in said directory to excel, I know i can alter the file extension to other file types but is there a way for it to send folder names in said directory to excel, also when dealing with file types is there away of just importing the names without the extension on the end. many thanks for any help. James.

Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i, j
folderpath = "C:\Test"
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFolder = fso.GetFolder(folderpath)
Set objFileList = objFolder.Files

Set xl = CreateObject("Excel.application")
if not fso.folderexists("C:\Forms") then
    fso.CreateFolder("C:\Forms")
end if
if not fso.fileexists("c:\Forms\Test.xls") then 
    xl.application.workbooks.add
    xl.application.save("C:\Forms\Test.xls")
    xl.application.quit
End if
xl.Application.Workbooks.Open "C:\Forms\Test.xls"
xl.Application.Visible = True
set objSheet = xl.ActiveWorkbook.Worksheets(1)

i = 1
j = 1

For Each File In objFileList
    fullpath = folderpath & "\" & file.name
    if fso.getextensionname(fullpath) = "txt" then
        objSheet.Cells(i, j).value = file.name
        i = i + 1
    end if
Next

xl.DisplayAlerts = FALSE
objSheet.saveas("C:\Forms\Test.xls")
xl.application.quit

[abhijit]

Here's a script that will help you list all folders.

Dim objFSO, objFile, objFolder, objFc, objF
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("d:\")
Set objFc = objFolder.SubFolders
 
For Each objF in objFC
    Wscript.Echo "Listing sub folder  " & objF.Name
Next
 
 
Set objFolder = Nothing
Set objFSO = Nothing

[abhijit]

I modified your script and merged it with mine. I hope this is what you want.

Dim objFolder, obFileList, folderpath, fullpath, xl, i, j
folderpath = "C:\Test"
 
Dim objFSO, objFile, objFc, objF
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("d:\")
Set objFc = objFolder.SubFolders
Set xl = CreateObject("Excel.application")
if not objFSO.folderexists("d:\Forms") then
        objFSO.CreateFolder("d:\Forms")
end if
if not objFSO.fileexists("d:\Forms\Test.xls") then 
    xl.application.workbooks.add
    xl.application.save("d:\Forms\Test.xls")
    xl.application.quit
End if
xl.Application.Workbooks.Open "d:\Forms\Test.xls"
xl.Application.Visible = True
set objSheet = xl.ActiveWorkbook.Worksheets(1)
 
i = 1
j = 1
 
For Each objF in objFC
    objSheet.Cells(i, j).value = objF.Name
    i = i + 1
Next
 
xl.DisplayAlerts = FALSE
objSheet.saveas("d:\Forms\Test.xls")
xl.application.quit

[mrwonker]

Hi, thanks for your reply I apreciate your time and trouble in trying to help, I have tried your script but am getting an error message says
line: 11
char: 1
error: expected statement
code: 800A0400
any ideas sorry I dont even have know the basics of programming vbs.
thanks, James.

[abhijit]

Hi, thanks for your reply I apreciate your time and trouble in trying to help, I have tried your script but am getting an error message says
line: 11
char: 1
error: expected statement
code: 800A0400
any ideas sorry I dont even have know the basics of programming vbs.
thanks, James.

Do you have D:\ on your machine and do you have permissions to create a folder on it?

[mrwonker]

d: is my dvd drive I had alreardy changed all the d: in the script to C: my main hard drive and am running as administrator
thanks.

[kdondrea]

d: is my dvd drive I had alreardy changed all the d: in the script to C: my main hard drive and am running as administrator
thanks.

Make sure the Excel spreadsheet exists, also make sure Excel is installed on the computer you are running the script on.