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

 

[Jaafar Tribak]

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.

True. I missed the fact that the code was closing the active workbook before opening the new copy-workbook.

See if this works for you:

Sub Copy()
    Dim strFilename As String
    Dim strActiveWbName As Workbook
    
    strActiveWbName = ActiveWorkbook.Name
    strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52
    Application.OnTime Now + TimeSerial(0, 0, 2), "'OpenFile """ & strFilename & """,""" & strActiveWbName & "'"

End Sub

Sub OpenFile(ByVal FilePathName As String, ByVal ActiveWbName As String)
    Workbooks.Open (FilePathName)
    Workbooks(ActiveWbName).Close
End Sub

 

[khwaja]

Thanks. It has thrown up an runtime error 91: Object variable or With Block Variable not set and it stopping at:

strActiveWbName = ActiveWorkbook.Name

 

[MoshiM]

Thanks. It has thrown up an runtime error 91: Object variable or With Block Variable not set and it stopping at:

strActiveWbName = ActiveWorkbook.Name

I figured it out. using the save as method overwrites the existing workbook so instead use save copy as. If the format of the copy doesn't change then just use save as on the newly opened workbook

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 

   'Set K = Workbooks.Open (strFilename)
   
   Workbooks.Open (strFilename)
    
    D.Close

End Sub

 

[Davavo]

hey,
I am not sure if your problem is caused by the same issue i was having but maybe try this for closing the file down.

wb.Close SaveChanges:=True 
 Set wb = Nothing

'without this i find that sometimes the (i think) vba editor keeps files open that should be closed down.

 

[Jaafar Tribak]

Thanks. It has thrown up an runtime error 91: Object variable or With Block Variable not set and it stopping at:

strActiveWbName = ActiveWorkbook.Name

Sorry - the variable should be declared as String :

Try this :

Sub Copy()
    Dim strFilename As String
    Dim strActiveWbName As String
    
    strActiveWbName = ActiveWorkbook.Name
    strFilename = ActiveWorkbook.Path & "" & "GJCT Roster Week " & Range("sWeekNo") + 1 & " WS " & Range("sWeekStart") + 7 & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52
    Application.OnTime Now + TimeSerial(0, 0, 2), "'OpenFile """ & strFilename & """,""" & strActiveWbName & "'"

End Sub

Sub OpenFile(ByVal FilePathName As String, ByVal ActiveWbName As String)
    Workbooks.Open (FilePathName)
    Workbooks(ActiveWbName).Close
End Sub

 

[khwaja]

I am again grateful for your help. I tried the following code and it worked.

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

'Set K = Workbooks.Open (strFilename)

Workbooks.Open (strFilename)

D.Close

End Sub

The last one was producing an error.