Closing & Opening Files

khwaja

New Member
Joined
Aug 19, 2005
Messages
14
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.

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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.

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


Code:
workbooks.open(strfilename)
 
Upvote 0
Thanks a lot. I tried the code. For some reason it is not opening the file I just created. Ms Excel opens though. I hope I placed it correctly.

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
Workbooks.Open (strFilename)
End Sub
 
Upvote 0
What if you introduce a small delay as follows :
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
    Application.OnTime Now + TimeSerial(0, 0, 2), "'OpenFile """ & strFilename & "'"
End Sub

Sub OpenFile(ByVal FilePathName As String)
    Debug.Print Dir(FilePathName)
    Workbooks.Open (FilePathName)
End Sub
 
Last edited:
Upvote 0
Thanks a lot. I tried the code. For some reason it is not opening the file I just created. Ms Excel opens though. I hope I placed it correctly.

Change
Code:
strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week "  & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7  & ".xlsm"

To
Code:
strFilename = ActiveWorkbook.Path & "\" & "GJCT Roster Week "  & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7  & ".xlsm"
 
Last edited:
Upvote 0
Thanks. I did try to run it with the amended code but still it does not open the duplicated file created. Code now looks like this:

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
Application.OnTime Now + TimeSerial(0, 0, 2), "'OpenFile """ & strFilename & "'"
End Sub

Sub OpenFile(ByVal FilePathName As String)
Debug.Print Dir(FilePathName)
Workbooks.Open (FilePathName)
End Sub
 
Upvote 0
What is the output in the immediate window of the following line:
Code:
[COLOR=#333333]Debug.Print Dir(FilePathName)[/COLOR]
 
Upvote 0
I am very grateful for your help.

I think the reason for this. The macro is in the file I am closing, so it loses the ability to go further to open the file. Do you think I need to save the macro somewhere? I remember in an old version of Excel I used to have menu bar listing of a few macros. Not too sure whether the 365 version has that option. Bit rusty on these things as I am semi retired and no longer actively working in Excel. An issue is this that if I don't save this at workbook level, how could someone else run the macro in their own application.
 
Last edited:
Upvote 0
I am very grateful for your help.

I think the reason for this. The macro is in the file I am closing, so it loses the ability to go further to open the file. Do you think I need to save the macro somewhere? I remember in an old version of Excel I used to have menu bar listing of a few macros. Not too sure whether the 365 version has that option. Bit rusty on these things as I am semi retired and no longer actively working in Excel.
Code:
Sub Copy()
Dim strFilename As String,K as workbook,D as WORKBOOK
Set D =activeworkbook
strFilename = D.Path & "\GJCT Roster Week "  & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7  & ".xlsm"
D.SaveAs Filename:=strFilename, FileFormat:=52
SET K=Workbooks.Open (strFilename)
D.Close

End Sub
 
Last edited:
Upvote 0
Thanks. It is still the same. Only Excel remains open. It could be checked generically as it could be any file. Could you kindly try duplicating a file yourself and then opening it?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top