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
