VB Code to save as XML Spreadsheet

[Desu Nota from Columbus]

I tried searching google and this website for a code and nothing worked.

I am opening notepad documents in excel and running a macro I recorded, and then I am manually clicking, save as-->xml spreadsheet, using the same file name.

How do you make this happen automatically? I need a code to add to the end of the macro I recorded.

 

[pbornemeier]

See this page:
http://www.rondebruin.nl/saveas.htm

 

[Desu Nota from Columbus]

is there one for 2003?

 

[Andrew Poulsom]

The file format for xml is xlXMLSpreadsheet (value 46).

 

[Desu Nota from Columbus]

So what exactly do I add to the end of my macro? I can't make sense of all the different lines of code from this website. I want it to use the parent file's name, but just change the file type to xml without removing the parent file.

Can someone please demystify this for me?

I dont think this keeps the parent file name:

ActiveWorkbook.SaveAs "C:\ron.xlsm", fileformat:=46

the "C:\ron.xlsm" part needs to say, "use parent file name or use current file name" <----however this is worded with code

 

[pbornemeier]

This should do what you asked for:

If InStrRev(ThisWorkbook.Name, ".") > 0 Then
    ActiveWorkbook.SaveAs Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & ".xml", FileFormat:=46
Else
    MsgBox "Save with a valid filename before saving as .xml"
End If

 

[Desu Nota from Columbus]

First off, thank you for the help but I need one more problem resolved.

When I use this, it asks me to override my worksheet named personal.xls.

I have the macro saved to personal.xls, where should I save the macro instead or how should the code be tweeked?

 

[Andrew Poulsom]

Try changing ThisWorkbook to ActiveWorkbook.

 

[pbornemeier]

Concur with Andrew Poulsom. I only tested my code in the workbook that contained the code and was not consistent about how I referenced that workbook!

When the old code is installed in the personal.xls workbook ThisWorkbook refers to the personal.xls workbook. ActiveWorkbook always refers to the workbook that has focus.

Revised code below.

If InStrRev(ActiveWorkbook.Name, ".") > 0 Then
    ActiveWorkbook.SaveAs Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & ".xml", FileFormat:=46
Else
    MsgBox "Save with a valid filename before saving as .xml"
End If

 

[Desu Nota from Columbus]

Thanks a bunch. It works.

In the future, is there a way to specify the folder it saves to? Right now (and I am completely ok with it) everything is saved to /My Documents.