FileFormat options?

[BarefootPaul]

I wrote this code to populate a cell in the file and then save it to a location on a shared folder on the server. When it runs it fails to save it at all.

Private Sub BtnOK_Click()
    ActiveWorkbook.Sheets("Info").Activate
    Range("A3").Select
    If IsEmpty(TxtCredentials.Value) = True Then
        ActiveCell.Value = TxtFName.Value & " " & TxtLName.Value
    ElseIf IsEmpty(TxtCredentials.Value) = False Then
        ActiveCell.Value = TxtFName.Value & " " & TxtLName.Value & ", " & TxtCredentials.Value
    End If
 
    Dim MyName As String
    Dim MyPath As String
    MyName = TxtFName.Value & " " & TxtLName.Value & " TCM Billing Log 2011.xlsm"
    MyPath = "I:\Forms\Billing\Users\" & TxtEmail.Value & "\"
    ActiveWorkbook.SaveAs Filename = MyPath & MyName, FileFormat = xlOpenXMLWorkbookMacroEnabled
    MsgBox ("Your Billing Log has been saved to the shared drive at I:\Forms\Billing\Users\" & TxtEmail & "\ as" & TxtFName.Value & " " & TxtLName.Value & " TCM Billing Log 2011.xlsm.")
End Sub

I have read http://www.rondebruin.nl/saveas.htm and tried using FileFormat = 52, but get the same error.

Any help would be greatly appreciated. I am new to this and welcome any and all feedback.

Thanks

 

[AlphaFrog]

Do you need to add a path separator at the end of MyPath?

MyPath = "I:\Forms\Billing\Users\" & TxtEmail.Value & "[COLOR="Red"]\[/COLOR]"

 

[BarefootPaul]

I have it that way in my code. I must have copied it when I was fooling around trying to get something to work. If I remove the FileFormat option from the code, it will save the workbook to the Documents directory as False.xls. Which, of course, is not what I want.

 

[AlphaFrog]

What version of Excel is this macro running from?

 

[BarefootPaul]

2010

 

[AlphaFrog]

Try this...

ActiveWorkbook.SaveAs Filename[COLOR="Red"]:=[/COLOR]MyPath & MyName, FileFormat[COLOR="Red"]:=[/COLOR]xlOpenXMLWorkbookMacroEnabled

 

[BarefootPaul]

Thanks, that saved it as an .xlsm file, though it still saved it in my Documents folder as False.xlsm.

I guess I need to figure out why the Path and File name are not working right. That will be for tomorrow!

Why does it want := instead of =? Should I be using that in all of the code in 2010?

Thanks again.

 

[AlphaFrog]

FileName and FileFormat are argument labels for SaveAS. They are not variables.

Arguments use the syntax; ArgLabel:=

Variables use the equal sign to set a value.

 

[wigi]

Fileformats can also be found on my website (although I apologize it's in Dutch):
http://users.skynet.be/fa436118/wim/bestandsformaten.htm

But I think you can find some info there too (check out the last table).