Hi, all, I have the following code in a *.xlsm workbook with 4 sheets. When I click the "Create Docs" button on one sheet, it calls the CreateDocs() sub below, which in turn calls the SetLocationNumber function and the SaveExportFile sub. The sub runs fine as long as the proposed file name is unique, but it gives me the "Microsoft Excel has stopped working" error if I run it twice with the same "locaNumber" and "selectedPerson" values. When it does and I click "Restart the program", it takes quite some time before restarting. Several times I had to use Task Manager to get out of the recovery process. It fails BEFORE opening the Save As dialog box. I suspect that it has something to do with the call to ActiveWorkbook.BreakLink. Can you please help me understand where I've gone wrong?
I am using Excel 2010 in Windows 7, but some of my users are still in Office 2007 and Windows XP.
Option Explicit
'Declare module-level variables.
Private formattedLocaNum As String
Sub CreateDocs()
If SetLocationNumber = False Then Exit Sub
' Copy the needed sheets to a new workbook.
Sheets(Array("Pay App", "PO")).Copy
' Break links to the main workbook.
ActiveWorkbook.BreakLink Name:=ThisWorkbook.FullName, Type:=xlExcelLinks
' Select the first sheet.
Sheets("Pay App").Select
Call SaveExportFile("Docs - " & Range("selectedPerson").Value, _
"Microsoft Office Excel Workbook (*.xlsx),*.xlsx", _
xlOpenXMLWorkbook, True)
End Sub
Private Function SetLocationNumber() As Boolean
' Set up the 4-digit location number.
Dim rawLocaLength, i As Integer
Dim rawLocaNum As String
Dim currentSheet As Worksheet, inputSheetExists As Boolean
inputSheetExists = False
For Each currentSheet In Worksheets
If currentSheet.Name Like "Input" Then inputSheetExists = True: Exit For
Next
If inputSheetExists = True Then
rawLocaNum = Trim(Range("locaNumber").Value)
' Empty the string.
formattedLocaNum = ""
' Determine how many zeros are needed.
rawLocaLength = 4 - Len(rawLocaNum)
' Add a "0" to the location number with each loop.
For i = 0 To rawLocaLength - 1
formattedLocaNum = formattedLocaNum & "0"
Next
' Prepend the leading zeros onto the location number.
formattedLocaNum = formattedLocaNum & rawLocaNum
Else
Dim result As Integer
result = MsgBox("Sorry! Unable to complete request." & vbCrLf & _
"This workbook does not include all the needed worksheets.", vbOKOnly, "Missing Data")
End If
'Return true/false based on success.
SetLocationNumber = inputSheetExists
End Function
Private Sub SaveExportFile(ByVal fileNameSuffix As String, _
ByVal fileTypeDropdown As String, _
ByVal fileFormatConstant As Integer, _
ByVal autoCloseExportFile As Boolean)
Dim myFileName As Variant
' Create the default file name.
myFileName = formattedLocaNum & " - " & fileNameSuffix
' Open a save as dialog box.
myFileName = Application.GetSaveAsFilename(myFileName, fileTypeDropdown, , "Select File Location")
' Only do the following if the user doesn't cancel or try to save an empty string.
If myFileName <> "" And myFileName <> "False" And myFileName <> False Then
' Turn off alerts.
If fileFormatConstant = xlExcel8 Then Application.DisplayAlerts = False
' Save the file with the name they chose.
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=fileFormatConstant
' Turn alerts back on.
If Application.DisplayAlerts = False Then Application.DisplayAlerts = True
' Close the file if flagged for closure and re-activate this workbook.
If autoCloseExportFile = True Then
ActiveWorkbook.Close
ThisWorkbook.Activate
End If
End If
End Sub
Bookmarks