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

C:\CABD\CABD.xls

Putting the Workbooks.Open Filename statement is easy enough when it i
always one location but several of the files will require having
location that varies by site. I do not want to have to redo the cod
for each site and each site has a different directory name (ex. Y: o
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 o
you experts out there point me in the right direction. The incorrec
code is stated below...

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

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

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

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

Thank you very much for a very thorough answer. Is there a way to ge
a particular cell's value on my spreadsheet to be the default? In othe
words, a value not hardcoded into the VBA

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

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

Hi Croney,

One way is to have a datasheet in the workbook where the macros are and
store the name of the file as in:

1. name the sheet "data"
2. in column 1 give names
3. say a5="File to read"
4. say B5="C:\CABD\CABD.xls"

Ok this has set up the data, we now need the code:

In a code module I call it globals enter the following:

' the name of the sheet where the filename is
Public Const cszDataSheet As String = "data"
' the cell where the filename is
Public Const cszCellFileNameIn As String = "B5"

This sets up the references to the the data sheet and the cells where the
data are.

Then in your code:

Dim CWDBXLS As String
CWDBXLS = ThisWorkbook.Worksheets( _
cszDataSheet).Range(cszCellFileNameIn)
Workbooks.Open Filename:=CWDBXLS

or :

Workbooks.Open Filename:=ThisWorkbook.Worksheets( _
cszDataSheet).Range(cszCellFileNameIn)


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"croney" wrote:


Thank you very much for a very thorough answer. Is there a way to get
a particular cell's value on my spreadsheet to be the default? In other
words, a value not hardcoded into the VBA.


--
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

Thank you all for your hel

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

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