Kate has attended:
Access Introduction course
Access Intermediate course
Referencing a workbook in a macro
I would like to set up a macro for other people in my company to use.
The macro will depend on the location of a file they need to download from a website. They will then name it something relevant like "client1data". It will be either a csv or excel file (i have not decided yet which will be best).
To make it easier for my colleagues to find the file they would like to use, i would like to use some kind of functionality like the "file open" or "hyperlink" options, whereby a window pops up and you can search through folders to find your file. I was then hoping to use the location of the file which is returned in a macro which would copy data from their file to my Master file.
Is there a way to do this which does not involve my colleagues having to type in the location of the file manually?
Many thanks,
Kate
RE: Referencing a workbook in a macro
Hi Kate
Thanks for your question
The following code opens the open file dialog and then opens the selected workbook
Sub OpenFile()
Dim varItem As Variant
Dim strPath As String
Dim selectedFile As String
Dim filePicker As FileDialog
Set filePicker = Application.FileDialog(msoFileDialogOpen)
With filePicker
'setup File Dialog'
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
.Title = "Select File"
.InitialFileName = "C:\"
.Show
End With
selectedFile = filePicker.SelectedItems(1)
Application.Workbooks.Open selectedFile
End SubHope this is useful
Regards
Stephen
RE: Referencing a workbook in a macro
That's brilliant, thanks!
Is there then a way to save the name of this file anywhere.
For example if i wanted to reference this file later in my macro (which happens in another workbook) can i save the name of the selected file?
Kind regards,
Kate
