0

I have a VBScript that opens any XML files in one folder and saves them as excel files to another folder.

it seems to work, although the resulting excel files have no XLS extension, any ideas why ?

what i would like the script to then do is delete all the files from the sourcefolder. how would i do this?

Dim xlApp, xlWkb, SourceFolder,TargetFolder,file
Set xlApp = CreateObject("excel.application")
set fs = CreateObject("Scripting.FileSystemObject")
Const xlNormal=1
SourceFolder="c:\test\xml"
TargetFolder="c:\test\xls"

'Hide Excel
xlApp.Visible = false

'Process each file in SourceFolder
for each file in fs.GetFolder(SourceFolder).files
'Open file in SourceFolder
Set xlWkb = xlApp.Workbooks.Open(file)
'Get Filename
BaseName= fs.getbasename(file)
'Concatenate full path. Extension will be automatically added by Excel
FullTargetPath=TargetFolder & "\" & BaseName
'Save as XLS file into TargetFolder
xlWkb.SaveAs FullTargetPath, xlNormal
'Close file
xlWkb.close
next

Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing
chenks
  • 129
  • 1
  • 4
  • 16
  • Have you already tried anything yourself to delete the files? It's not that this feature is hidden or undocumented. – Tomalak Oct 09 '13 at 11:14
  • i'm unfamiliar with the process, hence why i'm asking for advice. i didn't write this script, it was already in place, and i'm being asked to amend it. – chenks Oct 09 '13 at 11:16
  • 1
    Yes, but the purpose of this website is not to find someone who does your work. Please do a *minimum* of research and try to fix this yourself. Code samples of how to use VBScript to delete files are literally all over the internet, you just have not even tried so far. – Tomalak Oct 09 '13 at 11:22
  • thanks for your constructive input. you haven't commented on the other question i asked regarding the missing XLS file extension. – chenks Oct 09 '13 at 11:25
  • 1
    OK i added fs.DeleteFile("C:\test\xml\*.xml") to the script which appears to do it, whether that is the most efficient way of doing it, i don't know. – chenks Oct 09 '13 at 11:29
  • There's no need to get snippy. That you found out how to do it in 8 minutes only proves my point. I'm not sure what you mean by "most efficient". Are you looking for a way to delete the files or for a way to delete the files *as fast as possible*? Efficiency is completely besides the point in your scenario. ;) In any case, that's the standard way to delete files in VBScript, it's absolutely fine and it is as fast as it will get. – Tomalak Oct 09 '13 at 11:49
  • As to your second question: The files do not have an `xls` extension because you don't give them one. Excel saves them with precisely the file name you pass in (`FullTargetPath`). Just append the desired extension. (`FullTargetPath & ".xls"`). That an extension is appended automatically when you manually save a file in the program is a feature of Excel's UI. The `.SaveAs()` API function just takes whatever you give it. File format is controlled through a [respective parameter](http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.saveas.aspx), not through the extension. – Tomalak Oct 09 '13 at 11:51

1 Answers1

0

Files gets no extension because you are not giving it.

Output file is 

TargetFolder & "\" & fs.GetBaseName( file )

GetBaseName doesn't include extension. Just add the extension 

TargetFolder & "\" & fs.GetBaseName( file ) & ".xls"
MC ND
  • 69,615
  • 8
  • 84
  • 126