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!

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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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

"Bryan Dickerson" wrote:

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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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.

--
Regards,
Tom Ogilvy


"Bryan Dickerson" wrote in message
...
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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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?

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Bryan Dickerson" wrote in message
...
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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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!

"Bryan Dickerson" wrote in message
...
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?

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Bryan Dickerson" wrote in message
...
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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

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.

--
Regards,
Tom Ogilvy



"Bryan Dickerson" wrote in message
...
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!

"Bryan Dickerson" wrote in message
...
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?

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Bryan Dickerson" wrote in message
...
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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!

Ok, the light finally came on! Thank you!

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

Thanx again.

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy



"Bryan Dickerson" wrote in message
...
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!

"Bryan Dickerson" wrote in message
...
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?

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Bryan Dickerson" wrote in message
...
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.

"Bob Phillips" wrote in message
...
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.

--
HTH

-------

Bob Phillips
"Bryan Dickerson" wrote in message
...
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!