Why won't this work?

[mrphilk]

Hi All,

I have this code from another project that I am trying to adapt for another, but for some reason it wont work. The original was written in Excel 2003, I am now using 2007.

=====

Sub Generate_Office_Copy()

' On Error GoTo oops:

Dim Response As VbMsgBoxResult
Response = MsgBox("All entered information will now be saved as a New File." & vbCrLf & _
"Are you sure you want to continue?", vbQuestion + vbYesNo, "KHS Service Sheet Office Copy Generation")
If Response = vbNo Then Exit Sub

ActiveWorkbook.save

Dim newfile As String, ssno As String, eng As String, cust As String, loc As String

ssno = Range("Y2").Value
eng = Range("F19").Value
cust = Range("G44").Value
'loc = Range("j4").Value

newfile = ssno & ", " & eng & ", " & cust & "_" & " Service Report.xlsm"

ChDir ActiveWorkbook.Path

'  "C:\Documents and Settings\KHS\Desktop\Drafts"

ActiveWorkbook.SaveAs Filename:=newfile
    
' If Filename.newfile Then GoTo oops


    
MsgBox "Service Report Saved in the directory;" & vbCrLf & _
        vbCrLf & _
        ActiveWorkbook.Path & "\" & newfile & vbCrLf & _
        vbCrLf & _
        "PLEASE MAKE A NOTE OF THIS LOCATION FOR WHEN FOR FUTURE EDITING / MAILING!" & vbCrLf & _
         vbCrLf & "If you need to make further changes to this sheet then do so in the normal way (i.e file - save etc)" & _
         vbCrLf & _
         vbCrLf & _
         "=================[ NB: This is the OFFICCE COPY! ]================" & vbCrLf & _
         "If you need to make another Service Sheet please Re-open the MASTER DOCUMENT" _
         , vbQuestion + vbOKOnly, "Office Copy Created"
         
  ' ActiveWorkbook.Close
   
                       
 GoTo finished:
         
oops:

MsgBox ("The service sheet: " & newfile & " already exists" & vbCrLf & _
        vbCrLf & _
        "Please change the service sheet number and try again")
              
finished:

End Sub

It seems to be hanging on

ActiveWorkbook.SaveAs Filename:=newfile

But I cant seem to see why, any ideas?

Also, how do I stop Excel from throwing up privacy errors because I am using macros every time I save?

Thanks!

 

[kpark91]

What are these values?

ssno = Range("Y2").Value
eng = Range("F19").Value
cust = Range("G44").Value

 

[mrphilk]

Genius! The last value was the date, didnt realise but it had `/` in it, so was not correct.

Thanks!

Any idea on the error message every time I save?

 

[kpark91]

?? Is it still causing errors?
What odes the error msg say?

 

[mrphilk]

Sorry I was vague on that issue, its the privacy warning but I've got rid under privacy options. I hate what they've done with office its like going back to school lol!

Im trying to get the date into the filename but this keeps throwing up a debug error.

I think its because the actual cell value is 01/01/2011, although I change the cell format to something which should be ok, ie

01 Jan 11

It still throws in an error, any ideas?

Thanks

 

[kpark91]

How about changing whatever ur cell is calling to as

.Text

instead of .Value

Try debugging it step by step and then seeing the value of ssno, eng, and cust

I'm quite confident that it has to do with the filename variable...
What are in the cells?
You need full path of the file you want to save to.. i.e C:\Windows\BlahbBlah\Hello.xls

Try looking if you have any of the following windows naming restricted characters in the filename
http://msdn.microsoft.com/en-us/library/aa365247(v=vs.85).aspx

 

[mrphilk]

Thats done it!


Thanks for that, also thanks for such a quick response!

Now on with the next issue lol!