jakebailey
04-13-2023, 07:01 AM
I'm looking for some guidance on the following and appreciate any help.
I have a nightly excel report being exported from SAP and sent to a team. This export is flagged as Read-Only by default. I have an Add-In that the team uses to 'clean' the report and make it usable for Pivot Tables. Everything seems to work with the exception of the last call to save the workbook. I want to do a Save-As to a file location in the My Documents folder and have the active workbook reflect the new file name. The code below does save the file however the active workbook that is opened is still the old workbook with the old name. After doing a couple of hours of searching the best workaround I found was to Save-As the workbook, then open it, then close the existing workbook. Although clunky, it does work for most machines. However there are a number of users on the team where for some reason it does not work properly. Almost like the code is executing and windows/excel isn't keeping up. If I comment out Sub SaveWorkbook() and then go and manually do a Save-As, the active workbook then takes on the new file name and the Read-Only flag goes away. I would love to do away with saving the workbook, then opening it, then closing the old one. To me that seems clunky but maybe that's my only option. Any guidance is much appreciated.
Sub SaveWorkbook()
On Error Resume Next
Dim strFileName As String
Dim strFilePath As String
Dim originalWorkbook As Workbook
Dim newWorkbook As Workbook
With ActiveWorkbook
SetAttr .FullName, vbNormal
.ChangeFileAccess xlReadWrite
Application.DisplayAlerts = False
.Save
Application.DisplayAlerts = True
End With
Call TimeStamp
' Set the original workbook
Set originalWorkbook = ActiveWorkbook
' Get the file path
strFilePath = Environ$("USERPROFILE") & "\Documents\InvPln\"
' Create the file name using the global variable
strFileName = "Sample Text - " & strTimeStamp & ".xlsx"
' Save a copy of the original workbook with the new name
originalWorkbook.SaveCopyAs Filename:=strFilePath & strFileName
' Open the new workbook
Set newWorkbook = Workbooks.Open(strFilePath & strFileName)
' Activate the new workbook
newWorkbook.Activate
Call HideWorksheets
' Close the original workbook without saving changes
originalWorkbook.Close SaveChanges:=False
End Sub
I have a nightly excel report being exported from SAP and sent to a team. This export is flagged as Read-Only by default. I have an Add-In that the team uses to 'clean' the report and make it usable for Pivot Tables. Everything seems to work with the exception of the last call to save the workbook. I want to do a Save-As to a file location in the My Documents folder and have the active workbook reflect the new file name. The code below does save the file however the active workbook that is opened is still the old workbook with the old name. After doing a couple of hours of searching the best workaround I found was to Save-As the workbook, then open it, then close the existing workbook. Although clunky, it does work for most machines. However there are a number of users on the team where for some reason it does not work properly. Almost like the code is executing and windows/excel isn't keeping up. If I comment out Sub SaveWorkbook() and then go and manually do a Save-As, the active workbook then takes on the new file name and the Read-Only flag goes away. I would love to do away with saving the workbook, then opening it, then closing the old one. To me that seems clunky but maybe that's my only option. Any guidance is much appreciated.
Sub SaveWorkbook()
On Error Resume Next
Dim strFileName As String
Dim strFilePath As String
Dim originalWorkbook As Workbook
Dim newWorkbook As Workbook
With ActiveWorkbook
SetAttr .FullName, vbNormal
.ChangeFileAccess xlReadWrite
Application.DisplayAlerts = False
.Save
Application.DisplayAlerts = True
End With
Call TimeStamp
' Set the original workbook
Set originalWorkbook = ActiveWorkbook
' Get the file path
strFilePath = Environ$("USERPROFILE") & "\Documents\InvPln\"
' Create the file name using the global variable
strFileName = "Sample Text - " & strTimeStamp & ".xlsx"
' Save a copy of the original workbook with the new name
originalWorkbook.SaveCopyAs Filename:=strFilePath & strFileName
' Open the new workbook
Set newWorkbook = Workbooks.Open(strFilePath & strFileName)
' Activate the new workbook
newWorkbook.Activate
Call HideWorksheets
' Close the original workbook without saving changes
originalWorkbook.Close SaveChanges:=False
End Sub