Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
Hi there -
I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
df_five.worksheets(1).range(c1:ba1009).copy _
rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2) -- Regards, Tom Ogilvy "NewBike" wrote: Hi there - I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
For what you are trying to do you need to get you head around 3 different
types of objects. Workbook, Worksheet and Range. A workbook contains 1 or more worksheets and a worksheet contains ranges. A range is a group of 1 or more cells... In VBA we can Set (note the key work set since we are dealing with objects) objects that will essentially point to the 3 different kinds of objects. We can create as many objects as are necessary to do the voodoo we want to do. Here is a very quick example... Dim wbkDataFileOne as Workbook Dim wbkReportFileOne as Workbook dim wksFrom as worksheet dim wksDestination as Worksheet dim rngFrom as range dim rngDestination as range Set wbkDataFileOne = Workbook("dataOne.xls") Set wbkReportFileOne = Workbook ("reportone.xls") set wksfrom = wbkDataFileOne.sheets("Sheet1") set wksDestination = wbkReportFileOne .sheets("Sheet1") set rngfrom = wksfrom.range("A1:B2") set rngDestination = wksdestination.range("A1") rngfrom.copy Destination:=rngDestination set rngfrom = wksfrom.Range("B3:C4") rngfrom.copy rngdestination.offset(5,5).pasteSpecial xlFormats Note that we can repoint the objects as we go by SETting (pointing) them to different books, sheets or ranges... By properly declaring your objects the intellisense dropdown will function as you write your code so you will know all of the appropriate preperties and methodes of each object... -- HTH... Jim Thomlinson "NewBike" wrote: Hi there - I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
Hi Tom -
Thanks, but when I enter the following code: rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents I get a Compile Error: Invalid qualifier Sorry if I am being a ditz on this, I am on a VBA crash course right now LOL!! "Tom Ogilvy" wrote: df_five.worksheets(1).range(c1:ba1009).copy _ rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2) -- Regards, Tom Ogilvy "NewBike" wrote: Hi there - I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
Thanks Jim.
So, I can redirect or reSET the properties along the way which is going to be a huge help when selecting ranges! With this particular code, I am declaring my workbooks in advance since the macro bounces back and forth so much - that is ok isn't it? Then I only need reset the ranges as I go. But, I am still getting an error as noted in my response to Tom, and don't understand why...or did I jst read the answer and totally miss it? LOL. "Jim Thomlinson" wrote: For what you are trying to do you need to get you head around 3 different types of objects. Workbook, Worksheet and Range. A workbook contains 1 or more worksheets and a worksheet contains ranges. A range is a group of 1 or more cells... In VBA we can Set (note the key work set since we are dealing with objects) objects that will essentially point to the 3 different kinds of objects. We can create as many objects as are necessary to do the voodoo we want to do. Here is a very quick example... Dim wbkDataFileOne as Workbook Dim wbkReportFileOne as Workbook dim wksFrom as worksheet dim wksDestination as Worksheet dim rngFrom as range dim rngDestination as range Set wbkDataFileOne = Workbook("dataOne.xls") Set wbkReportFileOne = Workbook ("reportone.xls") set wksfrom = wbkDataFileOne.sheets("Sheet1") set wksDestination = wbkReportFileOne .sheets("Sheet1") set rngfrom = wksfrom.range("A1:B2") set rngDestination = wksdestination.range("A1") rngfrom.copy Destination:=rngDestination set rngfrom = wksfrom.Range("B3:C4") rngfrom.copy rngdestination.offset(5,5).pasteSpecial xlFormats Note that we can repoint the objects as we go by SETting (pointing) them to different books, sheets or ranges... By properly declaring your objects the intellisense dropdown will function as you write your code so you will know all of the appropriate preperties and methodes of each object... -- HTH... Jim Thomlinson "NewBike" wrote: Hi there - I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Incative Workbooks
Dim rpt_Six as Workbook
set rpt_Six = Workbooks("reportone.xls") ' not "s" in Workbooks rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents would be my guess. -- regards, Tom Ogilvy "NewBike" wrote in message ... Hi Tom - Thanks, but when I enter the following code: rpt_SIX.Worksheet("EASTCST").Range("A4:k8000").Cle arContents I get a Compile Error: Invalid qualifier Sorry if I am being a ditz on this, I am on a VBA crash course right now LOL!! "Tom Ogilvy" wrote: df_five.worksheets(1).range(c1:ba1009).copy _ rpt_six.worksheets(1).cells(rows.count,1).End(xlup )(2) -- Regards, Tom Ogilvy "NewBike" wrote: Hi there - I am trying to make some code more efficient. Currently, I have a workbook that is opened via MS Scheduler, a Workbook_Open Event starts a macro that opens approximately 7 report workbooks and updates those with approx 16 data files. According to what I have read, it is much more efficient to leave as many files as possible inactive and just pull the data. However, I am at a loss as to the proper way to focus on the inactive workbooks as I need them. I have Dim'd them all as: 'data files Dim df_One as string Set df_One = Workbook("dataOne.xls") 'onward til all are named 'report files Dim rpt_One as String Set rpt_One = Workbook ("reportone.xls") 'and so on Now lets say I want to work with data from df_five.range(c1:ba1009) copy it to rpt_six, then get data from df_two.range (a2:c1010) and copy it to rpt_six. What is the most efficient way to accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with 2 workbooks | Excel Programming | |||
Working with Different workbooks | Excel Discussion (Misc queries) | |||
Working with Different workbooks | Excel Discussion (Misc queries) | |||
Working with two workbooks | Excel Programming | |||
Working with Two WorkBooks | Excel Programming |