You could prompt the user to select a folder, and then loop through all the
XLS files in that folder. For example, you can use the following code.
You'll need a reference to "Microsoft Shell Controls And Automation". In
VBA, go to the Tools menu, choose References, and scroll down to and check
"Microsoft Shell Controls And Automation".

Option Explicit
Option Compare Text
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Function BrowseFolder(Optional Caption As String, _
Optional InitialFolder As String) As String

Dim SH As Shell32.Shell
Dim F As Shell32.Folder

Set SH = New Shell32.Shell
Set F = SH.BrowseForFolder(0&, Caption, BIF_RETURNONLYFSDIRS, _
InitialFolder)

If Not F Is Nothing Then
BrowseFolder = F.Items.Item.Path
End If

End Function

Sub DoWorkbooks()

Dim SaveDir As String
Dim FileName As String
Dim FolderName As String
Dim WB As Workbook

''''''''''''''''''''''''''''''''''''
' Prompt the user for a folder name.
''''''''''''''''''''''''''''''''''''
FolderName = BrowseFolder(Caption:="Select A Folder", _
InitialFolder:="C:\")
If FolderName = vbNullString Then
'''''''''''''''''''''''''''
' User cancelled. Get out.
'''''''''''''''''''''''''''
Exit Sub
End If
'''''''''''''''''''''''''''''''''''''
' Save the current directory setting.
'''''''''''''''''''''''''''''''''''''
SaveDir = CurDir
''''''''''''''''''''''''''''''''''''
' Change the default directory to
' the folder selected by the user.
''''''''''''''''''''''''''''''''''''
ChDrive FolderName
ChDir FolderName
''''''''''''''''''''''''''''''''''''
' Get all the XLS files in the
' current directory.
''''''''''''''''''''''''''''''''''''
FileName = Dir("*.xls")
Do Until FileName = vbNullString
''''''''''''''''''''''''''''''''''''''''
' Open the filename returned by Dir.
' Note that you cannot with this code
' expect filenames to be returned in any
' particular order.
'''''''''''''''''''''''''''''''''''''''
Set WB = Workbooks.Open(FileName:=FileName)

''''''''''''''''''''''''''''''''''''''''''''
' Do something with WB
''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''
' Save and close WB
''''''''''''''''''''
WB.Close savechanges:=True
'''''''''''''''''''''''
' Get the next filename
'''''''''''''''''''''''
FileName = Dir()
Loop

'''''''''''''''''''''''
' Restore directory settings.
'''''''''''''''''''''''
ChDrive SaveDir
ChDir SaveDir

End Sub

If you need to prompt only for an existing filename, use the follow code
snippet:

Dim FName As Variant
Dim WB As Workbook
FName = Application.GetOpenFilename("Excel Files *.xls,*.xls")
If FName = False Then
Exit Sub
End If
Set WB = Workbooks.Open(FileName:=FName)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"croney" wrote in message
...

I am writing a program for use at a bunch of sites around the world. It
uses several SAP extracts that are to be stored on the C: drive of the
user in a specific location, in this case

C:\CABD\CABD.xls

Putting the Workbooks.Open Filename statement is easy enough when it is
always one location but several of the files will require having a
location that varies by site. I do not want to have to redo the code
for each site and each site has a different directory name (ex. Y: or
Q:). I know I need to use a string but am new to the world of strings.
I am not sure no the syntax necessary to make this work. Can any of
you experts out there point me in the right direction. The incorrect
code is stated below...

Dim CWDBXLS
CWDBXLS = '[CABD.xls]Local Data Copy!R46C5
Workbooks.Open Filename:=CWDBXLS


--
croney
------------------------------------------------------------------------
croney's Profile: http://www.officehelp.in/member.php?userid=5986
View this thread: http://www.officehelp.in/showthread.php?t=1318743

Posted from - http://www.officehelp.in