Microsoft Office Forums

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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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 offline Closing & Opening Files Windows 7 64bit Closing & Opening Files Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@kwhaja
Please wrap your code with code tags ( the #button)
Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:20 AM.


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