Using variable to identify a workbook

  • Thread starter Thread starter Andyjim
  • Start date Start date
A

Andyjim

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
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top