| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a file through a macro - Help!!
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.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file through a macro - Help!!
You look for the file he
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 -- Regards, Tom Ogilvy "Matt" wrote: 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.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file through a macro - Help!!
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" wrote: 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.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file through a macro - Help!!
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 "Tom Ogilvy" wrote: You look for the file he 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 -- Regards, Tom Ogilvy "Matt" wrote: 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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file through a macro - Help!!
Great idea Jorge....thank you
"JorgeR" wrote: 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" wrote: 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.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening a file through a macro - Help!!
http://msdn.microsoft.com/library/de...ce09072000.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 -- Regards, Tom Ogilvy "Matt" wrote: 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 "Tom Ogilvy" wrote: You look for the file he 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 -- Regards, Tom Ogilvy "Matt" wrote: 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.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a macro be run on opening the file? | Excel Discussion (Misc queries) | |||
| File opening via Macro with varying file name | Excel Programming | |||
| opening ftp-file from macro | Excel Programming | |||
| Opening a file with a Macro | Excel Discussion (Misc queries) | |||
Linear Mode
