If you need to Prompt to open a file in Excel VBA, check out the below code snippet. Please leave us a comment below if you have questions or comments and share this page if you think it can help others!
Sub grabOpenOtherWorkbook() 'declare other workbook so we can use Intellisense (autofill) Dim othWb As Workbook 'prompt for the workbook to Open myFile = Application.GetOpenFilename() If myFile = Empty Then Exit Sub 'open that workbook Set othWb = Workbooks.Open(myFile) End Sub
You can now manipulate the other workbook, because we told Excel that the other workbook will be referred to from now on as othWb, so we can do things we normally would do with a Workbook object, such as:
'Update a Cell Value othWb.range("a1") = "New Value"
'Or Close The Other Workbook othWb.Close SaveChanges:=True
**New To VBA?: In these Excel VBA Posts, you'll need to Open The Visual Basic Editor (VBE), and Make a New Module and then Create A New Macro (Subroutine) in order to paste the code below and use it. Start here ==>Click here for VBA Basics.**