Opening a file through a macro - Help!!

[Matt]

I have a question in opening a file from a macro used in Excel. I want the
file open to be flexible no matter what machine the Excel file is opened on,
but not sure how to do it. I've never learned VB in a class or anything and
have been learning it on my own. So, any help would be greatly appreciated.
here is the code I have written thus far (it is a conditional operation):

Sub Macro6()


Sheets("Main Menu").Select

If Range(" T11 ").Value = "2" Then


If Range("W11").Value = "0" Or Range("W11").Value = "1" Or
Range("W11").Value = "3" Then
ChDir _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports\Updated Responder Data.xls"

Sheets("Responders").Visible = True
Sheets("Responders").Select

Range("AU2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AV2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If

If Range("W11").Value = "2" Then
Windows("Updated Responder Data.xls").Activate


Sheets("Responders").Visible = True
Sheets("Responders").Select

Range("AU2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AV2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If

Windows("Master Aggregate Response Report.xls").Activate

Sheets("Response Pivot Table").Visible = True
Sheets("Response Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Response Pivot Table").Visible = False
Sheets("Main Menu").Select
End If

If Range(" T11 ").Value = ("1" Or "3") Then

Sheets("Response Pivot Table").Visible = False

End If

End Sub




So, obviously I don't want it to follow the path as written so that another
person can perform the macro at a different machine....

 

[Tom Ogilvy]

You look for the file here:

C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports\Updated Responder Data.xls

Will the path always be the same? If not will it always be the same except
for the "Rob" portion. If not, then how would you find it manually.

Why do you use spaces like this:
If Range(" T11 ").Value = "2" Then

Just use Range("T1")

toward the bottom you are always testing if Range("T1") = 3

with the code: If Range(" T11 ").Value = ("1" Or "3") Then

if you want to test whether it contains 1 or 3 you must do

If Range("T11").Value = 1 Or Range("T11").Value = 3 Then

 

[JorgeR]

Hi Matt,
what I did, is leave the directory address in a cell, in a worksheet where
your macro is, so that everyone who wants to use your file, have their own
address in that book.
Example:
Your machine:
Cell A4 value: "C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"
Somebody else:
Cell A4 value: "C\Jorge\Rob"
and in the macro line
Workbooks.Open Filename:= _
ActiveSheet.Range("A4").Value & "Updated Responder Data.xls"
erase this line (not necesary):
ChDir _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"


You can use a

 

[Matt]

Tom,
Thank you much for the response.

As far as the file reference, the path won't always be the same....that was
where the issue would be....I might want a client to be able to run the code,
and obviously on their machine the path would be different. But, this is to
create more automated reporting, so I don't want to have to select manually
if it can be helped.

As far as the spaces, just an old programming bad habit of mine (I use to
program in other languages and like to space my stuff out).

Thanks for the help on the if-then logic as well

 

[Matt]

Great idea Jorge....thank you

Hi Matt,
what I did, is leave the directory address in a cell, in a worksheet where
your macro is, so that everyone who wants to use your file, have their own
address in that book.
Example:
Your machine:
Cell A4 value: "C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"
Somebody else:
Cell A4 value: "C\Jorge\Rob"
and in the macro line
Workbooks.Open Filename:= _
ActiveSheet.Range("A4").Value & "Updated Responder Data.xls"
erase this line (not necesary):
ChDir _
"C:\Documents and Settings\Rob\Desktop\Clients\Stearns
Mortgage\Response Reports"


You can use a

 

[Tom Ogilvy]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office09072000.asp
Working with Files, Folders, and Drives: More VBA Tips and Tricks
by David Shank (Office talk)




http://support.microsoft.com/kb/185476/EN-US/
How To Search Directories to Find or List Files

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

http://support.microsoft.com/kb/186118/EN-US/
How To Use FileSystemObject with Visual Basic