I am experiencing issues related to the thread below. I have used both the URL (https) and network addresses (\\server\...) and am experiencing the same problems.
Users are able to save 2 or 3 times normally and then they receive
Getting_List.jpg
The box never goes away and when you cancel, the code gives 1004 error on the saveas command.
I have added a 3 second pause before the save command as suggested by an internal IT guy and am using the FileFormat attributes on all saveas commands.
http://www.excelforum.com/excel-prog...ent-types.html
Code is below...
Sub Build_Send_File()
toast = Empty
toastname = Empty
Cncl = False
UserForm1.Show
If Cncl = True Then GoTo endr
If Application.Version >= "12.0" Then
rpt2010:
toast = Application.GetSaveAsFilename(InitialFileName:=Emp_Name & " - " & Format(DandT, "mm-dd-yyyy") & _
".xlsx", fileFilter:="Excel Files (*.xlsx), *.xlsx,All Files (*.*), *.*")
If toast = False Then
If MsgBox("All information will be lost." & vbCrLf & _
"Are you sure you wish to cancel the save process?", vbYesNo) = vbYes Then _
GoTo endr Else GoTo rpt2010
End If
toastname = Right(toast, Len(toast) - InStrRev(toast, "\"))
FFormat = 51
Else
rpt2003:
toast = Application.GetSaveAsFilename(InitialFileName:=Emp_Name & " - " & Format(DandT, "mm-dd-yyyy") & _
".xls", fileFilter:="Excel Files (*.xls), *.xls,All Files (*.*), *.*")
If toast = False Then
If MsgBox("All information will be lost." & vbCrLf & _
"Are you sure you wish to cancel the save process?", vbYesNo) = vbYes Then _
GoTo endr Else GoTo rpt2003
End If
toastname = Right(toast, Len(toast) - InStrRev(toast, "\"))
FFormat = 56
End If
Workbooks.Add xlWorksheet
NewBook = ActiveWorkbook.Name
Range("A1").Select
ActiveCell.Value = "Center"
ActiveCell.Offset(0, 1).Value = Center
ActiveCell.Offset(1, 0).Value = "Employee Name"
ActiveCell.Offset(1, 1).Value = Emp_Name
ActiveCell.Offset(2, 0).Value = "Employee Supervisor"
ActiveCell.Offset(2, 1).Value = Emp_Sup
ActiveCell.Offset(4, 0).Value = "Meeting Held"
ActiveCell.Offset(4, 1).Value = MeetHeld
ActiveCell.Offset(5, 0).Value = "Date"
ActiveCell.Offset(5, 1).Value = DandT
ActiveCell.Offset(6, 0).Value = "Non-Compliance"
ActiveCell.Offset(6, 1).Value = Noncomp
ActiveCell.Offset(7, 0).Value = "Comments"
ActiveCell.Offset(7, 1).Value = Comments
Range("A:B").EntireColumn.AutoFit
Application.Wait (Now + TimeValue("00:00:03"))
If Center = "EMEA" Or Center = "ASIAPAC" Or Center = "Other" Then
ActiveWorkbook.SaveAs Filename:="https://server/sites/Shared%20Documents/Temp_Storage/" _
& toastname, FileFormat:=FFormat
Else
ActiveWorkbook.SaveAs Filename:="\\server@SSL\DavWWWRoot\sites\Shared%20Documents\Temp_Storage\" _
& toastname, FileFormat:=FFormat
End If
ActiveWorkbook.SaveAs Filename:=toast, FileFormat:=FFormat
ActiveWorkbook.Close
endr:
End Sub
Please help.
Bookmarks