Help with paths

[Albert Browne]

Hi.


Do I need to use the full path in VBA code i.e.

GetSheet = "FTP " & DailyDate
Set wb = Workbooks.Open("C:\Users\***\***\****\Archives")
Windows("Archives").Activate
Sheets(GetSheet).Select
Sheets(GetSheet).Copy After:=Workbooks("Daily Reports.xls").Sheets(1)
wb.Close False


this if fine while under development on one pc. The code will have a number
of workbooks in one folder on a network. It will be accessed by a number of
pc's. can I shorten this path so that it just looks in the folder where all
the workbooks will be stored?

Thanks,

Albert

 

[Chip Pearson]

If you omit the path information from the file name, the Open procedure will
look in the current default directory. You can retrieve the full path of
the current directory with

Dim S As String
S = CurDir
MsgBox S

You can change the current directory with code like

ChDrive "G:\Test"
ChDir "G:\Test"


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gary''s Student]

If all the workbooks, including the one with the VBA, are stored in the same
folder, then you can use the path of the active workbook:

Sub browne()
s1 = ActiveWorkbook.Path
s2 = "\B.xls"
Workbooks.Open s1 & s2
End Sub

will open b.xls in the common folder.