We help IT Professionals succeed at work.

Excel VBA: Define current workbook in VBA

ouestque
ouestque asked
on
1,256 Views
Last Modified: 2012-05-10
What VBA code would I need so that it will store the current workbooks name in a public variable. (Even after a "Save As...")

Here is what I am thinking of...

i.e. In a module I might have...
--->Public Current_Worksheet_Name as String

i.e. When the workbook opens I might have...
--->Current_Worksheet_Name = "Activeworkbook.name"


This is usually fine, but what if the user save's the worksheet under a different name while it is still open. i.e. user Goes to File--->Save As.... --->Name="NewWorksheetName"

In this case the public variable should update to: "NewWorksheetName"
BUT... there is not a after_SaveAs event in Excel 2003. Any ideas? hmmm...
Comment
Watch Question

CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The question is, why have a global variable that you have to keep updating?
These are already globally accessible:

ActiveWorkbook.Name    ' the currently active workbook
ThisWorkbook.Name   ' where the module lives

In what use case would you need to store that reference?

Author

Commented:

i.e. if I have a form that has a button, when you press the button, the form does the following...

sheet1.cells(1,1) = "Hello World"

If the user has 2 sheets open. Opens the form, then moves focus to the new worksheet, with the code above the form will copy "Hello World" to the newly focused worksheet not the original worksheet it was intended for.

My resolution to this was to change my code above so that it will refer to the worksheet that the form belongs.

i.e. ("ExcelFile1.xls").sheet1.cells(1,1)= "Hello World"

This way it will always save "Hello World" to "ExcelFile1.xls" no matter what worksheet the user is looking at.

Nonetheless, this worksheet can change names if it is saved as something different, making this code not work.
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
rorya that is correct, but if user has both sheets open and the form is open, then the user goes to 'excelfile1.xls' and saves as... --'excelfile25314225.xls' the code will throw an error. this is because it will try to paste 'hello orld' to 'excelfile1.xls'. but 'excelfile1.xls'' is no longer open at this point. it is now called 'excelfile25314225' because user saved it as such.

my goal is to have a form that always pastes to the originating worksheet no matter what the user does to it and no matter what other workbooks have the focus. (i.e. saves under difft name.)
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Again, no it won't, because you are referring to the sheet by codename (assuming that actually is your code; if not, this discussion is pointless) so it will always refer to the sheet in the workbook containing the code. You do not refer to the workbook name at all.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
For clarity, I am referring to this:
sheet1.cells(1,1) = "Hello World"

which did not require changing.

Author

Commented:
Shoot! I did not mean to close this question. Please award the people involved the appropriate points.

Author

Commented:
I wanted to give points to rorya and cyberkiwi and make their comments the solution.

I did not want to make my own comment (ouestque) the solution. It was an accident.

Author

Commented:
Rorya was correct! Thanks for the help!
CERTIFIED EXPERT
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
> Rorya was correct!

You mean I wasn't :)
haha.. there's a reason why Rory is MVP *and* top Excel expert this year

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.