Convert XML file to Excel 2003 XLS file

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Convert XML file to Excel 2003 XLS file

Logged in as: Guest

arrSession:exec spGetSession 2,2,66035

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

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Convert XML file to Excel 2003 XLS file
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Convert XML file to Excel 2003 XLS file - 11/5/2008 12:58:58 PM

FZDA

Posts: 2
Score: 0
Joined: 10/22/2008
Status: offline

Hi Guys,

I have searched countless forums and have an interesting one (maybe easy for some). I have the following code that opens an XML file in a specific directory and then saves it as an excel 2003 file. What the script does is it searches the directory for all files and loops through them that have the word Test in the filename. Once it finds it, I want it to be opened (as it is in XML) and save it again as the same file name but as a normal excel 2003 workbook. The xml file has been saved with a .xls extension.

Option Explicit
Dim sResults
'DDC_FileInit
'=========================================================
Function DDC_FileInit()
  Dim sFile, Cont, sSrcFile, sDstFile, sDstFile2, FSO, sSrcPath, sDstPath, sDstPath2, sSrcName, objFolder, colFiles, objFile
  Dim strSearch, colMatches, objRegEx, strNewName, errResult, colFileList, ObjWMIService, strComputer
  Dim xl As New Excel.Application, WB As Excel.Workbook
   Set FSO = CreateObject("Scripting.FileSystemObject")
   Cont = MsgBox("Continue to Copy All Files?" & Chr(13) & Chr(13) & _
       "YES - Copy files to C: & G: Drive" & Chr(13) & Chr(13) & _
       "NO - Stop the process", vbYesNo, "Output File Copying Process")
   If Cont = vbNo Then 'Exit
       Exit Function
   Else ' all Drives
       sSrcPath = "C:\DailyFiles\Extracted\"
       sDstPath = "C:\Reports\Output\"
       sDstPath2 = "G:\Direct & Internet\Files\"
       
       Set objFolder = FSO.GetFolder(sSrcPath)
   
       Set colFiles = objFolder.Files
       For Each objFile In colFiles
               strSearch = objFile.Name
           
           Set objRegEx = CreateObject("VBScript.RegExp")
           objRegEx.Global = True
           objRegEx.Pattern = "Test"
   
           Set colMatches = objRegEx.Execute(strSearch)
   
           If colMatches.Count > 0 Then
               'this is where you do the dirty work
   
               xl.Visible = True
               sSrcFile = sSrcPath & strSearch
               sDstFile = sDstPath & strSearch
               sDstFile2 = sDstPath2 & strSearch
               MsgBox sSrcFile
               
               Set WB = xl.Workbooks.Open(sSrcFile)
               
               WB.SaveAs sSrcFile, FileFormat:=-4143 ' <- 2003 version
               WB.Close
               xl.Quit
               goFileCopy "FZ", sSrcFile, sDstFile
               goFileCopy "FZ", sSrcFile, sDstFile2
           End If
       Next
       MsgBox "Completed", vbInformation, "Process Complete"
   End If

End Function

When I run this code in MS Excel VBA it works fine but when I run it as a VBS file it has an error. The error specifies the following details :
Line 51, Char 58, Error: Expected Statement, Code: 800A0400, Source: Microsoft Compilation Error.

I have determined that the error is about this line and at the point of File being saved in another format.

I have tried xlNormal, xlWorkbookNormal, xlExcell11, -4143 but the script file can't understand the statement.

Does anyone have any ideas what the issue is?

Thanks in advance

Post #: 1

RE: Convert XML file to Excel 2003 XLS file - 11/5/2008 2:37:11 PM

dm_4ever

Posts: 2728
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

Have you tried

ObjWorkbooks.SaveAs sSrcFile, -4143

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to FZDA)