Creating a button to open a file

[ktpack]

I will regularly be downloading new Excel spreadsheets to my desktop
but will need to run the same macro (that i have recently created) o
each file.

What I'm thinking is that I will create a file on my desktop that ha
the macro saved there, plus a button on Sheet 1 that will allow th
user (sometimes a coworker who doesn't understand this stuff) to jus
go get the downloaded file and pull it into that doc. Then, the use
could use the keyboard shortcut to run the macro.

Any suggestions on how to create this button and the underlying VB t
get the user to the "Open file" area?

Thanks

 

[ktpack]

top.

Can no one help with this

 

[Dave Peterson]

How about recording a macro when you do it once manually?

Then you could edit that macro to make it more generic.

When you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.Open Filename:="C:\My Documents\excel\book2.xls"

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Excel files, *.xls", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.Open Filename:=myFileName '....rest of recorded code here!

End Sub