Macro to open the file selector select a document to open then run a macro

[tonywatsonhelp]

Hi everyone,
so i need a bit of help with this complex macro

I want to be able to have a macro that does this

the macro is contained in its own documet along with another called update.
when we run the macro it does this.
opens the file explore and asks the user to select the document they wish to open,
if possible check the selected document is xlsm
open the document (it will need the password "Password1234"
then run the othe macro "update" on the opened document and save it.

please help if you can

thabks

tony

 

[Wookiee]

This worked for me. You'll need to update the drive letter and drive paths in order for the macro to navigate directly to your preferred folder.

Sub OpenFileRunMacro()

'Declare Variables
Dim objExcel       As Object
Dim objMacro       As Object
Dim strMacroFile   As String
Dim varFileName    As Variant

Const STRDRIVELETTER As String = "C:"
Const STRDRIVEPATH As String = "\Example Folder\Subfolder"
Const STRFILTERS As String = "Excel Files (*.xlsm),*.xlsm,"

'Change Drive To Desired Folder Location
ChDrive STRDRIVELETTER
ChDir STRDRIVELETTER & STRDRIVEPATH

'Prompt User To Identify File With Macro To Run
varFileName = Application.GetOpenFilename _
   (FileFilter:=STRFILTERS, _
   FilterIndex:=1, _
   MultiSelect:=False)
strMacroFile = varFileName

'Open Macro File
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

'Run Macro
Set objMacro = objExcel.Workbooks _
   .Open(Filename:=strMacroFile, ReadOnly:=False)


objExcel.Run "HellWorld"

objMacro.Save
objMacro.Close

Set objExcel = Nothing
Set objMacro = Nothing

End Sub