Solved

Excel VBA: Define current workbook in VBA

Posted on 2010-11-07
11
1,097 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...
0
Comment
Question by:ouestque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34081343
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?
0
 

Author Comment

by:ouestque
ID: 34083635

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.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 34084181
>>", with the code above the form will copy "Hello World" to the newly focused worksheet"

No, it won't, since you are using the code name of the sheet.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 250 total points
ID: 34086692
' ThisWorkbook.Name   ' where the module/form lives

This?

ThisWorkbook.sheets("Sheet1").cells(1,1)= "Hello World"
0
 

Author Comment

by:ouestque
ID: 34111634
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.)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34111672
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34111688
For clarity, I am referring to this:
sheet1.cells(1,1) = "Hello World"

which did not require changing.
0
 

Author Comment

by:ouestque
ID: 34117367
Shoot! I did not mean to close this question. Please award the people involved the appropriate points.
0
 

Author Comment

by:ouestque
ID: 34117378
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.
0
 

Author Closing Comment

by:ouestque
ID: 34117386
Rorya was correct! Thanks for the help!
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34117605
> Rorya was correct!

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel (XLSM) on iPad 3 39
Excel - merge column values 3 29
need count all combination 31 34
VBA - How to copy the data from workbook to workbook masterfile in VBA? 20 50
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question