Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Referencing a workbook in a macro
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Referencing a workbook in a macro
Resolved · Urgent Priority · Version 2007
Reply Tue 17 Aug 2010, 15:40Delegate Kate said...
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
For upcoming training course dates see: Pricing & availability
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 Sub
Hope this is useful
Regards
Stephen
Reply Wed 18 Aug 2010, 12:20Delegate Kate said...
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
|