none
Open Excel template as a template VBA RRS feed

  • Question

  • When I open a template file it opens as a read only excel document?

        Set xlApp = CreateObject("Excel.Application") 'Create an Excel instance

        Set xlWb = xlApp.Workbooks.Open(sFile, True) 'Open the specified workbook
        xlApp.Visible = True    

    Where sFile = "..\…\…\filename.xltm"

    Any suggestions please. I need to open the file, run a macro which updates some cells then close and save as the macro file

    Thanks

                   

    Tuesday, August 6, 2019 2:01 PM

Answers

  • In the line

    Set xlWb = xlApp.Workbooks.Open(sFile, True)

    the second argument True is not the Editable argument, but the UpdateLinks argument.

    It's a long shot, but what if you change it to

    Set xlWb = xlApp.Workbooks.Open(FileName:=sFile, Editable:=True)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Judith M Tuesday, August 6, 2019 3:18 PM
    Tuesday, August 6, 2019 3:10 PM

All replies

  • Do you really want to open the template, or do you want to create a new workbook based on the template?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 6, 2019 2:16 PM
  • I really want to open the template. This is used by users who open it up to create their timesheets. However the template has some back data that is used to force data validation and I want to update the back data and then close it down so that when they next use it the background data is correct

    Thanks

    Tuesday, August 6, 2019 2:20 PM
  • Have you added the file location to your Trusted Locations?

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, August 6, 2019 2:22 PM
  • I think the location is fine. if I run the macro from excel

        Workbooks.Open Filename:= _
            "xxxx\xxxxx\Timesheet TEMPLATE 190719 v2.xltm" _
            , Editable:=True

    It opens up fine as the excel template

    Tuesday, August 6, 2019 2:31 PM
  • In the line

    Set xlWb = xlApp.Workbooks.Open(sFile, True)

    the second argument True is not the Editable argument, but the UpdateLinks argument.

    It's a long shot, but what if you change it to

    Set xlWb = xlApp.Workbooks.Open(FileName:=sFile, Editable:=True)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Judith M Tuesday, August 6, 2019 3:18 PM
    Tuesday, August 6, 2019 3:10 PM
  • yep, that worked perfectly thank you
    Tuesday, August 6, 2019 3:18 PM