Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Workbook in VBA
HI all,
Practically zero experience with VBA, so bear with me. I'm trying to create a macro that will update our client data. Problem is the names of the workbooks is constantly changing. In SPSS, we are running a huge data dump for all of our clients along with several ratings variables for each. I wrote a macro that works taking this data dump, formating it and pasting it into the correct location of our overall spreadsheet. Everything works fine up to this point. What I'd like to do is create spreadsheets for each client and with only their applicable ratings. I have no problem going in by hand in the overall spreadsheet, manually deleting other clients and blank ratings then doing a save as client_xyz. The question is, when the macro comes with spreadsheet client_xyz, is there a way to way that spreadsheet update instead of the overall? I'd really prefer not to have to go into each individual one and change the macro. This is what I originally started with: Sub auto_open() ' ' auto_open Macro ' Macro recorded 3/6/2008 by backstation2 ' ' ChDir "M:\DATA\MERGE\0708\graphs" Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" Windows("allmeans.dbf").Activate Selection.CurrentRegion.Select Selection.Copy Windows("overall.xls").Activate Sheets("Means").Activate Range("C5").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Windows("allmeans.dbf").Activate Workbooks("allmeans.dbf").Close SaveChanges:=False End Sub The dbf file is the data dump and the overall.xls is the overall data containing all clients. Is there maybe code that I could use to replace the Windows("overall.xls").Activate that looks like Windows("Current Workbook").Activate or something along those lines? FYI, all the client spreadsheets will have the same data dump tab with all ratings and all clients. The difference is a second tab that references the overall for each resort, so no matter whose sheet its going to, it will always be going to cell C5 in the "Means" tab. I posted this elsewhere are some took a stab at it with the following: Sub auto_open() ' ' auto_open Macro ' Macro recorded 3/6/2008 by backstation2 ' ' set overbk = workbooks("overall.xls") ChDir "M:\DATA\MERGE\0708\graphs" Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" set allbk = Activeworkbook allbk.Selection.CurrentRegion.Select Selection.Copy overbk.Sheets("Means").Range("C5").paste overbk.Sheets("Means").Range("C5").Replace _ What:="#NULL!", _ Replacement:="", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False allbk.Close SaveChanges:=False End Sub This doesn't quite work, but I have a feeling it is close to it. Anyone have any ideas? Any help/direction is much appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Workbook in VBA
If you always start with the target workbook active then your first line can
be: Set TargerWB = ActiveWorkbook I used TargetWB instead of overbk just to make it generic. It doesn't matter what name you use. Replace all uses of overbk with whatever you use. You don't need the ChDir for the macro to run. Does not harm though. I didn't run your code but it looks okay except for the replace: overbk.Sheets("Means").Cells.Replace _ This doesn't quite work It helps to know what doesn't work. Why not say it? -- Jim wrote in message ... | HI all, | | Practically zero experience with VBA, so bear with me. | | I'm trying to create a macro that will update our client data. | Problem is the names of the workbooks is constantly changing. In | SPSS, we are running a huge data dump for all of our clients along | with several ratings variables for each. I wrote a macro that works | taking this data dump, formating it and pasting it into the correct | location of our overall spreadsheet. Everything works fine up to this | point. What I'd like to do is create spreadsheets for each client and | with only their applicable ratings. I have no problem going in by | hand in the overall spreadsheet, manually deleting other clients and | blank ratings then doing a save as client_xyz. The question is, when | the macro comes with spreadsheet client_xyz, is there a way to way | that spreadsheet update instead of the overall? I'd really prefer not | to have to go into each individual one and change the macro. This is | what I originally started with: | | Sub auto_open() | ' | ' auto_open Macro | ' Macro recorded 3/6/2008 by backstation2 | ' | | ' | ChDir "M:\DATA\MERGE\0708\graphs" | Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" | Windows("allmeans.dbf").Activate | Selection.CurrentRegion.Select | Selection.Copy | Windows("overall.xls").Activate | Sheets("Means").Activate | Range("C5").Select | ActiveSheet.Paste | Application.CutCopyMode = False | Selection.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, | _ | SearchOrder:=xlByRows, MatchCase:=False | Windows("allmeans.dbf").Activate | Workbooks("allmeans.dbf").Close SaveChanges:=False | End Sub | | The dbf file is the data dump and the overall.xls is the overall data | containing all clients. Is there maybe code that I could use to | replace the Windows("overall.xls").Activate that looks like | Windows("Current Workbook").Activate or something along those lines? | FYI, all the client spreadsheets will have the same data dump tab with | all ratings and all clients. | The difference is a second tab that references the overall for each | resort, so no matter whose sheet its going to, it will always be going | to cell C5 in the "Means" tab. | | I posted this elsewhere are some took a stab at it with the following: | Sub auto_open() | ' | ' auto_open Macro | ' Macro recorded 3/6/2008 by backstation2 | ' | | ' | set overbk = workbooks("overall.xls") | | ChDir "M:\DATA\MERGE\0708\graphs" | Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" | set allbk = Activeworkbook | allbk.Selection.CurrentRegion.Select | Selection.Copy | overbk.Sheets("Means").Range("C5").paste | overbk.Sheets("Means").Range("C5").Replace _ | What:="#NULL!", _ | Replacement:="", _ | LookAt:=xlPart, _ | SearchOrder:=xlByRows, _ | MatchCase:=False | allbk.Close SaveChanges:=False | End Sub | | This doesn't quite work, but I have a feeling it is close to it. | Anyone have any ideas? | | Any help/direction is much appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Workbook in VBA
On Mar 11, 9:10 am, "Jim Rech" wrote:
If you always start with the target workbook active then your first line can be: Set TargerWB = ActiveWorkbook I used TargetWB instead of overbk just to make it generic. It doesn't matter what name you use. Replace all uses of overbk with whatever you use. You don't need the ChDir for the macro to run. Does not harm though. I didn't run your code but it looks okay except for the replace: overbk.Sheets("Means").Cells.Replace _ This doesn't quite work It helps to know what doesn't work. Why not say it? -- wrote in message ... | HI all, | | Practically zero experience with VBA, so bear with me. | | I'm trying to create a macro that will update our client data. | Problem is the names of the workbooks is constantly changing. In | SPSS, we are running a huge data dump for all of our clients along | with several ratings variables for each. I wrote a macro that works | taking this data dump, formating it and pasting it into the correct | location of our overall spreadsheet. Everything works fine up to this | point. What I'd like to do is create spreadsheets for each client and | with only their applicable ratings. I have no problem going in by | hand in the overall spreadsheet, manually deleting other clients and | blank ratings then doing a save as client_xyz. The question is, when | the macro comes with spreadsheet client_xyz, is there a way to way | that spreadsheet update instead of the overall? I'd really prefer not | to have to go into each individual one and change the macro. This is | what I originally started with: | | Sub auto_open() | ' | ' auto_open Macro | ' Macro recorded 3/6/2008 by backstation2 | ' | | ' | ChDir "M:\DATA\MERGE\0708\graphs" | Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" | Windows("allmeans.dbf").Activate | Selection.CurrentRegion.Select | Selection.Copy | Windows("overall.xls").Activate | Sheets("Means").Activate | Range("C5").Select | ActiveSheet.Paste | Application.CutCopyMode = False | Selection.Replace What:="#NULL!", Replacement:="", LookAt:=xlPart, | _ | SearchOrder:=xlByRows, MatchCase:=False | Windows("allmeans.dbf").Activate | Workbooks("allmeans.dbf").Close SaveChanges:=False | End Sub | | The dbf file is the data dump and the overall.xls is the overall data | containing all clients. Is there maybe code that I could use to | replace the Windows("overall.xls").Activate that looks like | Windows("Current Workbook").Activate or something along those lines? | FYI, all the client spreadsheets will have the same data dump tab with | all ratings and all clients. | The difference is a second tab that references the overall for each | resort, so no matter whose sheet its going to, it will always be going | to cell C5 in the "Means" tab. | | I posted this elsewhere are some took a stab at it with the following: | Sub auto_open() | ' | ' auto_open Macro | ' Macro recorded 3/6/2008 by backstation2 | ' | | ' | set overbk = workbooks("overall.xls") | | ChDir "M:\DATA\MERGE\0708\graphs" | Workbooks.Open Filename:="M:\DATA\MERGE\0708\graphs\allmeans.dbf" | set allbk = Activeworkbook | allbk.Selection.CurrentRegion.Select | Selection.Copy | overbk.Sheets("Means").Range("C5").paste | overbk.Sheets("Means").Range("C5").Replace _ | What:="#NULL!", _ | Replacement:="", _ | LookAt:=xlPart, _ | SearchOrder:=xlByRows, _ | MatchCase:=False | allbk.Close SaveChanges:=False | End Sub | | This doesn't quite work, but I have a feeling it is close to it. | Anyone have any ideas? | | Any help/direction is much appreciated! Sorry about not including the error. What happens when I hit the run button, I get a pop-up window that says Run-time error '438': Object doesn't support this property or method. When I click the Debug button the line allbk.Selection.CurrentRegion.Select is highlighted. Also, should the Set TargerWB = ActiveWorkbook command replace the set overbk = workbooks("overall.xls") command or just get inserted in front of it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart pasted to a new workbook in report can't be dynamic | Charts and Charting in Excel | |||
how to make a dynamic workbook? | Excel Discussion (Misc queries) | |||
Dynamic Range with another workbook | Excel Programming | |||
dynamic workbook | Excel Programming | |||
Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming |