Help: Capture file location on the first opening of a workbook/workbook open event

[anish.ms]

Dear Experts,

I have the following 2 workbooks (1) Checklist and (2) Database. I wanted to open the Workbook("Database") when I open the Workbook("Checklist"). Currently, I have hard-coded the database location in the workbook open event. Is there any way by which the database location can be captured in the workbook open event by using an input box when a user opens the Workbook("Checklist") for the first time?


Private Sub Workbook_Open()
LPDBFile = "https://abccompany-my.sharepoint.com/personal/anish_ms_abccompany_com/Documents/Test/LP%20Database.xlsm"
Set LPDBWBook = Application.Workbooks.Open(Filename:=LPDBFile, ReadOnly:=False)
End Sub


Thanks in advance!

[p45cal]

You can store a default file name in a cell on a sheet somewhere. For example you could have a sheet called Control and have cell A1 contain the file name and path. You might want to hide the sheet.
Get the code to see if there's anything in that cell, and if there is, use it, but if there isn't use Application.GetOpenFilename to get the user to pick the file. As soon as he does that his pick goes into that cell A1.
Perhaps the likes of:
Private Sub Workbook_Open()
LPDBFile = Me.Sheets("Control").Range("A1").Value
If Len(LPDBFile) = 0 Then
LPDBFile = Application.GetOpenFilename("Excel Files, *.xls*")
If LPDBFile <> False Then Me.Sheets("Control").Range("A1").Value = LPDBFile
End If
Set LPDBWBook = Application.Workbooks.Open(Filename:=LPDBFile, ReadOnly:=False)
End Sub
Here it will complain if cell A1 is empty AND the user cancels out of picking a file, but you get the gist.
Consider also using Application.FileDialog(msoFileDialogFilePicker) instead of GetOpenFilename.
To reset the filename, just delete cell A1.

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)