Macro goes AWOL

[Bryan Dickerson]

I have a VBA macro behind a spreadsheet that I wish to stay with the
spreadsheet. The user wants to be able to refresh the data, which comes
from a data query, save the report and email it. So I'm writing up
instructions on how to save the spreadsheet as a CSV file and email it.
Very simple. So I try it to make sure I've spelled it out as completely as
possible. Works. I close the CSV and get back into spreadsheet where the
VBA macro is (I put the macro in the "ThisWorkbook" module/class) and try to
run it and Excel says it can't find the macro! It's looking for it in
<csvfilename>!ThisWorkbook. So I have a question:

1. How do I get the macro to "stay" with the spreadsheet and not "travel" to
the CSV Filename? If it confuses me the programmer, imagine what kind of
mind games it would play with the user!!

I thought you couldn't save macros with CSV files?! What gives?

TIA!

 

[Bob Phillips]

You can save macros with Excel workbooks, that's it.

A csv file is a form of text file, and as such knows nothing about Excel
macros.

 

[Bryan Dickerson]

So evidently I've screwed something up. 'cause I can make it happen over
and over: If I save the spreadsheet as a csv, then close the csv file and
open up the spreadsheet and try to run my macro again, I get:

"The macro C:\MyFile.csv'!ThisWorkbook.MyMacro' cannot be found."

I'm using Office XP SP3. So what do I do to correct this so that the macro
doesn't "travel" to the CSV file. I know logically that's not possible, but
it's what Excel is essentially saying.

 

[Guest]

Bryan-

How are you calling the macro? The macro isn't really going anywhere,
right. It is just the reference to the macro that is getting changed?

You might try putting your macro in a Module sheet within the VBA project
rather than storing it in "ThisWorkbook"

Stan Shoemaker
Palo Alto, CA

 

[Tom Ogilvy]

make a copy of your workbook with a different name (Possibly save copy as,
then open it up). then save that as a csv file.

It sounds like you have assigned your macro to a button or something. when
you do a save as to the original file, this causes the assignment to point
at the .CSV, there the macro is until you actually close the workbook. If
you make the copy and use that, you should be able to avoid that situation.

 

[Bryan Dickerson]

To answer both you and 'stanshoe', I am trying to run the macro from a
custom button and evidently the reference to the macro changes when the name
of the file changes. I tried moving the macro to a module instead of the
"ThisWorkbook" module/class--it doesn't seem to change anything.

Tom, I've read your post several times and I don't understand what you're
saying. Maybe my brain cells are taking a long weekend.

Let me start at the beginning:
1. Is an Excel VBA macro physically stored with the .xls file? Or is it
stored in the deep recesses of C:\Documents and Settings\<username>?
2. If it's physically stored with the .xls file and I do a SaveAs to a .csv
file(since a CSV file is basically text), shouldn't the reference to the
macro stay the same in the button and stop working after the SaveAs to a
CSV?

 

[Bryan Dickerson]

Ok, I think I'm beginning to put the pieces together now. I guess I have
determined that my macro *is* stored with the spreadsheet, 'cause when I do
a SaveAs to a CSV, then close the CSV and re-open the spreadsheet, it
complains that <csvfilename>!MyMacro is not found. So it has to be storing
it with the spreadsheet. So now the question becomes, how do I get the
button reference to stay constant if I save the spreadsheet as out to a CSV
file?

Thanx for your patience!

 

[Tom Ogilvy]

make a copy of your workbook with a different name using "save copy as"
(not save as),
so if your workbook were mybook.xls you would 'save copy as' with a name
of mybooka.xls

Now open mybooka.xls

Now you have
mybooka.xls
mybook.xls

both open and they are identical

make mybooka.xls the active workbook.

Do Save AS select CSV and give it a filename

close the CSV file/workbook.

now your button should still work.


Excel adjusts the name of the workbook for the macro the button is assigned
to if you do a SAVE AS on it. The above avoids that.

Your macro shoud be in a general module. You should reserve the
Thisworkbook and sheet modules for event code related to the
workbook/worksheets.

 

[Bryan Dickerson]

Ok, the light finally came on! Thank you!

Now I have another question, but I'll start another thread...

Thanx again.