Hi Group,

I've recreated a spreadsheet that has multiple macro's behind it. One of these macro's opens and existing file, copies the data in it and then pastes it into the original workbook (where the macro's originate) and then closes the existing file. After the copy and paste is done, this original worksheet (for some unknown reason) is being saved as a new file (that name is "89.123456"). For the life of me, I can't figure out why.

Here's the code that runs this:

    Option Explicit

    Dim strategyName As String
    Dim docName As String
    Dim priceOptName As String
    Dim folderName As String
    Dim rptName As String
    Dim prevDate1 As Date
    Dim prevDate As String
    Dim Month1 As String
    Dim Day1 As String
    Dim Year1 As String

Sub CopyStrategyAnalysis()

    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String

'   This Macro copies the Strategy Document and the PO Optimization Document

    ' This retrieves the files names from the Info tab page
    Sheets("Strategy").Select
    strategyName = Sheet4.Range("C6").Value
    docName = Sheet4.Range("C4").Value
    priceOptName = Sheet4.Range("C7").Value
    folderName = Sheet4.Range("B8").Value
    rptName = Sheet4.Range("C5").Value


    ' Check to see if we are on/off the Starwood network
    strPrompt = "Because you are off the network, you must first open" & vbNewLine & "the 'Analysis' and 'Price Optimization' documents" & vbNewLine & "manually.  If this has been done, click 'Yes'.  If not," & vbNewLine & "open these now and then click 'Yes'.  Otherwise click 'No'."
    strTitle = "Open Documents"

    If Sheet1.rdoNetwork = False Then
        iRet = MsgBox(strPrompt, vbYesNo, strTitle)
        If iRet = vbNo Then
            Exit Sub
        End If
    End If

    If Sheet1.rdoNetwork = True Then
        Workbooks.Open Filename:="O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels\" & folderName & "\Daily Reports\" & priceOptName
        Workbooks.Open Filename:="O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels\" & folderName & "\Daily Reports\" & strategyName
    End If

' Confirms that both files are open and ready to import
    If Sheet1.rdoNetwork = False Then
        Dim wBook1 As Workbook
        Dim wBook2 As Workbook


        On Error Resume Next
        Set wBook1 = Workbooks(priceOptName)
        Set wBook2 = Workbooks(strategyName)

        If wBook1 Is Nothing And wBook2 Is Nothing Then
            MsgBox priceOptName & " and " & strategyName & vbNewLine & "is not open.  Please open them now.", vbCritical, "Workbook Check"
            Set wBook1 = Nothing
            Set wBook2 = Nothing
        Exit Sub
        End If

        If wBook1 Is Nothing Then
            MsgBox priceOptName & " is not open", vbCritical, "Workbook Check"
            Set wBook1 = Nothing
            Exit Sub
        End If

        If wBook2 Is Nothing Then
            MsgBox priceOptName & " is not open", vbCritical, "Workbook Check"
            Set wBook2 = Nothing
            Exit Sub
        End If
    End If

    Application.DisplayAlerts = False
    ' This selects the Strategy Analysis document, copies and pastes the data into the Analysis tab page
    Windows(strategyName).Activate
    Cells.Select
    Selection.Copy
    Windows(docName).Activate
    Sheets("Analysis").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Windows(strategyName).Close

    ' This selects the PO Optimazation document, copies and pastes the info into the PO tab page
    Windows(priceOptName).Activate
    Range("A:AI").Select
    Selection.Copy
    Windows(docName).Activate
    Sheets("PO").Select
    Range("B1").Select
    ActiveSheet.Paste
    Range("B1").Select
    Windows(priceOptName).Close
    ' This returns the view to the Strategy tab page
    Sheets("Strategy").Select
    Range("A9").Select
    Application.DisplayAlerts = True

End Sub

One of the things that I do see is that when the original spreadsheet is closed (by another macro), it is leaving the instance of the workbook open. I can see this instance open in both the task manager and by looking at the left side of the Project listing on the VB editor. The close code looks like this:

Sub SaveAndUpdate()
    Dim docDate As String
    Dim Month1 As Integer
    Dim month2 As String
    Dim Day1 As Integer
    Dim day2 As String
    Dim Year1 As String
    Dim year2 As String
    Dim dateExt As String

    If Sheet1.rdoNetwork = False Then
        MsgBox "Because you are out of network, you" & vbNewLine & "will need to save this document to" & vbNewLine & "your computer manually", vbOKOnly
        Exit Sub
    End If

    Sheets("Info").Select
    strategyName = Range("C6").Value
    docName = Range("C4").Value
    priceOptName = Range("C7").Value
    folderName = Range("B8").Value
    rptName = Range("C5").Value
    Sheets("Strategy").Select
    ' Get current date and save it to be a file extension
    Month1 = Month(Date)
    If Month1 < 10 Then
        month2 = "0" & Month1
    Else
        month2 = Month1
    End If

    Day1 = Day(Date)
    If Day1 < 10 Then
        day2 = "0" & Day1
    Else
        day2 = Day1
    End If

    Year1 = Year(Date)
    Year1 = Right(Year1, 2)
    year2 = Year(Now())
    docDate = month2 & day2 & Year1
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels\" & folderName & "\Daily Reports\" & docName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
    Sheets("Strategy").btnSaveExportClose.Visible = False
    Sheets("Strategy").btnUpdateSheet.Visible = False
    Sheets("Strategy").btnClearLastDay.Visible = False
    Sheets("Strategy").rdoNetwork.Visible = False
    Sheets("Strategy").rdoOffNetwork.Visible = False

    Sheets("Info").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Historical Data").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Strategy").Select

    Application.DisplayAlerts = False
    ChDir "O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels\" & folderName & "\Daily Reports\" & year2
    ActiveWorkbook.SaveAs Filename:="O:\Revenue Management\Centralized Revenue Management Service\CRMS Hotels\" & folderName & "\Daily Reports\" & year2 & "\" & rptName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A9").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Application.DisplayAlerts = True
    ActiveWindow.Close
End Sub

A couple of questions:

Do you see anything that would cause the spreadsheet to save with this unique name and file extension? Why does the instance of the workbook remain open when the actual spreadsheet is closed? Is there a way to do a "hard close" on that instance of the workbook?

In advance, thanks for your help.

Don

Hope this help you!

ActiveWorkbook.Close True
Workbooks("Orignial_File_Name").Activate

add this at the end.

Hope this help you!

ActiveWorkbook.Close True
Workbooks("Orignial_File_Name").Activate

add this at the end.

if you want to don't save it then just change the code to FALSE.

Changing the code to read ActiveWorkbook.Close True made the spreadsheet stop and display an error.

have you tried to use false instead of true?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.