VBScript: Create files with folder structure from Excel

Question

I have cobbled together the following VBS file to read an Excel source file and create files with a name based on Excel column A and content based on column B (concatenated). This all works...

Dim xlsFile
Dim objExcel
Dim outFile
Dim path

path = "C:\Documents and Settings\Andy\Desktop\SHORTURLs\JSPs"
xlsFile = path & "\urls.xls"
Set objExcel = WScript.CreateObject("Excel.Application")
objExcel.Workbooks.open(xlsFile)
' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

intRow = 2 'Row 1 contains headings

' Here is the loop that cycles through the cells
Do Until objExcel.Cells(intRow,1).Value = ""
    strFile    = objExcel.Cells(intRow, 1).Value
    strDestURL = objExcel.Cells(intRow, 2).Value

    ' -- The heart of the create file script
    'Set objTextFile = objFSO.CreateTextFile("./" & strFile & ".jsp", True)
    outFile = path & "" & strFile & ".jsp"
    Set objTextFile = objFSO.CreateTextFile(outFile, True)

    ' Prep file contents
    sText = "<%" & vbCrLf
    sText = sText & "//REDIRECT301" & vbCrLf
    sText = sText & "//System.out.println(""<LOGGED> "" + " & strDestURL & ");" & vbCrLf
    sText = sText & "String dest = """ & strDestURL & """;" & vbCrLf
    sText = sText & "response.setStatus(response.SC_MOVED_PERMANENTLY); // response.SC_MOVED_TEMPORARILY [OR] response.SC_MOVED_PERMANENTLY" & vbCrLf
    sText = sText & "response.setHeader(""Location"", dest);" & vbCrLf
    sText = sText & "response.setHeader(""Connection"", ""close"");" & vbCrLf
    sText = sText & "%>"
    ' Write a line.
    objTextFile.Write(sText)

    objTextFile.Close
    intRow = intRow + 1
Loop

objExcel.Quit
WScript.Quit

However, I now need to amend the VBS to create files in a folder structure based on column A (column B is unaffected). Excel Schema:

+----------------------+----------------------+
|       Column A       |       Column B       |
+----------------------+----------------------+
| /folder/filename.jsp | /url/destination.jsp |
+----------------------+----------------------+
| /folder/filename.jsp | /url/destination.jsp |
+----------------------+----------------------+
| /folder/filename.jsp | /url/destination.jsp |
+----------------------+----------------------+

How would I go about creating the folders and the files within those folders? FYI: I believe the folders structure should be no more than 1 deep (i.e. /folder/file.xxx not /folder/folder/file.xxx).

PS. I know response.setHeader() in JSP files is bad practice but sadly, we have been forced to do it this way.

Ekkehard.Horner · Accepted Answer · 2013-02-13 18:09:03Z

5

Use FSO.

.GetParentFolderName() to get the directory from the file spec
.BuildPath() to prepend a common path prefix
.FolderExists() to check whether you need to create the directory
.CreateFolder() if necessary

answered Feb 13, 2013 at 18:09

Ekkehard.Horner

38.8k2 gold badges50 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBScript: Create files with folder structure from Excel

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related