Using variable to identify a workbook

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
 
B

Bob Phillips

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)
 
A

Andyjim

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...
 
B

Bob Phillips

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error: can't find project or library 6
Running macro in one workbook that refers to variable in another 3
Macro launched from another file only partially works 1
Kill a Macro? 3
Run macro from active workbook whose name keeps changing 20
Using a string variable to change a file name 2
Reference filename used in code from cell 5
Don't want to hard-code file name in macro 3

Top