Change file format using Excel object from Access 2007 vba RRS feed

  • Question

  • My import file is a text file with a filename like "MR.xls". I assume it's actually .csv. I am using A2007 with the Excel object model to read it. Works fine. But I want to copy it to a new sheet in Excel 2007 (.xlsx) format, then manipulate some of the columns and format some of the sheet. So far, I've tried doing a simple {FileCopy MR.xls, MR2.xls} Works fine, but does not change the fact that it's really a .csv file, so none of my formating or updates get applied, even when I save it using {xlApp.ActiveWorkBook.SaveAs "MR2.xls"} after doing all the updates.

    So, I tried opening the .xls sheet (really .csv.....), then immediately issuing a {xlApp.ActiveWorkBook.SaveAs "MR2.xlsx,xlExcel12"}. Got an error saying I couldn't use that extension for that workbook....

    What's the right strategy here?

    Tuesday, April 12, 2011 4:25 PM


  • Found the answer. Apparently, you must use "FileType" of "xlExcel12Xml" Thanks.
    • Marked as answer by Liliane Teng Thursday, April 14, 2011 2:48 AM
    Wednesday, April 13, 2011 12:40 AM

All replies