Excel 2003 to Excel 2010 VBA Error

[DanG]

Hello,

We just converted from Excel 2003 to 2010 and I am getting the following error "Error 1004 - Method 'SaveAs' of object_Workbook failed".. The code worked fine in 2003.

The macro uses the contents of the clipboard to supply the Workbook name. and uses "DataObj.GetText" to do this. I have added MS Form 2.0 to the library already. The code below is not complete, just partial. The error seems to highlight the 3 lines below "ChDir DTAddress". When I run my mouse over the highlighted error area, I can see that it does pick up the contents of the clipboard as a file name if that helps.

I think the problem is with the dataobjt, but I'm not sure.

Any ideas would be great!

Sub SaveToClientList()

'If MsgBox("The Filename Must be Loaded in Your Clipboard", _
'vbOKCancel + vbQuestion, "Confirm Filename Loaded") <> vbOK Then
'Exit Sub
'End If


' The DataObj needs to have MSForms addes to the library
' for this macro to work.

Dim DataObj As New MSForms.DataObject
Dim S As String
Dim DTAddress As String

' DataObj is variable for data that is in the clipboard at the time
' The variable uses substitute to strip hidden characters from the end.

DataObj.GetFromClipboard

' DTAddress is variable for the path to the desktop and adds folder named ClientList

S = DataObj.GetText
S = Application.WorksheetFunction.Substitute(S, Chr(13), "")
S = Application.WorksheetFunction.Substitute(S, Chr(10), "")
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\ClientList"

If Len(Dir(DTAddress, vbDirectory)) = 0 Then
    MkDir DTAddress
End If

' saving of the workbook from the advisors dashboard from a csv to a xls file into
' a folder called "ClientList"

   ChDir DTAddress
    ActiveWorkbook.SaveAs Filename:=S, _
      FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
      ReadOnlyRecommended:=False, CreateBackup:=False

 

[DanG]

Solved!

The problem was "FileFormat:=xlExcel9795", I changed it to "=xlExcel8".