export list of subdirectories to excel
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.
Code:
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
Re: export list of subdirectories to excel
Here's a script that will help you list all folders.
vb Code:
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
Re: export list of subdirectories to excel
I modified your script and merged it with mine. I hope this is what you want.
vb Code:
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
Re: export list of subdirectories to excel
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.
Re: export list of subdirectories to excel
Quote:
Originally Posted by
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.
Do you have D:\ on your machine and do you have permissions to create a folder on it?
Re: export list of subdirectories to excel
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.
Re: export list of subdirectories to excel
Quote:
Originally Posted by
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.
Make sure the Excel spreadsheet exists, also make sure Excel is installed on the computer you are running the script on.