Excel VBA

[Theresa]

Hello:

I have a macro that goes to the My Documents folder and
opens a specific file. Is there a way that I can enter a
parameter for the file name, so that I can have the macro
open any filename that I specify?

Thanks for your help.

Theresa

 

[pfsardella]

This will allow you to select a file to open. You'll need to add
error handling. See VBE help for options with GetOpenFilename.

Dim strF as String
strF = Application.GetOpenFilename
Workbooks.Open FileName:=strF

HTH
Paul

 

[steve]

Theresa,

Here's some code ('stolen' from this group) that allows opening multiple
files from a folder. (check the notes...) The code can be shortened to use
input boxes or forms for the path and file name variables.

Sub OpenMyFile()
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
Dim pth As String
Dim wbnm As String

'Open *.xls

Application.DisplayAlerts = False
Application.ScreenUpdating = False

On Error Resume Next
pth = ActiveWorkbook.path ' sets directory to current book path
' change pth as needed...
ChDir pth
On Error GoTo 0

GetFiles = Application.GetOpenFilename _
(FileFilter:="Excel Files (*.xls),*.xls", _
Title:="Select Files To Open", MultiSelect:=True)
' allows selection of multiple files
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
'' GetFiles is Array of Strings (File Names)
'' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
' List Files in Immediate Window
Workbooks.Open FileName:=GetFiles(iFiles)

Next
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub