VBS Script to read in file name and path from folder structure, run convert to xls

Forums › Microsoft Windows › VBS Script to read in file name and path from folder structure, run convert to xls

mmlakeman

106 posts

Master Geek

#109538 21-Sep-2012 15:48

Hi - I've got a large number of Lotus 123 Wk4 files (in lots of folders) and need to convert them all to XLS files. I found this on the Scripting Centre-

' Script name: XLS_SaveAsSYLK.vbs
' Created on: 07/07/2011
' Author: Dennis Hemken
' Purpose: Opens an existing Microsoft Excel Document
' and then saves the file in SYLK format.

Dim AppExcel
Dim OpenWorkbook
Const xlS = 1
Const xlSYLK = 2
Set AppExcel = CreateObject("Excel.Application")
AppExcel.Visible = False
Set OpenWorkbook = AppExcel.Workbooks.Open("C:\temp\Temp.wk4")
OpenWorkbook.SaveAs "C:\temp\newfile", xls
OpenWorkbook.Close
Set OpenWorkbook = Nothing
AppExcel.Quit
Set AppExcel = Nothing

Which works well against 1 specified file name - How do i loop through all the files in the folder structure, converting them and leaving the converted file in the same folder as the source wk4 file?

Thanks for any pointers! (-:

amanzi

Amanzi
1292 posts

Uber Geek

ID Verified

Trusted

Lifetime subscriber

#689525 21-Sep-2012 15:56

You'd modify that script so that the existing logic is moved into a function, then you'd loop through the file system, calling that function every time you find a file wk4 file.

freitasm

BDFL - Memuneh
79250 posts

Uber Geek

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#689530 21-Sep-2012 16:15

What about something like (fix the syntax but you get the idea)

  
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")  
Set objFolder = objFSO.GetFolder("path")  
  
For Each objSubfolder In objFolder.SubFolders  
   For Each objFile in For objSubfolder.Files 
      ' Repeat logic  
   Next 
Next  
  
Set objItem = Nothing  
Set objSubfolder = Nothing 
Set objFolder = Nothing  
Set objFSO = Nothing

This code will take some time to run, depending on how Excel perform on each file...

mmlakeman

106 posts

Master Geek

#689544 21-Sep-2012 16:54

thanks guys - that has put me on the right track - once I get the syntax tidied up I will post for future ref (-:

mmlakeman

106 posts

Master Geek

#689735 21-Sep-2012 22:53

Got there in the end (-;

' Script to convert all files in source folder and subfolders to XLS files from
' Lotus 123 WK4 files.
' Requires Excel 2003 (NOT Excel 2007 or2010)
' Files are converted but keep orginal name but with XLS appended.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

strFolderName = "c:\lotus"

Set colSubfolders = objWMIService.ExecQuery _
("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
& "Where AssocClass = Win32_Subdirectory " _
& "ResultRole = PartComponent")

Wscript.Echo strFolderName

arrFolderPath = Split(strFolderName, "\")
strNewPath = ""
For i = 1 to Ubound(arrFolderPath)
strNewPath = strNewPath & "\\" & arrFolderPath(i)
Next
strPath = strNewPath & "\\"

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile where Path = '" & strPath & "'")

For Each objFile in colFiles
If objFile.Extension = "wk4" or objFile.Extension = "WK4" Then
Wscript.Echo objFile.Name
ExcelConvert objfile.Name
End If

Next

For Each objFolder in colSubfolders
GetSubFolders strFolderName
Next

Sub GetSubFolders(strFolderName)
Set colSubfolders2 = objWMIService.ExecQuery _
("Associators of {Win32_Directory.Name='" & strFolderName & "'} " _
& "Where AssocClass = Win32_Subdirectory " _
& "ResultRole = PartComponent")

For Each objFolder2 in colSubfolders2
strFolderName = objFolder2.Name
Wscript.Echo
Wscript.Echo objFolder2.Name
arrFolderPath = Split(strFolderName, "\")
strNewPath = ""
For i = 1 to Ubound(arrFolderPath)
strNewPath = strNewPath & "\\" & arrFolderPath(i)
Next
strPath = strNewPath & "\\"

Set colFiles = objWMIService.ExecQuery _
("Select * from CIM_DataFile where Path = '" & strPath & "'")

For Each objFile in colFiles
If objFile.Extension = "wk4" or objFile.Extension = "WK4" Then
Wscript.Echo objFile.Name
End If
Next

GetSubFolders strFolderName
Next
End Sub

Sub ExcelConvert(SourceFile)
Dim AppExcel
Dim OpenWorkbook
Const xlS = 1

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = False

Set OpenWorkbook = AppExcel.Workbooks.Open(SourceFile)
DestFile = Sourcefile & ".xls"
OpenWorkbook.SaveAs DestFile, xls

OpenWorkbook.Close
Set OpenWorkbook = Nothing

AppExcel.Quit
Set AppExcel = Nothing
End Sub