can excel vba function open a file?

Question

i'm defining a function to save files as .xls format:

Public Function save_as_xls(full_file_path As String) As String
    save_as_xls = ""

    Dim src_file As Workbook
    Set src_file = Workbooks.Open(full_file_path)
    src_file.SaveAs filename:=full_file_path, FileFormat:=xlExcel8
    src_file.Close

    save_as_xls = "OK"
End Function

then call it in excel cell formula as =save_as_xls("c:\temp\test.xls")

However, it doesn't work, the src_file get Nothing from Workbooks.Open

Is there a limitation on vba functions that cannot open files? I only know that it can't write to other cells.

Check [this answer](https://stackoverflow.com/a/23232311/2165759). — omegastripes, Oct 17 '17 at 05:36
Possibly related [Excel VBA can't open Workbook](https://stackoverflow.com/questions/7693530/excel-vba-cant-open-workbook) (if not *duplicate*) — AntiDrondert, Oct 17 '17 at 07:56
I just added the [excel-udf] tag, as the main part of the question is that this is a function being used as a UDF. However, I'm not 100% sure that I **should** have added the tag, because the tag itself gives the answer to the question. So feel free to remove it again if you want to. (Just click on the [edit history](https://stackoverflow.com/posts/46782402/revisions) link and rollback to the original version.) — YowE3K, Oct 17 '17 at 08:17

omegastripes · Accepted Answer · 2017-10-17T15:11:20.657

5

Excel UDF have certain limitations, so you can't save workbook. You may try a workaround with late bound instance of Excel as shown in the below code.

Put this code to the standard module:

Public objExcel As Application

Public Function SaveAsXls(FilePath As String) As String

    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
        With objExcel
            .Visible = True ' for debug
            .DisplayAlerts = False
        End With
    End If
    With objExcel
        With .Workbooks.Open(FilePath)
            .SaveAs _
                Filename:=FilePath, _
                FileFormat:=xlExcel8
            .Close True
        End With
    End With
    SaveAsXls = "OK"

End Function

Put this code to ThisWorkbook section:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If TypeName(objExcel) = "Application" Then objExcel.Quit

End Sub

So you can call it in Excel cell formula as =SaveAsXls("c:\temp\test.xls")

edited Oct 17 '17 at 15:11

answered Oct 17 '17 at 08:47

omegastripes

12,351
4
45
96

what does this mean? if i put these in A.xlsm, then when A.xlsm is closed, before closing, `Workbook_BeforeClose` will be triggered, how is this to trigger `SaveAsXls`? – athos Oct 17 '17 at 10:53
@athos While the workbook is opened, and once the late bound instance of Excel is created, it remains in memory to improve performance. `Workbook_BeforeClose` just quits that instance of Excel to release memory, and avoid hanging Excel in processes. It's do nothing with `SaveAsXls`. – omegastripes Oct 17 '17 at 12:23
@athos This answer should achieve what you are attempting to do with the code in your question. – YowE3K Oct 17 '17 at 18:54
@YowE3K ah I see. I confused at the quitting part. Thx both! – athos Oct 17 '17 at 21:11

can excel vba function open a file?

1 Answers1

Linked