0

I am able to open .xlsx file by CreateObject("Excel.Application").Workbooks.Open("path")

Something like this is not allowing me to create a new Excel Workbook via a PowerPoint Macro.

Set ExcelFile = CreateObject("Excel.Application")
ExcelFile.Workbooks.Add
ExcelFile.ActiveWorkbook.SaveAs "path"
Kusa Shaha
  • 112
  • 1
  • 3
  • 16

1 Answers1

2


sample code, just checked on PP 2016:
(remember to close xlsApp, set obj to nothing etc.) 

Public Sub StackOverflow()

Dim xlsApp As Object
Dim wkbWorking As Object

Set xlsApp = CreateObject("Excel.Application")  'basically it opens excel application
Set wkbWorking = xlsApp.Workbooks.Add       'it creates new workbook in just opened excel

xlsApp.Visible = True           'makes excel visible
wkbWorking.SaveAs "C:\Temp\PesentationExcel.xlsx"

wkbWorking.Close   'closes workbook
xlsApp.Quit        'closes excel application

'sets variables to nothing
Set wkbWorking = Nothing
Set xlsApp = Nothing

End Sub
Hronic
  • 155
  • 1
  • 1
  • 7
  • Thank you very much! This is working. However, I did not want the excel file to open. I removed the line `xlsApp.Visible = True` and it is showing that the file is locked for editing. xlsApp.Close is also causing me an error. Also, what did you mean by setting obj to nothing? – Kusa Shaha Mar 12 '20 at 16:27
  • 1
    `xlsApp.Visible = True` -> it does not turn on your excel, it just shows you on the screen `wkbWorking.Close ` 'use this line to close workbook `xlsApp.Quit ` 'use this line to close excel application 'set variables to nothing `Set wkbWorking = Nothing ` `Set xlsApp = Nothing` – Hronic Mar 12 '20 at 16:52
  • Why do we set variables to nothing? What does that accomplish? – Kusa Shaha Mar 12 '20 at 16:55
  • 1
    1. Good practice 2. Generally it should be done by garbage collector but it is said it sucks, so do it manually 3. clears memory and because of that your files are not "occupied" if you want to edit them from windows. – Hronic Mar 12 '20 at 16:56
  • Ah, does this help in getting rid of the "file is locked for editing" ? – Kusa Shaha Mar 12 '20 at 17:00
  • 1
    @KusaShaha - [relevant](https://stackoverflow.com/questions/19038350/when-should-an-excel-vba-variable-be-killed-or-set-to-nothing/19038890), or maybe [this one](https://stackoverflow.com/questions/517006/is-there-a-need-to-set-objects-to-nothing-inside-vba-functions) too. – BigBen Mar 12 '20 at 17:07