Simple? Macro to Open a file?

[Paperclipper]

this sounds simple, but i havent got a clue!

the path will change for different machines i load the workbk on... so how do i code to open up the source workbks in either the current dir or a subfolder within it?


Sub OpenUp()

ChDir _
"S:\Contracts\Contract Data Sources"
Workbooks.Open Filename:= _
"S:\Contracts\Contract Data Sources\Contracts Breakdown 1980.xls" _
, UpdateLinks:=0

End Sub


This has got me baffled!

 

[Juan Pablo González]

You can get the directory of the workbook with

Dim sDir As String
sDir = ThisWorkbook.Path

 

[Paperclipper]

thanks! but now that i passed the path into sDir, how can i open the files from that directory?

do i still need to change into that directory? or can i just do the opens?

sorry, i'm not advanced enough yet...

 

[Smitty]

Workbooks.Open Filename:= _
sDir & "\Contracts Breakdown 1980.xls" _
, UpdateLinks:=0

HTH,

Smitty

 

[Paperclipper]

WINNER!

Juan Pablo González and Smitty did it!

thanks guys! (now all i have to do is figure out how to loop through all twenty some odd files - arrangged by dates! so i guess a do while loop that increments?)

 

[Paperclipper]

Re: Simple? Macro to Open/close a file?

Here is the code if anyone's interested!

(any clue as to how to close now? i tried to replace the "open" with "close", but it didnt work!)

Sub OpenUp()
'
'Declare variable and get current path
Dim sDrive As String
sDrive = ThisWorkbook.Path

Dim Paranoia, idx
Paranoia = True
idx = 1980

Do
Do While idx < 1985
Workbooks.Open Filename:= _
sDrive & "\Contract Data Sources\Contracts Breakdown " & idx & ".xls" _
, UpdateLinks:=0

idx = idx + 1

'this sets flag for Paranoia (double-check) and exits do while loop!
If idx = 1985 Then
Paranoia = False
Exit Do
End If
Loop

'another check!
Loop Until Paranoia = False

 

[Paperclipper]

Re: Simple? Macro to Close a file?

how do i close now?

i would like to use the same code as to open, but simply replacing the "open" with "close" doesnt work...

i want to only close the ones specified in the loop... (in case there are other files open!) please help!

 

[Juan Pablo González]

Try with

ActiveWorkbook.Close SaveChanges:=False 'or True if you want to save them

 

[Paperclipper]

Juan, that wont apply here because i have to tell excel which of the files needs to be closed... i dont want all files open to close either...