Convert CSV file to XLS

[jamsta]

Hi all,

I am after some code that will convert a csv file to an XLS file. I have made a start with the help of some older posts on this forum and elsewhere. Currently I have this:

Sub test2()
    
    strDBPathAndFile = CurrentProject.Path 
    
    Dim XLApp As Object
    Set XLApp = CreateObject("Excel.Application")
    
    With XLApp
        .Application.DisplayAlerts = False
        
        .Workbooks.Open strDBPathAndFile & "\Inputs\PBL.csv"
        .ActiveWorkbook.SaveAs strDBPathAndFile & "\Inputs\PBL.xls"
        .ActiveWorkbook.Close
        
        .Application.DisplayAlerts = True
    End With

End Sub

This does produce a file call PBL.XLS, which is what I'm after, but it's not actually an XLS - it just has that extension.

So what I want to mimmick is the thing that happens if a user opens a csv in Excel, and then save as an XLS by selecting "Excel 97 - 2003 Workbook (*.xls)" from the drop-down list.

Any advice, much appreciated as ever.

 

[jamsta]

All,

I've found it... I should have had:

.ActiveWorkbook.SaveAs strDBPathAndFile & "\Inputs\PBL.xls", FileFormat:=xlNormal

You see - it's inspiration enough just to post on here, even if I knew the answer all along!

 

[The_Doc_Man]

Try that .SaveAs using

....SaveAs file, FileFormat={correct constant for file format}

Look that up using Excel help to look up the FileFormat constants. Better yet, use the Excel help to look up all the options that you don't want defaulted for the .SaveAs function. You'll get better results for it.

FOLLOW UP: Our posts crossed each other. You must have posted just before I clicked on my Save Changes button. But at least you got the right answer.

 

[jamsta]

Thanks Doc Man.