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:

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

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?

Can you explain the overall purpose of this? What are you trying to achieve with that? Why don't you just copy that file instead of open and save as? — Pᴇʜ
– Pᴇʜ, Commented Feb 28, 2018 at 11:07

Cindy Meister · Accepted Answer · 2018-02-28 14:07:57Z

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

Collectives™ on Stack Overflow

Open other application from vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related