Error in previously functioning script for saving workbooks to users desktop 1

[Walter349]

Hi,

Due to a change in security policy, we have had to change our hardware, operating system (now its WIN7) and MS office suite (rolled back to office 2007). and of course its at about 48hours notice.
So the excel script that as previously used under Office 2010 and XP and was working fine, now does not work coming up with the following error.

'Runtime error '1004'
Application-defined or object defined error.

The error seems to be with this line, but I cannot see why! Unless this is not supported in Excel 2007.
Can anybody throw some light on this at all?

ActiveSheet.SaveAs Filename:=DTAddress & "-" & sRange1 & "-" & sRange2 & "-" & sRange3, FileFormat:=xlNormal

from this I am deducing that it does not like the

DTAddress = CreateObject("WScript.shell").specialfolders("Desktop") & Application.PathSeparator

This is the code I am using

Private Sub Save_Click()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim DTAddress As String

'Change Record:
'1 - Changed drive letter to B:\ due to new policy change on hardware & software versions FEB2014.

    
    ActiveSheet.PrintOut

    sRange1 = ActiveSheet.Range("I9")
    sRange2 = ActiveSheet.Range("I5")
    sRange3 = Format$(Date, "DD-MM-YYYY")
        
    Application.DisplayAlerts = False
        ActiveSheet.Shapes("Save").Delete
        DTAddress = CreateObject("WScript.shell").specialfolders("Desktop") & Application.PathSeparator
        ActiveSheet.SaveAs Filename:=DTAddress & "-" & sRange1 & "-" & sRange2 & "-" & sRange3, FileFormat:=xlNormal
        ActiveWorkbook.SaveCopyAs Filename:="B:\" & "-" & sRange1 & "-" & sRange2 & "-" & sRange3 '1
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
        
    Application.DisplayAlerts = True
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Excel.Range, Cancel As Boolean)
     If Not Intersect(target, Range("B19:I19")) Is Nothing Then
        If target.Interior.ColorIndex = xlNone Then
            target.Interior.ColorIndex = 3
        ElseIf target.Interior.ColorIndex = 3 Then
            target.Interior.ColorIndex = xlNone
        End If
    End If
    
    If Not Intersect(target, Range("B47:L47")) Is Nothing Then
        If target.Interior.ColorIndex = xlNone Then
            target.Interior.ColorIndex = 3
        ElseIf target.Interior.ColorIndex = 3 Then
            target.Interior.ColorIndex = xlNone
        End If
    End If

    If Not Intersect(target, Range("B49:I49")) Is Nothing Then
        If target.Interior.ColorIndex = xlNone Then
            target.Interior.ColorIndex = 3
        ElseIf target.Interior.ColorIndex = 3 Then
            target.Interior.ColorIndex = xlNone
        End If
    End If
End Sub
Private Sub worksheet_change(ByVal target As Range)
    If target.Address = "$I$9" Then
        ActiveSheet.Name = Range("I9")
    End If
End Sub

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

hi,

Your DTAddress script works in Excel 2007. I like that. I never saw that before, but it returned the path to my desktop!

  ActiveSheet.SaveAs Filename:=DTAddress & "-" & sRange1 & "-" & sRange2 & "-" & sRange3, FileFormat:=xlNormal

Concerning the Worksheet.SaveAs, I have these comments:

I would not be using ActiveSheet in any of this code unless this was the ONLY sheet in the workbook, and even then it is possible for this code to work on the incorrect worksheet, if other workbooks are also open in the same instance of Excel. Thatever worksheet you intend to be the ActiveSheet, is the ONLY sheet the has the right stuff in I9 & I5!

Look in VBA Help for SaveAs - FileFormat and you'ii see no xlNormal in 2007.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

xlNormal is enumerated constant in xlWindowState, it equals -4143 (&HFFFFEFD1). This value equals to value of xlWorkbookNormal in xlFileFormat. Luckily they match, but it's a good practise to avoid such inconsistencies.
You can try to go through the whole process manually, possibly with recording it. There may be a problem with saving file to specific directory (I've seen a situation when it was not permitted to write file in a root directory, however the user could create a folder).
You can also try to debug the code:
- comment "DisplayAlerts" and see messages,
- declare variable and assign full name to it before saving a sheet, check it (see locals window),
- sometimes below error message (Application-defined or object defined error.) there is additional description, is it your case?

Skip, there is more info:

http://technet.microsoft.com/en-us/library/ee156603.aspx

. Alternatively one can use SHELL (with even more system constants):
[tt]Dim shShell As Shell32.Shell
Set shShell = New Shell32.Shell
MsgBox shShell.Namespace(ssfDESKTOP).Self.Path[/tt]



combo

 

[SkipVought]

Thx combo. Good Tip!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]