DanG
Registered User.
- Local time
- Today, 11:29
- Joined
- Nov 4, 2004
- Messages
- 477
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!
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!
Code:
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
