Referencing a workbook in a macro

Resolved · Urgent Priority · Version 2007

Tue 17 Aug 2010, 15:40 Delegate 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

Wed 18 Aug 2010, 11:34 Trainer Stephen said...

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

Wed 18 Aug 2010, 12:20 Delegate 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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.