-
Apr 19th, 2006, 01:44 AM
#1
Thread Starter
New Member
Directory Listing to Excel Spreadsheet
Hey guys,
I have been looking around and have not been able to find a way of doing this.
What I need to do get a list of files of a certain extension (eg *.txt) and output the results to a specific excel spreadsheet..
For example - directory listing of all txt files in C:\Test to excel sheet c:\Forms\Test.xls.
If anyone know how this can be done that would be great.
-
Apr 19th, 2006, 03:46 AM
#2
Member
Re: Directory Listing to Excel Spreadsheet
Could try something like:
VB 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
Edited just to say if you want to search for a different file extension, say gif, then change "txt" to "gif".
Last edited by l12ngo; Apr 19th, 2006 at 04:01 AM.
-
Apr 19th, 2006, 06:38 PM
#3
Thread Starter
New Member
Re: Directory Listing to Excel Spreadsheet
Thanks for that, that worked fine.
Just a quick question is it possible to make append to the next column.
-
Apr 20th, 2006, 02:21 AM
#4
Member
Re: Directory Listing to Excel Spreadsheet
Yes, if you look at the line:
objSheet.Cells(i, j).value = file.name
j is a variable which is set to 1 in my code. i is another variable which is set to one, but is incremented by 1 each time we loop round. So really, what we're saying is
objSheet.Cells(1, 1).value = file.name
for the first iteration of the loop
objSheet.Cells(2, 1).value = file.name
for the second etc etc. Now all this is code is doing is setting the value of the cell to file.name where i is the row number of the cell and j is the column number. If I wanted to set the value of the second column then all i'd need to do is put in another line so my for loop could look something like:
VB Code:
For Each File In objFileList
fullpath = folderpath & "\" & file.name
if fso.getextensionname(fullpath) = "txt" then
objSheet.Cells(i, j).value = file.name
objSheet.Cells(i, 2).value = "Hello"
objSheet.Cells(i,j).Font.Bold = True ' not needed
objSheet.Columns("A:A").ColumnWidth = "20" 'not needed
i = i + 1
end if
Next
.. Hope this explains it. It's just about manipulating the numbers in your loop. You can also format cells and columns to meet your needs. I've thrown in two little examples in the For Loop which will set the first column width to 20 and will bold the cells in the first column. I've commented them as not needed so you can delete those lines if you want to, it's just there to show you how you can do other stuff to your spreadsheet.
-
Apr 20th, 2006, 07:13 PM
#5
Thread Starter
New Member
Re: Directory Listing to Excel Spreadsheet
Again thanks for that it has been a real help.
I added in that other section and ended up with the following bit below, I changed the I to 2 so that I can put a header line for the file descriptions. But is it possible to have the append to the next line or leave a line gap insert the date and then add on the directory listing. I know now I am getting more complex, but is it possible.
Essentially what I am trying to create is a script that will update a list of files, but these will files will change on a daily basis so we need to know what files are missing and keep track of what files are being updated.
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 = 2
j = 1
For Each File In objFileList
fullpath = folderpath & "\" & file.name
if fso.getextensionname(fullpath) = "txt" then
objSheet.Cells(i, j).value = file.name
objSheet.Cells(i, 2).value = "Hello"
objSheet.Cells(i,j).Font.Bold = True ' not needed
objSheet.Columns("A:A").ColumnWidth = "20" 'not needed
i = i + 1
end if
Next
xl.DisplayAlerts = FALSE
objSheet.saveas("C:\Forms\Test.xls")
xl.application.quit
Last edited by Navarre; Apr 20th, 2006 at 10:20 PM.
-
Apr 21st, 2006, 02:29 AM
#6
Member
Re: Directory Listing to Excel Spreadsheet
No worries about things getting more complicated, it's usual to keep adding complexity to scripts as your needs develop so we're good there.
Now, there's good news and bad news. First the bad news, I'm not sure exactly what you're trying to do here and if it's what I'm thinking you're trying to do then we may need to modify our approach. At the moment, we're grabbing the names of all the files in C:\Test and then outputting them to an Excel spreadsheet. Each time we run our script though, it will overwrite Test.xls, so for comparisons sake, this may not be that useful. You mentioned adding a line-break and if you change the line where we increment i (i = i + 1) to i = i + 2 then you'll see what I mean. Now a line break will appear, but because we're just overwriting the cells, in this case the line breaks will have old data in them because all the VBScript does is to overwrite the cells that we tell it to set a new value to. So, if we're trying to compare the same file we're going to run into problems because the xls sheet is overwritten each time we run the VBScript. Hope this makes sense
Now, the good news is that as well as having the ablity to get the name of the file, we can get the date it was last modified which looks like it's vital for your script, so we could run this:
VB Code:
Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i
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
objSheet.Columns("A:A").ColumnWidth = "20"
objSheet.Columns("B:B").ColumnWidth = "15"
objSheet.Cells(i, 1).value = "File Name"
objSheet.Cells(i, 1).Font.Bold = True
objSheet.Cells(i, 2).value = "Date Modified"
objSheet.Cells(i, 2).Font.Bold = True
i = 2
For Each File In objFileList
fullpath = folderpath & "\" & file.name
if fso.getextensionname(fullpath) = "txt" then
objSheet.Cells(i, 1).value = file.name
objSheet.Cells(i, 2).value = file.datelastmodified
i = i + 1
end if
Next
xl.DisplayAlerts = FALSE
objSheet.saveas("C:\Forms\Test.xls")
xl.application.quit
Now, like I mentioned earlier, if you need line breaks inserted between each file listing, you can change i = i + 1 to i = i + 2 or i = i + 3 etc. But, you need to make this decision from the beginning otherwise your spreadsheet will leave old data in the line breaks from the times you previously ran your script.
What I guess I'm confused about is the way you need to compare the files. Are we always going to be dragging the data from C:\Test or will this directory change from day to day? Also, do we need to keep some sort of track on the files, like days they were modified etc? Also, when you say you need to know if a file is missing, what exactly does this mean? Because at the moment all the script is going to do is dump the info about the files in C:\Test to the spreadsheet and overwrite any info in there.
Sorry if I'm missing the obvious, it's often difficult to understand what other people mean on a forum and likewise, if I haven't explained myself properly on some of these points then just let me know and I'll try to clarify
-
Apr 24th, 2006, 12:51 AM
#7
Thread Starter
New Member
Re: Directory Listing to Excel Spreadsheet
Again thanks for the help.
What my goal is to get a list of files that have been uploaded to a server by our stores, these files represent daily sales information that has been successfully copied across to the server.
We also need to keep a log of all files that have been transferred to us in case we have to go back and find missing data.
Essentially I want to keep adding the same information to the file, because once we have the list of files we can know what stores did not transfer their data (for whatever reason and manually got and get it). But also we can keep our history of all files for say a month. Then just rename the file and move it somewhere else so that they can keep monthly records or what ever.
-
Apr 24th, 2006, 03:59 AM
#8
Member
Re: Directory Listing to Excel Spreadsheet
Okay, try this and let me know what we need to do from here. I've commented this script so you know what's going on:
VB Code:
Option Explicit 'thrown this in to keep track of my variables
' declare my variables
Dim fso, objFolder, obFileList, folderpath, fullpath, xl, i
Dim TheDate, LastRow, objFileList, objSheet, File
' Path to where my files are
folderpath = "C:\Test"
' set up some objects
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFolder = fso.GetFolder(folderpath)
Set objFileList = objFolder.Files
Set xl = CreateObject("Excel.application")
' if my destination folder doesn't need creating, i create it
if not fso.folderexists("c:\Forms") then
fso.CreateFolder("C:\Forms")
end if
' create xl file with columns if it's not there
if not fso.fileexists("c:\Forms\Test.xls") then
xl.application.workbooks.add
xl.application.save("c:\Forms\Test.xls")
set objSheet = xl.ActiveWorkbook.Worksheets(1)
i = 1
objSheet.Columns("A:A").ColumnWidth = "20"
objSheet.Columns("B:B").ColumnWidth = "15"
objSheet.Cells(i, 1).value = "File Name"
objSheet.Cells(i, 1).Font.Bold = True
objSheet.Cells(i, 2).value = "Date Modified"
objSheet.Cells(i, 2).Font.Bold = True
xl.DisplayAlerts = FALSE
objSheet.saveas("C:\Forms\Test.xls")
xl.application.quit
End if
' open up the xl sheet
xl.Application.Workbooks.Open "c:\Forms\Test.xls"
xl.Application.Visible = True
set objSheet = xl.ActiveWorkbook.Worksheets(1)
' get todays date and place in a variable
TheDate = Date()
' get the last row in the spreadsheet
LastRow = objSheet.UsedRange.Rows.Count
' just an if statement in case it's the first entry
' i leave a line between updates by incrementing lastrow by 2
' if you want a bigger gap you could change this to 3, 4, 5 etc
if LastRow = 1 then
i = 2
else
i = LastRow + 2
End if
' iterate through the files and write details to spreadsheet
For Each File In objFileList
fullpath = folderpath & "\" & file.name
if fso.getextensionname(fullpath) = "txt" then
objSheet.Cells(i, 1).value = file.name
objSheet.Cells(i, 2).value = file.datelastmodified
i = i + 1
end if
Next
' add a line to say when update to spreadsheet was ran
objSheet.Cells(i,1).Font.ColorIndex = 11
objSheet.Cells(i,1).Value = "Updated " & TheDate
' save & close
xl.DisplayAlerts = FALSE
objSheet.saveas("C:\Forms\Test.xls")
xl.application.quit
This appends your file details rather than replacing them. If you need to add other comments, you can do that by adding in similar code to the bit where I write the line "Updated " & TheDate
-
May 2nd, 2006, 11:18 PM
#9
Thread Starter
New Member
Re: Directory Listing to Excel Spreadsheet
That worked just great, exactly what I wanted to do.
Thanks again.
-
Aug 10th, 2009, 11:17 AM
#10
New Member
Re: Directory Listing to Excel Spreadsheet
Great Script!! works pretty well. though i have made some adjustments, how do i get adjust the script to scan subfolders...
looking forward to hearing from you.
thanks a lot
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|