I'm attempting to put together some code in VBA where it will open a specific folder, let me choose the file then continue running my code.
Currently what I have (below) "works" in that it will open a folder but usually it starts from a generic location (Desktop) but will not go the the specific folder location to let me open the file I want.
Dim Filename as String filename = Application.GetOpenFilename(FileFilter:="Excel Files, *.xl*;*.xm*") If filename <> False Then Workbooks.Open filename:=filename End IfI've also tried something like this:
Dim Directory as String Dim Filename as String Directory = "\\page\data\NFInventory\groups\CID\Retail Setting\Lago Retail Uploads\" & strBrand & "\" & strSeason & "\" & strPrefix & "\" Filename = Dir(Directory & "*.xl*;*.xm*") Workbooks.Open Filename:=DirectoryBut it doesn't do anything and I think I have everything right. Any help or push in the right direction would be greatly appreciated.
-Deke
asked Aug 19, 2020 at 19:43
2
This will start an Open Dialog at the specified location:
Sub openBeckJFolder() With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show .InitialFileName = "C:\Users\beckj\" End With End SubThe Microsoft document page doesn't really get into it, but FileDialog has several features such as the InitialFileName that I used here.
_
UPDATE: To open the workbook
Code added that allows you to highlight the workbook & click Open, or double-click on the workbook to open it.
Sub openBeckJFolder() Dim Filename As String With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False .InitialFileName = "C:\Users\beckj\" If .Show = True Then Filename = .SelectedItems(1) End If End With Workbooks.Open (Filename) End Subanswered Aug 19, 2020 at 20:17
Joe BeckJoe Beck
3811 silver badge12 bronze badges
2
Home ➜ VBA ➜ VBA Open Workbook (Excel File) To open a workbook using VBA, you need to use the “Workbook.Open” method and specify the path of the file (make
sure to specify the full path to the workbook with name and extension file type). This method has a total of fifteen optional arguments which you can use to deal with different kinds of files.
In this tutorial, we will explore it in detail and look at an alternative method that you can use.
Steps to Open a Workbook using VBA
- To start the code, use the “Workbooks” object.
- Type a dot (.) after that and select the Open method from the list.
- Specify the file path in the first argument and make sure to enclose it in double quotation marks.
- In the end, run the code to open the workbook.
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Workbook.Open Syntax
Now it’s time to look at the syntax of the method that you just have used in the above example. As I mentioned, there are fifteen arguments that you can use:
expression.Open (FileName, UpdateLinks, _ ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, _ Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)You won’t be using all these arguments. But a few of them are quite important and could be useful for you in the real world.
Opening a Password Protected Workbook
If you want to open a workbook that is password-protected, in that case, you can specify the password with the password argument.
Here I have a workbook on the desktop that has the password “test123” and now I want to open it and unprotect it at the same time. Following is the code that I need to use.
Workbooks.Open "C:\Users\Dell\Desktop\myFile.xlsx", , , Password:="test123"Opening a Workbook as Read Only
When you open a workbook as read-only you can’t make changes to the same workbook, but you need to save a copy of it.
Workbooks.Open "C:\Users\Dell\Desktop\Folder\1.xlsx", , TrueOpen All the Workbooks from a Folder
Sub vba_open_multiple_workbooks_folder() Dim wb As Workbook Dim strFolder As String Dim strFile As String strFolder = "C:\Users\Dell\Desktop\Folder\" strFile = Dir(strFolder & "*.xls*") Do While strFile <> "" Set wb = Workbooks.Open(strFolder & strFile) strFile = Dir Loop End SubTo use it as per your needs, make sure to change the folder path.
Sub vba_open_dialog() Dim strFile As String strFile = Application.GetOpenFilename() Workbooks.Open (strFile) End SubMore on VBA Workbooks
VBA Save Workbook | VBA Close Workbook | VBA Delete Workbook | VBA ThisWorkbook | VBA Rename Workbook | VBA Activate Workbook | VBA Combine Workbook | VBA Protect Workbook (Unprotect) | VBA Check IF a Workbook is Open | VBA Check IF an Excel Workbook Exists in a Folder| VBA Create New Workbook (Excel File)
- VBA Workbook