Open other application from vba

Question

I am working on a macro to open a file(might already be open) and save with new name and then open the new file from vba in excel.

This file can Powerpoint,mathcad,visio, word etc..(can also be template files such as dotx etc..)

So my idea is that:

1. I first need to figure out if the application is open or not,
2. then I somehow need to figure if the file is open or not,
3. then save it with the new filename.
4. Open the new document
5. Go through the document and dumps custom variables into the database, populate custom variables from database(Not shown in code below, seperate module)

6. Activate the new document so that the user can edit it.

   Public Sub saveAsVBADocument(filenameNew As String, fileNameOld As String, applicationType As String)
   Dim objectApplication As Object
   Dim documentApplication As Object
   
   On Error Resume Next
   Set objectApplication = GetObject(, applicationType)
   On Error GoTo 0
   
   If objectApplication Is Nothing Then
       Set objectApplication = CreateObject(applicationType)
   End If
   
   
   objectApplication.Visible = True
   
   
   On Error Resume Next
   Set documentApplication = objectApplication.Workbooks(FileHandling.GetFilenameFromPath(fileNameOld)) 'Excel
   Set documentApplication = objectApplication.Documents(FileHandling.GetFilenameFromPath(fileNameOld)) 'Word
   Set documentApplication = objectApplication.WorkSheets(FileHandling.GetFilenameFromPath(fileNameOld)) 'Mathcad
   Set documentApplication = objectApplication.Presentations(FileHandling.GetFilenameFromPath(fileNameOld)) 'PowerPoint
   Set documentApplication = objectApplication.Projects(FileHandling.GetFilenameFromPath(fileNameOld)) 'MS Project "Msproject.Application"
   Set documentApplication = objectApplication.Documents(FileHandling.GetFilenameFromPath(fileNameOld)) 'MS Visio "Visio.Application"
   
   
   If documentApplication Is Nothing Then
       Set documentApplication = objectApplication.FileOpen(fileNameOld) ' add  read only
   End If
   
   documentApplication.SaveAs filename:=filenameNew
   
   Set objectApplication = Nothing
   Set documentApplication = Nothing
   
   End Sub
   

What is a possible solution to handle all vba acceptable document types?

Answer 1

You can use GetObject("Filename") to open a file directly in its application. So something like this can open any file that has its extension in the Windows Registry. That will be most file types; certainly the Office applications. Whether you'll be able to use SaveAs will depend on whether those applications support OLE Server (meaning they have a coding interface exposed). Again, all the Office applications do support this.

You'll probably want to put in some error-handling for the case the application for the file extension can't be found in the Registry. And of course in case the file name doesn't exist.

My example is for Excel and Word, only - you should be able to fill in others. My code makes sure the file is visible and available to the user as that makes it easier to trouble-shoot. You can, of course, change that once you have everything working satisfactorily.

Sub OpenFileInUnknownApp()
    Dim objFile As Object
    Dim objApp As Object
    Dim sPath As String, sExt As String
    Dim sFileName As String
    Dim sAppName As String
    Dim snewfilename As String

    sPath = "C:\Test\"
    sFileName = sPath & "Quote.docx" 'RngNames.xlsx"
    snewfilename = sPath & "NewName"

    '''Open the file in its application
    Set objFile = GetObject(sFileName)
    Set objApp = objFile.Application
    sAppName = objApp.Name

    Select Case sAppName
        Case Is = "Microsoft Excel"
            Dim wb As Excel.Workbook
            sExt = "xlsx"
            objApp.Visible = True
            Set wb = objFile
            wb.Activate
            wb.Windows(1).Visible = True
            objApp.UserControl = True 'so that it "lives" after the code ends
            objApp.Activate
            wb.SaveAs "sNewFileName" & sExt
        Case Is = "Microsoft Word"
            Dim doc As word.Document
            sExt = "docx"
            objApp.Visible = True
            Set doc = objFile
            objApp.Activate
            doc.SaveAs2 "sNewFileName" & sExt
        Case Else
    End Select
    Set objFile = Nothing
    Set objApp = Nothing
End Sub