PDA

View Full Version : Macro to ask to find file



cadence24
05-28-2008, 05:11 PM
Hello all,

I am trying to write a macro, so that when a button is clicked it asks the user for a file.

The file path is unknown until the user selects from the dialogue screen.

I don't know how you do that in a mac. I can only get it to go and get the same file rather than ask for a specific path.

A generic one would be enough to get me started, I just don't know the syntax.

thanks johnny

Jan Karel Pieterse
05-28-2008, 09:47 PM
Check out help for Application.GetOpenFilename. a WIndows sample is below, not sure if it works on a MAC:


Sub GetOpenFileNameExample3()
Dim lCount As Long
Dim vFilename As Variant
Dim sPath As String
Dim lFilecount As Long
sPath = "c:\windows\temp\"
ChDrive sPath
ChDir sPath
vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to open", , True)
If TypeName(vFilename) = "Boolean" Then Exit Sub
For lCount = 1 To UBound(vFilename)
Workbooks.Open vFilename(lCount)
Next
End Sub

cadence24
05-28-2008, 10:01 PM
Jan thanks, slight typo by me, what if i want that file to be an image? and that it asks for what image[path?]

mikerickson
05-28-2008, 10:19 PM
This showed me the file path to an image in my iPhoto library.

Dim uiFilePath As String

uiFilePath = Application.GetOpenFilename
If uiFilePath = "False" Then Exit Sub: Rem cancel pressed
MsgBox uiFilePath

Mac OS10.5 Excel2004

Jan Karel Pieterse
05-28-2008, 10:22 PM
You could change the code to look for any file:


Sub GetOpenFileNameExample2()
Dim vFilename As Variant
Dim sPath As String
Dim lFilecount As Long
Dim lCount As Long
sPath = "c:\windows\temp\"
ChDrive sPath
ChDir sPath
vFilename = Application.GetOpenFilename("Image files (*.jpg),*.jpg", , "Please select the file(s) to import", , True)
If TypeName(vFilename) = "Boolean" Then Exit Sub
For lCount = 1 To UBound(vFilename)
DoSomethingWithThisOne CStr(vFilename(lCount))
Next
End Sub

Of course you'd have to write the sub called "DoSomethingWithThisOne" to handle the selected file(s).