Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable to identify a workbook
I hope I can explain this coherantly. We have put together a small trading
system which we plan to send out to users. In order to accommodate future updates, we want to be able to take the name of the user's file (which we don't know for sure) and identify that name so that we eventually save the update as that name. Also, in the process, we will need to be copying history data from the original file to the update file so we need to be able to go back and forth between files. I am woefully unsuccessful so far in making the workbook name a variable. Here is a start: Sub Update1() ' 'Inserts filename in "Filename" cell which is cell LookUP!c28 'Makes backup copy of old file, appending "_Backup" to filename 'Sends "Filename" to Update file, "UserFilename" cell 'Calls Update2 macro in Update file 'Insert Filename in Filename cell Dim wbname As String Range("Filename") = ActiveWorkbook.Name 'BOMBS HERE Set wbname.Value = Range("filename") Set wklookup = Worksheets("Lookup") 'Make backup of current file ActiveWorkbook.SaveCopyAs Filename:= _ "RiskulatorBackup.xls" 'Send Filename to Update file, UserFilename cell 'BOMBS OUT HERE ALSO Windows.wbname.Activate Range("lookup!c28").Select Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("[RiskulatorUpdate.xls]lookup!UserFilename").Select ActiveSheet.Paste 'Send Current Version to Update file, Old Version cell Windows(wbname).Activate Range("CurrentVersion").Select Application.CutCopyMode = False Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("OldVersion").Select ActiveSheet.Paste End Sub Like everybody else who asks for help from you, we are on a tight timeframe. Thanks so much. Andy and Beverly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable to identify a workbook
Just capture the activeworkbook
Set oWB = ActiveWorkbook then you can save that later oWB.SaveCopyAs Filename:="RiskulatorBackup.xls" and manipulate it in other ways oWB.Save oWB.Close all the while working on a new, different, activeworkbook. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andyjim" wrote in message ... I hope I can explain this coherantly. We have put together a small trading system which we plan to send out to users. In order to accommodate future updates, we want to be able to take the name of the user's file (which we don't know for sure) and identify that name so that we eventually save the update as that name. Also, in the process, we will need to be copying history data from the original file to the update file so we need to be able to go back and forth between files. I am woefully unsuccessful so far in making the workbook name a variable. Here is a start: Sub Update1() ' 'Inserts filename in "Filename" cell which is cell LookUP!c28 'Makes backup copy of old file, appending "_Backup" to filename 'Sends "Filename" to Update file, "UserFilename" cell 'Calls Update2 macro in Update file 'Insert Filename in Filename cell Dim wbname As String Range("Filename") = ActiveWorkbook.Name 'BOMBS HERE Set wbname.Value = Range("filename") Set wklookup = Worksheets("Lookup") 'Make backup of current file ActiveWorkbook.SaveCopyAs Filename:= _ "RiskulatorBackup.xls" 'Send Filename to Update file, UserFilename cell 'BOMBS OUT HERE ALSO Windows.wbname.Activate Range("lookup!c28").Select Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("[RiskulatorUpdate.xls]lookup!UserFilename").Select ActiveSheet.Paste 'Send Current Version to Update file, Old Version cell Windows(wbname).Activate Range("CurrentVersion").Select Application.CutCopyMode = False Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("OldVersion").Select ActiveSheet.Paste End Sub Like everybody else who asks for help from you, we are on a tight timeframe. Thanks so much. Andy and Beverly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable to identify a workbook
Hi Bob-
Your suggestion worked beautifully. Got one more hurdle. How do you take the text from a cell in the file and make it the name of the file i.e. cell text = somefilename.xls I want the VBA to grab that filename and save the open file as that name. Thanks much. Andy "Bob Phillips" wrote: Just capture the activeworkbook Set oWB = ActiveWorkbook then you can save that later oWB.SaveCopyAs Filename:="RiskulatorBackup.xls" and manipulate it in other ways oWB.Save oWB.Close all the while working on a new, different, activeworkbook. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andyjim" wrote in message ... I hope I can explain this coherantly. We have put together a small trading system which we plan to send out to users. In order to accommodate future updates, we want to be able to take the name of the user's file (which we don't know for sure) and identify that name so that we eventually save the update as that name. Also, in the process, we will need to be copying history data from the original file to the update file so we need to be able to go back and forth between files. I am woefully unsuccessful so far in making the workbook name a variable. Here is a start: Sub Update1() ' 'Inserts filename in "Filename" cell which is cell LookUP!c28 'Makes backup copy of old file, appending "_Backup" to filename 'Sends "Filename" to Update file, "UserFilename" cell 'Calls Update2 macro in Update file 'Insert Filename in Filename cell Dim wbname As String Range("Filename") = ActiveWorkbook.Name 'BOMBS HERE Set wbname.Value = Range("filename") Set wklookup = Worksheets("Lookup") 'Make backup of current file ActiveWorkbook.SaveCopyAs Filename:= _ "RiskulatorBackup.xls" 'Send Filename to Update file, UserFilename cell 'BOMBS OUT HERE ALSO Windows.wbname.Activate Range("lookup!c28").Select Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("[RiskulatorUpdate.xls]lookup!UserFilename").Select ActiveSheet.Paste 'Send Current Version to Update file, Old Version cell Windows(wbname).Activate Range("CurrentVersion").Select Application.CutCopyMode = False Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("OldVersion").Select ActiveSheet.Paste End Sub Like everybody else who asks for help from you, we are on a tight timeframe. Thanks so much. Andy and Beverly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using variable to identify a workbook
oWB.SaveAs = Activeworkbook.Worksheets(1).Range("A1").Value
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andyjim" wrote in message ... Hi Bob- Your suggestion worked beautifully. Got one more hurdle. How do you take the text from a cell in the file and make it the name of the file i.e. cell text = somefilename.xls I want the VBA to grab that filename and save the open file as that name. Thanks much. Andy "Bob Phillips" wrote: Just capture the activeworkbook Set oWB = ActiveWorkbook then you can save that later oWB.SaveCopyAs Filename:="RiskulatorBackup.xls" and manipulate it in other ways oWB.Save oWB.Close all the while working on a new, different, activeworkbook. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andyjim" wrote in message ... I hope I can explain this coherantly. We have put together a small trading system which we plan to send out to users. In order to accommodate future updates, we want to be able to take the name of the user's file (which we don't know for sure) and identify that name so that we eventually save the update as that name. Also, in the process, we will need to be copying history data from the original file to the update file so we need to be able to go back and forth between files. I am woefully unsuccessful so far in making the workbook name a variable. Here is a start: Sub Update1() ' 'Inserts filename in "Filename" cell which is cell LookUP!c28 'Makes backup copy of old file, appending "_Backup" to filename 'Sends "Filename" to Update file, "UserFilename" cell 'Calls Update2 macro in Update file 'Insert Filename in Filename cell Dim wbname As String Range("Filename") = ActiveWorkbook.Name 'BOMBS HERE Set wbname.Value = Range("filename") Set wklookup = Worksheets("Lookup") 'Make backup of current file ActiveWorkbook.SaveCopyAs Filename:= _ "RiskulatorBackup.xls" 'Send Filename to Update file, UserFilename cell 'BOMBS OUT HERE ALSO Windows.wbname.Activate Range("lookup!c28").Select Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("[RiskulatorUpdate.xls]lookup!UserFilename").Select ActiveSheet.Paste 'Send Current Version to Update file, Old Version cell Windows(wbname).Activate Range("CurrentVersion").Select Application.CutCopyMode = False Selection.Copy Windows("RiskulatorUpdate.xls").Activate Range("OldVersion").Select ActiveSheet.Paste End Sub Like everybody else who asks for help from you, we are on a tight timeframe. Thanks so much. Andy and Beverly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I identify who placed security on a workbook? | Excel Worksheet Functions | |||
How do I identify Filter criteria or variable graph title? | Excel Discussion (Misc queries) | |||
How to identify variable workbooks in an excel Addin | Excel Programming | |||
Use of variable to identify range of sheets in a workbook | Excel Discussion (Misc queries) | |||
VBA Identify Workbook | Excel Programming |