Error in the access vba code (1 Viewer)

[Jovial]

Below is the code where template file File1.xlsm is loaded with new data and saved it as File2.xlsm and close the File1.xlsm without saving it. But I am having two problems:
1. Once I try to open the File2.xlsm after database finish the process, it gives the error:
"Excel cannot open the file '01-06-2016 File2.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

2. File1.xlsm does not get closed without saving the content and asked the user to save/unsave the file before closing.

I am not sure where i am doing wrong. Please give me some suggestions.

Dim objApp1 As Object
Dim objBook1 As Object
Dim objSheet1 As Object
Dim strSQL1 as string
Dim db As DAO.Database
Dim rst1 as DAO.recordset

Set objApp1 = CreateObject("Excel.Application")
Set objBook1 = objApp1.Workbooks.Open("C:\Desktop\File1.xlsm")
'Set objApp1 = objBook1.Parent
'Set objSheet1 = objBook1.Worksheets("Data Input")
Set objSheet1 = objBook1.ActiveSheet

objBook1.Application.Visible = True

'Opens the recordset
strSQL1 = "Select * from sometable"
Set rst1 = CurrentDb.OpenRecordset(strSQL1)

With ActiveSheet
       'Clears the current contents in the workbook range
        .Range("A5:G65000").ClearContents
        'rst Copies the recordset into the worksheet
        .Range("A5").CopyFromRecordset rst1
End With
rst1.Close

objBook1.SaveAs "C\Desktop\" & Format(Now(), "mm-dd-yyyy") & " File2.xlsm ", FileFormat:=xlOpenXMLWorkbookMacroEnabled
objBook1.Close

Set rst1 = Nothing
Set objSheet1 = Nothing
Set objBook1 = Nothing
Set objApp1 = Nothing

 

[MarkK]

This format is not correct.

FileFormat:=xlOpenXMLWorkbookMacroEnabled

Check the documentation. I would first try to omit this parameter, and let Excel pick the default format.

 

[ByteMyzer]

Actually, the FileFormat is correct. However, the following modifications need to be made:

objBook1.SaveAs "C\Desktop\" & Format(Now(), "mm-dd-yyyy") _
    & " File2.xlsm ", FileFormat:=xlOpenXMLWorkbookMacroEnabled
[B][I]objBook1.Saved = [COLOR="Navy"]True[/COLOR][/I][/B]
objBook1.Close [B][I][COLOR="navy"]False[/COLOR][/I][/B]

 

[Jovial]

Thank you both for replying.
I modified the code as suggested but i am still having problem in opening the file

 

[ByteMyzer]

Three other corrections to be made that I just spotted:

Change 1:

[COLOR="Navy"]With[/COLOR] ActiveSheet

to...

[COLOR="navy"]With[/COLOR] objBook1.ActiveSheet

Change 2:

objBook1.SaveAs "C\Desktop\" & Format(Now(), "mm-dd-yyyy") _
    & " File2.xlsm ", FileFormat:=xlOpenXMLWorkbookMacroEnabled

...to:

objBook1.SaveAs "C:\Desktop\" & Format(Now(), "mm-dd-yyyy") _
    & " File2.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Change 3:

[COLOR="Navy"]Set[/COLOR] rst1 = [COLOR="navy"]Nothing
Set[/COLOR] objSheet1 = [COLOR="navy"]Nothing
Set[/COLOR] objBook1 = [COLOR="navy"]Nothing
Set[/COLOR] objApp1 = [COLOR="navy"]Nothing[/COLOR]

to...

[COLOR="Navy"]Set[/COLOR] rst1 = [COLOR="navy"]Nothing
Set[/COLOR] objSheet1 = [COLOR="navy"]Nothing
Set[/COLOR] objBook1 = [COLOR="navy"]Nothing[/COLOR]
objApp1.Quit
[COLOR="Navy"]Set[/COLOR] objApp1 = [COLOR="navy"]Nothing[/COLOR]

Also, add this line at the beginning of your code:

[COLOR="Navy"]Const[/COLOR] xlOpenXMLWorkbookMacroEnabled = &H34

 

[Jovial]

Change the code to

[COLOR="navy"]With[/COLOR] objBook1.ActiveSheet

and got the error on line .Range("A5:G65000").ClearContents
Run-timer error '91':
Object variable or With block variable not set

 

[ByteMyzer]

By any chance, is this code inside a module in File1.xlsm itself?

 

[Jovial]

This code is behind the button on the form. Upon clicking, it is supposed to load the data in the template and save it as different name.

 

[Jovial]

I use old code in 'with' statement as:

With ActiveSheet
'Clears the current contents in the workbook range
        .Range("A5:G65000").ClearContents
'rst Copies the recordset into the worksheet
        .Range("A5").CopyFromRecordset rst1
    End With

And I modify the code for 'Save as' as:

ActiveSheet.SaveAs "C\Desktop\" & Format(Now(), "mm-dd-yyyy") _
  & " File2.", FileFormat:=52

This code saves the file and I am able to open it too but remaining code errors out in line objBook1.close False.
Error says:
Run-time error '1004':
This workbook is currently referenced by another workbook and cannot be closed.

 

[The_Doc_Man]

Just as a crazy thought, use the FileCopy function to copy the template to the new name then load/update only the new name file.

 

[Jovial]

I have not use the FileCopy function till now and there are macros and code behind the template.