Excel Spreadsheet Generator Script

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Excel Spreadsheet Generator Script

Logged in as: Guest

arrSession:exec spGetSession 2,16,48916

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> Post a VBScript >> Excel Spreadsheet Generator Script
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Excel Spreadsheet Generator Script - 6/29/2007 11:54:02 AM

emcoffey3

Posts: 2
Score: 0
Joined: 6/29/2007
Status: offline

This is a VBScript file that I wrote for a friend. My friend runs a karaoke business, and was using Word to make his karaoke books. He wanted something to easily grab all the songs in a certain directory and create a spreadsheet with Artist and Title columns, so he could easily alphabetize by either field and create two books. The script file asks the user to select a directory, reads each file in the directory that is of a valid format, and generates an Excel spreadsheet, placing the data in the first two columns. I did this in about 2 hours one night and he now uses it regularly. I figured I�d pass it along to anyone who was interested.

'********************************************************************************
'* Song List Generation Script                                                                                                                
'* Written by Ed Coffey                                                                                                                         
'* Important Notes: All songs must be in format "<artist> - <song>.<extension>"                           
'* Songs not in that format will be ignored.                    
'* All songs must be in the same folder.  No subfolders!            
'* File generated is called SongList.xls and is located in specified folder.    
'* There must not already be a file called SongList.xls.  It will be deleted!    
'* Enjoy!                                    
'********************************************************************************

'All variables and objects must be declared with the "dim" statement.
Option Explicit

'Create File System Object
dim objFileSystem
set objFileSystem = CreateObject("Scripting.FileSystemObject")

'Instructions for user.
dim strInstructions
strInstructions = "Please copy all song files into the same folder." & vbCrLf & _
   "All song files must take the form: <artist> - <song>.<extension>"

'Display instructions.
MsgBox strInstructions,    0, "Song List Generator"

'Create a shell object.
dim objShell
Set objShell = CreateObject("Shell.Application")

'Create Folder Object.  Browse for folder.
dim objFolder
Set objFolder = objShell.BrowseForFolder(0, "Select a folder:", 0)
If (objFolder is Nothing) Then
   WScript.Quit        'User cancelled.
End If

'Folder item.
dim objFolderItem
Set objFolderItem = objFolder.Self

'Get file path.
dim strFolderName
strFolderName = objFolderItem.Path

'If folder doesn't exist, quit.
If objFileSystem.FolderExists(strFolderName) Then
   set objFolder = objFileSystem.GetFolder(strFolderName)
Else
   MsgBox "Invalid directory!" & vbCrLf & "Quitting..."
   WScript.Quit
End If

'If spreadsheet exists already, delete it!
If objFileSystem.FileExists(strFolderName & "\SongList.xls") Then
   MsgBox "A file named SongList.xls already exists!" & vbCrLf & "Deleting file..."
   objFileSystem.DeleteFile strFolderName & "\SongList.xls", True
End If

'File object for objFolder.Files collection.
dim objFile

'Create Excel object.
dim objExcel
Set objExcel = CreateObject("Excel.Application")

'Create a new workbook.
objExcel.Workbooks.Add

'Bind to worksheet.
dim objSheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Karaoke"

'Add headers and make bold.
objSheet.Cells(1, 1).Value = "Artist Name"
objSheet.Cells(1, 2).Value = "Song Title"
objSheet.Range("A1:B1").Font.Bold = True

'String variables, array variable, and iterator variable.
dim strFileName, strArtistName, strSongName, strSongNameWithExtension, arrElements, i

i=2    'Which row to add the strings to.

'Loop through each file in directory.
for each objFile in objFolder.Files
   strFileName = objFile.Name                'Get filename.
   'If not a valid format, move on to next file!
   If InStr(strFileName, " - ") > 0 and InStr(strFileName, ".") >  0 Then
       arrElements = Split(strFileName, " - ")            'Parse along hyphen.
       strArtistName = arrElements(0)                'Get artist name.
       strSongNameWithExtension = arrElements(1)        'Get song (extension).
       arrElements = Split(strSongNameWithExtension, ".")    'Parse along dot.
       strSongName = arrElements(0)                'Get song (no extension).
       objSheet.Cells(i, 1).Value = strArtistName        'Insert artist.
       objSheet.Cells(i, 2).Value = strSongName        'Insert song.
       i=i+1                            'Increment row.
   End If
next

'Save spreadsheet in same directory.
dim strExcelFile
strExcelFile = strFolderName & "\SongList.xls"
objExcel.ActiveWorkbook.SaveAs strExcelFile
objExcel.ActiveWorkbook.Close

'Quit Excel.
objExcel.Application.Quit
Set objExcel = Nothing

'Display closer message here and we're done.
Wscript.Echo "File generation complete... please open SongList.xls!"

Post #: 1

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> Post a VBScript >> Excel Spreadsheet Generator Script

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts