Where's the Coding Error? XML files to be saved as .xlsx (batch)

[dguenther]

Hi,

Excel 2007 on Windows 7.

I have a couple thousand .xml files.
A.xml,. B.xml, C.xml etc etc

I simply need them to be .xlsx files.

It's as simple as

(1) Open the file
(2)Convert to .xlsx (manually clicking the Convert button under the Save button on the Office button ribbon)
(3) Close

Then I end up with
A.xlsx, B.xlsx, C.xlsx

I would like to have that automated.

I searched threads and tried to pull answers from two other questions to try and solve the problem programatically, but it fails somewhere.

Here are the chunks I am trying out.

Sub XMLXLS()
'
' XMLXLS Macro

    ActiveWorkbook.SaveAs Filename:= _
        "C:directoryformyfiles\a.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub


Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook

MyPath = "C:\hereiswheremyfileslive"
MyFileName = Dir(MyPath & "*.xml")

Do Until MyFileName = ""
    Workbooks.Open MyPath & MyFileName
    Set MyBook = ActiveWorkbook

    Application.Run "XMLXLS"

    MyBook.Save
    MyBook.Close
    MyFileName = Dir
Loop
End Sub

Any and all help would be greatly appreciated.

-dguenther

 

[doofusboy]

Knowing what line your code fails on would be a big help.

Other than that, a brief look at your code suggest you may need a few "\" characters as highligehted in red below:

Sub XMLXLS()
'
' XMLXLS Macro

    ActiveWorkbook.SaveAs Filename:= _
        "C:[B][COLOR=red]\[/COLOR][/B]directoryformyfiles\a.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub


Sub LoopFiles()
Dim MyFileName, MyPath As String
Dim MyBook As Workbook

MyPath = "C:\hereiswheremyfileslive"
MyFileName = Dir(MyPath & "[B][COLOR=red]\[/COLOR][/B]*.xml")

Do Until MyFileName = ""
    Workbooks.Open MyPath & MyFileName
    Set MyBook = ActiveWorkbook

    Application.Run "XMLXLS"

    MyBook.Save
    MyBook.Close
    MyFileName = Dir
Loop
End Sub