Simple? Macro to Open a file?

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
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

Legend
Joined
May 15, 2003
Messages
29,536
Workbooks.Open Filename:= _
sDir & "\Contracts Breakdown 1980.xls" _
, UpdateLinks:=0

HTH,

Smitty
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110

ADVERTISEMENT

WINNER! (y)

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

Board Regular
Joined
Mar 11, 2004
Messages
110
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

Board Regular
Joined
Mar 11, 2004
Messages
110

ADVERTISEMENT

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!
 

Paperclipper

Board Regular
Joined
Mar 11, 2004
Messages
110
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...
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top