Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
Helen,
Give yourself a WB variable to work with afterwards. e.g. Dim SourceWS as Workbook Set SourceWS=Workbooks.Open Filename:=mySourceFile Then you can use SourceWS.Worksheets(1).range... without caring what any actual names are. You could also incorporate a call to Application.GetOpenFileName, so you do not have to have any hard coded file names. NickHK "Helen" wrote in message ... I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
Nick,
I've copied what you've written below letter for letter but it's telling me that there's a syntax error on the following line: Set SourceWS=Workbooks.Open Filename:=mySourceFile "NickHK" wrote: Helen, Give yourself a WB variable to work with afterwards. e.g. Dim SourceWS as Workbook Set SourceWS=Workbooks.Open Filename:=mySourceFile Then you can use SourceWS.Worksheets(1).range... without caring what any actual names are. You could also incorporate a call to Application.GetOpenFileName, so you do not have to have any hard coded file names. NickHK "Helen" wrote in message ... I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
Helen,
I was too quick with the Copy/Paste. Yes, now that you are using the return value from Workbooks.Open, the arguments need to in brackets. So: Set SourceWS=Workbooks.Open (Filename:=mySourceFile) NickHK "Helen" wrote in message ... Nick, I've copied what you've written below letter for letter but it's telling me that there's a syntax error on the following line: Set SourceWS=Workbooks.Open Filename:=mySourceFile "NickHK" wrote: Helen, Give yourself a WB variable to work with afterwards. e.g. Dim SourceWS as Workbook Set SourceWS=Workbooks.Open Filename:=mySourceFile Then you can use SourceWS.Worksheets(1).range... without caring what any actual names are. You could also incorporate a call to Application.GetOpenFileName, so you do not have to have any hard coded file names. NickHK "Helen" wrote in message ... I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
Thanks Nick
It asks Excel to Open the file when you Set SourceWS. Is there a way that I can create a variable like this but without using the Open / Close functions? I would like to be able to apply this to the file containing the macro too, but as the file is already open when it's being run, using Set HomeWS=Workbooks.Open(Filename....) will prompt Excel to want to reopen and give the save / no save message box. Which I would like to avoid. "NickHK" wrote: Helen, I was too quick with the Copy/Paste. Yes, now that you are using the return value from Workbooks.Open, the arguments need to in brackets. So: Set SourceWS=Workbooks.Open (Filename:=mySourceFile) NickHK "Helen" wrote in message ... Nick, I've copied what you've written below letter for letter but it's telling me that there's a syntax error on the following line: Set SourceWS=Workbooks.Open Filename:=mySourceFile "NickHK" wrote: Helen, Give yourself a WB variable to work with afterwards. e.g. Dim SourceWS as Workbook Set SourceWS=Workbooks.Open Filename:=mySourceFile Then you can use SourceWS.Worksheets(1).range... without caring what any actual names are. You could also incorporate a call to Application.GetOpenFileName, so you do not have to have any hard coded file names. NickHK "Helen" wrote in message ... I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Window Object - Subscript out of Range
Nick.
Thanks for your help... I've worked out the rest: Dim HomeWS HomeWS = "PM&E Man Hour Report 253 draft.xls" Windows(HomeWS).Activate Thanks again. "Helen" wrote: Thanks Nick It asks Excel to Open the file when you Set SourceWS. Is there a way that I can create a variable like this but without using the Open / Close functions? I would like to be able to apply this to the file containing the macro too, but as the file is already open when it's being run, using Set HomeWS=Workbooks.Open(Filename....) will prompt Excel to want to reopen and give the save / no save message box. Which I would like to avoid. "NickHK" wrote: Helen, I was too quick with the Copy/Paste. Yes, now that you are using the return value from Workbooks.Open, the arguments need to in brackets. So: Set SourceWS=Workbooks.Open (Filename:=mySourceFile) NickHK "Helen" wrote in message ... Nick, I've copied what you've written below letter for letter but it's telling me that there's a syntax error on the following line: Set SourceWS=Workbooks.Open Filename:=mySourceFile "NickHK" wrote: Helen, Give yourself a WB variable to work with afterwards. e.g. Dim SourceWS as Workbook Set SourceWS=Workbooks.Open Filename:=mySourceFile Then you can use SourceWS.Worksheets(1).range... without caring what any actual names are. You could also incorporate a call to Application.GetOpenFileName, so you do not have to have any hard coded file names. NickHK "Helen" wrote in message ... I want to be able to use my macro for lots of different projects, so the filenames that I use throughout the macro need to be easy to change. So I've set them up at the beginning of the Macro so they're easy to identify using the following sequence: myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project Services\03 Man Hour Reports\" mySourceFileBase = ActiveWorkbook.Worksheets("Imported Data").Range("F5").Value mySourceFile = myPath & mySourceFileBase So I can put the file name that I want to import data from in the workbook containing the macro and hopefully not have to open the VBE to alter the file references. and it's used it correctly in this line: Workbooks.Open Filename:=mySourceFile but after I've activated a different window, I can't get the syntax right for activating the other window that mySourceFile refers to. It wants the file name reference ("RF Man Hours Report 0506.xls"). Is there any way that I can use mySourceFile as a window object? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Programming | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
Subscript out of range | Excel Programming | |||
subscript out of range | Excel Programming | |||
Subscript out of range | Excel Programming |