saveas workbook in vba

[lezawang]

Hi
When I run the code below, I get this message

But that defeat the purpose. I mean, if I entered file name as Hello.xlsm, sill I get that message.
How can I improve that code?

By the way, if I save the file as "*.xlsm" before running that code and then when I run the code below, I wont get that massage. But if the file was a new (for example its name is Book1) then running the code below will get me that message. Thanks a lot.

Sub mysaveas()
    Dim xfile As String
    xfile = InputBox("enter file name with extension")
    Workbooks(1).SaveAs Filename:=xfile
End Sub

 

[gallen]

I think you need to specify the file format in the saveas routine.

Workbooks(1).SaveAs Filename:=xfile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

 

[Fluff]

Do you always want to save as an xlsm file?

 

[lezawang]

Not really. If I can make it flexible that is even better. Thank you all for the help.

 

[Fluff]

Ok, how about

Sub mysaveas()
    Dim xfile As String
    Dim Frmt As Long
    xfile = InputBox("enter file name with extension")
    Select Case Mid(xfile, InStrRev(xfile, ".") + 1)
      Case "xls": Frmt = 56
      Case "xlsx": Frmt = 51
      Case "xlsm": Frmt = 52
      Case "xlsb": Frmt = 50
   End Select
   If Frmt = 0 Then Exit Sub
    Workbooks(1).SaveAs xfile, Frmt
End Sub

 

[lezawang]

Worked fine. Thank you a lot for the help that was great.

 

[Fluff]

Glad to help & thanks for the feedback