Microsoft Office Forums Closing & Opening Files

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-11-2019, 08:35 PM
Khwaja Khwaja is offline Closing & Opening Files Windows 10 Closing & Opening Files Office 2016
Novice
Closing & Opening Files
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default Closing & Opening Files

I am using the following code to create a duplicate of an active file and then close the active file. But I am struggling to open the file I just created. Will appreciate some hand holding.





Code:
Sub Copy()
Dim strFilename As String
strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm"
ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52
ActiveWorkbook.Close
End Sub

Last edited by Pecoflyer; 08-11-2019 at 11:57 PM. Reason: Replaced quote tags with code tags
Reply With Quote
  #2  
Old 08-12-2019, 07:27 AM
NoSparks NoSparks is offline Closing & Opening Files Windows 7 64bit Closing & Opening Files Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 722
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

I don't believe this is creating a duplicate of the active file.
When you save it with a different name the original will be abandon in its last saved state and
you will automatically now be working with the newly named and saved file in the current state.
If that is your desire, don't close the active workbook.
OR
Are you saying that you can't find the newly saved file after closing it ?
If so, those empty double quotes may be the issue.
Reply With Quote
  #3  
Old 08-12-2019, 11:44 PM
Khwaja Khwaja is offline Closing & Opening Files Windows 10 Closing & Opening Files Office 2016
Novice
Closing & Opening Files
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

You are spot on with your observation. When I removed close code it worked. following is the code I am using now.

Sub Copy()

Dim strFilename As String, D As Workbook ', K As Workbook

Set D = ActiveWorkbook

strFilename = D.Path & "\GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm"

D.SaveCopyAs Filename:=strFilename


Workbooks.Open (strFilename)

D.Close

End Sub

Only one thing I am now confronted with is the need to replace named ranges to the values I am renaming the file to in the above statements (sWeekNo and sWeekStart). In nutshell I would like the newly created file to show the new week number and week start date as reflected in the file name. Could the be extracted from file name?
Reply With Quote
  #4  
Old 08-13-2019, 04:04 AM
NoSparks NoSparks is offline Closing & Opening Files Windows 7 64bit Closing & Opening Files Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 722
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Use variables, something along the lines of this.
Had to format the date to eliminate forward slashes which are illegal in the file name.
Code:
Sub Try_This()

    Dim strFilename As String, D As Workbook
    Dim newWeekNo As String, newWeekStart As String

Set D = ActiveWorkbook

newWeekNo = Range("sWeekNo") + 1
newWeekStart = Format(Range("sWeekStart") + 7, "d-mmm-yy")

strFilename = D.Path & "\GJCT Roster Week " & newWeekNo & " WS " & newWeekStart & ".xlsm"

D.SaveCopyAs Filename:=strFilename

Workbooks.Open (strFilename)

Range("sWeekNo") = newWeekNo
Range("sWeekStart") = newWeekStart

D.Close

End Sub
Reply With Quote
  #5  
Old 08-14-2019, 12:07 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Closing & Opening Files Windows 7 64bit Closing & Opening Files Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,400
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@kwhaja
Please wrap your code with code tags ( the #button)
Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 08-14-2019, 10:04 PM
Khwaja Khwaja is offline Closing & Opening Files Windows 10 Closing & Opening Files Office 2016
Novice
Closing & Opening Files
 
Join Date: Aug 2018
Posts: 12
Khwaja is on a distinguished road
Default

I am very grateful for your assistance. It works great and in the manner I envisaged. You are a star.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Emails disappear after closing and re-opening outlook Rhen Outlook 1 01-07-2019 01:16 AM
Closing & Opening Files Track Changes Automatically Accepting Changes after Closing and Re-Opening Document cnethercott Word 1 01-16-2017 03:21 AM
Closing Word files Humptydingo Word 5 09-07-2013 10:17 AM
Update powerpoint on a server without closing an opening dntel123 PowerPoint 0 08-18-2011 02:41 AM
Closing & Opening Files WORD opening and closing is slow chum Word 2 07-05-2010 07:06 AM


All times are GMT -7. The time now is 12:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft