Closing & Opening Files

[khwaja]

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

 

[MoshiM]

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

workbooks.open(strfilename)

 

[khwaja]

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

 

[Jaafar Tribak]

What if you introduce a small delay as follows :

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

 

[MoshiM]

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

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

To

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

 

[khwaja]

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

 

[Jaafar Tribak]

What is the output in the immediate window of the following line:

[COLOR=#333333]Debug.Print Dir(FilePathName)[/COLOR]

 

[khwaja]

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.

 

[MoshiM]

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.

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

 

[khwaja]

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?