Saving a .csv as a .xlsx from a macro

[ZIXILPLIX]

I recorded the following code to save my file as a .xlsx (it starts as a .csv)

    ActiveWorkbook.SaveAs Filename:= _"C:\Users\Doug\Desktop\PARSED_PRODUCT CODE_ID.xlsx", FileFormat:=xlOpenXMLWorkbook _
        , CreateBackup:=False

It works great to save the file in the proper format. What it doesn't do is, it doesn't allow the user to update the name with the proper product code and ID. It just saves it in the path above automatically.

I have the following code that prompts and allows the user to change the name.

ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename("PARSED_PRODUCT CODE_ID" & ActiveWorkbook.Name, _
    fileFilter:="Excel Workbook (*.xlsx),*.xlsx,")

The problem with this bit of code is that even though it says it saves it as a .xlsx, when I try to open the saved file, it tells me it cannot because the file format or file extension is invalid. I've even tried to do .xls instead which gets me part way there. It'll allow me to open the file but all the formatting is gone and data has a bunch of commas. I really need it to be a .xlsx at the end of the day. I was just trying different things along the way.

Is there any way to get it to do what I need? Any help would be greatly appreciated.

 

[Joe4]

Why not just prompt them for a file name, and incorporate that into your code, i.e.

    Dim myFileName As String
    myFileName = InputBox("Enter file name to use (PRODUCT CODE AND ID")
    
    On Error GoTo err_fix
    If myFileName <> "" Then
        ActiveWorkbook.SaveAs Filename:= _
            "C:\Users\Doug\Desktop\" & myFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook _
            , CreateBackup:=False
    Else
        GoTo err_fix
    End If
    On Error GoTo 0
    
    Exit Sub
    
err_fix:
    MsgBox "You have not entered a valid file name!", vbOKOnly, "ERROR!!!"

 

[ZIXILPLIX]

That's brilliant! I didn't know that was possible. Thank you so much Joe4! I wonder if there is a way to have the file name box already populated with text of some sort? Like the name format for example. I toyed around with things like , Value: = ("PRODUCT CODE_ID_PARSED") and TextBox1.Value = ("PRODUCT CODE_ID_PARSED") but no luck. Any ideas?

 

[Joe4]

The third argument of InputBox is a default value, so try something like:

    myFileName = InputBox("Enter file name to use", , [COLOR=#333333]"PRODUCT CODE_ID_PARSED"[/COLOR])

 

[ZIXILPLIX]

Perfect!! Thank you!

 

[Joe4]

You are most welcome!