This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

On Wed, 12 Dec 2007 20:28:00 -0800, David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

My advice is not to use a macro to do this at all. Take your macro code
and translate it to a vbscript 'scripting macro'. Each time you want to
run the macro, you actually run the script, which opens the workbook and
does all the work with it - but does not actually put any code in the
workbook its self. IE separate model and controller.

In general, it is getting ever harder to use macros inside workbooks with
Excel, so I developed the concept of 'scripting macros' for just this
sort of problem which I hit constantly at work.

See my blog or book. http://nerds-central.blogspot.com and click on the
label for 'baby steps' or Exsead.

AJ

--
http://nerds-central.blogspot.com/20...-become-excel-
god.html

Why not create a normal workbook that contains the code that does the work.

And just open that workbook when you need to run the macro.



David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

--

Dave Peterson

"Dr Alexander J Turner" wrote in message
In general, it is getting ever harder to use macros inside workbooks with
Excel,

Would you care to elaborate on that?

so I developed the concept of 'scripting macros' for just this
sort of problem

Aren't "scripting macros" just plain-jane VBS? It isn't really a "concept"
to begin with, let alone one that is unique to Excel automation. There are
many ways (XLAs, XLLs, COM add-ins, Automation add-ins, NET/VSTO, etc) to
automate functions or operations in Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Dr Alexander J Turner" wrote in message
...
On Wed, 12 Dec 2007 20:28:00 -0800, David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

My advice is not to use a macro to do this at all. Take your macro code
and translate it to a vbscript 'scripting macro'. Each time you want to
run the macro, you actually run the script, which opens the workbook and
does all the work with it - but does not actually put any code in the
workbook its self. IE separate model and controller.

In general, it is getting ever harder to use macros inside workbooks with
Excel, so I developed the concept of 'scripting macros' for just this
sort of problem which I hit constantly at work.

See my blog or book. http://nerds-central.blogspot.com and click on the
label for 'baby steps' or Exsead.

AJ

--
http://nerds-central.blogspot.com/20...-become-excel-
god.html

On 12/13/2007 4:17 AM Dave Peterson spake thus:

Why not create a normal workbook that contains the code that does the work.

Well, I *thought* that was what I had. I have a workbook (that's the
jargon for "Excel file containing one or more worksheets", correct?)
with several sheets. It also contains the macro I'm using. Is that
"normal" or not?

And just open that workbook when you need to run the macro.

That's what I do. I open my file, click the button to run the macro,
then save the sheet I'm interested in as a CSV file.

Am I missing something obvious here?

David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

On 12/13/2007 3:04 AM Dr Alexander J Turner spake thus:

On Wed, 12 Dec 2007 20:28:00 -0800, David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.

My advice is not to use a macro to do this at all. Take your macro code
and translate it to a vbscript 'scripting macro'. Each time you want to
run the macro, you actually run the script, which opens the workbook and
does all the work with it - but does not actually put any code in the
workbook its self. IE separate model and controller.

In general, it is getting ever harder to use macros inside workbooks with
Excel, so I developed the concept of 'scripting macros' for just this
sort of problem which I hit constantly at work.

See my blog or book. http://nerds-central.blogspot.com and click on the
label for 'baby steps' or Exsead.

Thanks for the reply. Unfortunately, your site has about 10 tons too
much information. Could you please explain a few simple things: what's
the difference between a macro and a VB script? Where does the script
reside--in a separate file? How does one invoke the script?

Keep in mind that I'm designing this so it can be used by "dummies" who
won't have the faintest clue about macros, scripts or anything else. I'm
trying to idiot-proof it, so it gots to be simple.

I read your message that you were saving your file with the macro as a .csv file
(or discarding it).

..CSV files (and other text files (.prn, .txt)) are just plain old text files.
They won't include any of your code or any of your formatting or any of your
formulas.

So when you need a macro that can be re-run when you want, you can put that
macro in a workbook--but save that workbook as a normal .xls file--not .csv.

Then whenever you need to run the macro,
you can open your workbook with the macro.
Then open your data file (.csv or what you want)
then alt-f8 to select the macro to run (from the macro workbook).

If you write the macro so that it works against the activesheet (no sheet names,
no workbook names), it should work ok.

David Nebenzahl wrote:

On 12/13/2007 4:17 AM Dave Peterson spake thus:

Why not create a normal workbook that contains the code that does the work.

Well, I *thought* that was what I had. I have a workbook (that's the
jargon for "Excel file containing one or more worksheets", correct?)
with several sheets. It also contains the macro I'm using. Is that
"normal" or not?

And just open that workbook when you need to run the macro.

That's what I do. I open my file, click the button to run the macro,
then save the sheet I'm interested in as a CSV file.

Am I missing something obvious here?

David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.


--

Dave Peterson

ps. If I'm sharing macros with others, I'll save the file as an addin (with a
nice significant name) and give the users a way to run those macros.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

If you want to learn about modifying the ribbon in xl2007, you can start at Ron
de Bruin's site:
http://www.rondebruin.nl
or
http://www.rondebruin.nl/ribbon.htm



Dave Peterson wrote:

I read your message that you were saving your file with the macro as a .csv file
(or discarding it).

.CSV files (and other text files (.prn, .txt)) are just plain old text files.
They won't include any of your code or any of your formatting or any of your
formulas.

So when you need a macro that can be re-run when you want, you can put that
macro in a workbook--but save that workbook as a normal .xls file--not .csv.

Then whenever you need to run the macro,
you can open your workbook with the macro.
Then open your data file (.csv or what you want)
then alt-f8 to select the macro to run (from the macro workbook).

If you write the macro so that it works against the activesheet (no sheet names,
no workbook names), it should work ok.

David Nebenzahl wrote:

On 12/13/2007 4:17 AM Dave Peterson spake thus:

Why not create a normal workbook that contains the code that does the work.

Well, I *thought* that was what I had. I have a workbook (that's the
jargon for "Excel file containing one or more worksheets", correct?)
with several sheets. It also contains the macro I'm using. Is that
"normal" or not?

And just open that workbook when you need to run the macro.

That's what I do. I open my file, click the button to run the macro,
then save the sheet I'm interested in as a CSV file.

Am I missing something obvious here?

David Nebenzahl wrote:

This is a really annoying problem: I have a worksheet with a macro I
wrote to do some reformatting on one of the sheets before I submit it as
a CSV file to a database. I set up a button on the toolbar linked to the
macro: when I open the file, move my data to this one sheet and click
the button, the macro runs as it should.

I then save that sheet as a CSV file; I basically say "no" to all the
warning alerts that pop up, warning me that in that format it's only
possible to save that particular sheet (which is the only one I'm
interested in for that purpose anyhow), etc. The CSV file gets created
correctly. Then I just abandon the now-CSV workbook. Fine.

The problem is that the next time I open the Excel version of the
workbook (which was NOT saved in its altered state), I cannot run the
macro by using the toolbar button: I get an error message saying it
can't find <mumbo-jumbo ... macro-name.CSV. Even weirder, it also opens
the CSV file at the same time. (I can run the macro by invoking it
through the Tools menu, but the button is intended to make this whole
process easy for non-computer-literate types, and this defeats that
purpose.)

When I delete the CSV file, which is only needed temporarily, then
things get even uglier: Excel complains that it can't find the CSV file
when I'm working with the normal (XLS) version of the workbook.

Is there some way to get Excel to disassociate or disentangle itself
from this CSV file so I don't run into all these problems? It seems that
only a system reboot makes the XLS file usable again.


--

Dave Peterson

--

Dave Peterson

On 12/13/2007 10:10 AM Dave Peterson spake thus:

I read your message that you were saving your file with the macro as a .csv file
(or discarding it).

.CSV files (and other text files (.prn, .txt)) are just plain old text files.
They won't include any of your code or any of your formatting or any of your
formulas.

So when you need a macro that can be re-run when you want, you can put that
macro in a workbook--but save that workbook as a normal .xls file--not .csv.

Then whenever you need to run the macro,
you can open your workbook with the macro.
Then open your data file (.csv or what you want)
then alt-f8 to select the macro to run (from the macro workbook).

If you write the macro so that it works against the activesheet (no sheet names,
no workbook names), it should work ok.

Maybe I wasn't clear: the macro IS in a normal .xls file. Let me explain
again, hopefully more clearly:

I have an Excel workbook with several sheets. It contains the macro. One
of the sheets ("work") is a temporary work area where I copy records
from the other sheets for export. The macro takes those records in
"work" and reformats them. I then save the "work" sheet ONLY as a CSV
file for export. I use the CSV file, then discard (delete) it. The .xls
workbook remains unchanged.

All this works wonderfully well. The problem is that the next time I try
to use the .xls file, Excel wants to look in the CSV file for the macro
and says "I can't find it!", when the macro is really still in the .xls
file.

Does this make sense?

I'm not sure how you're starting the macro.

But it sounds like whatever object is used to invoke that macro is confused.
Depending on what that object is, you could just reassign the correct macro (in
the correct workbook) to that object.

Personally, I find building the object -- either a toolbar icon or an additional
option on a builtin toolbar -- in code makes my life much simpler.

That's why I suggested those links in the other post.

David Nebenzahl wrote:

On 12/13/2007 10:10 AM Dave Peterson spake thus:

I read your message that you were saving your file with the macro as a .csv file
(or discarding it).

.CSV files (and other text files (.prn, .txt)) are just plain old text files.
They won't include any of your code or any of your formatting or any of your
formulas.

So when you need a macro that can be re-run when you want, you can put that
macro in a workbook--but save that workbook as a normal .xls file--not .csv.

Then whenever you need to run the macro,
you can open your workbook with the macro.
Then open your data file (.csv or what you want)
then alt-f8 to select the macro to run (from the macro workbook).

If you write the macro so that it works against the activesheet (no sheet names,
no workbook names), it should work ok.

Maybe I wasn't clear: the macro IS in a normal .xls file. Let me explain
again, hopefully more clearly:

I have an Excel workbook with several sheets. It contains the macro. One
of the sheets ("work") is a temporary work area where I copy records
from the other sheets for export. The macro takes those records in
"work" and reformats them. I then save the "work" sheet ONLY as a CSV
file for export. I use the CSV file, then discard (delete) it. The .xls
workbook remains unchanged.

All this works wonderfully well. The problem is that the next time I try
to use the .xls file, Excel wants to look in the CSV file for the macro
and says "I can't find it!", when the macro is really still in the .xls
file.

Does this make sense?

--

Dave Peterson

On 12/13/2007 11:39 AM Dave Peterson spake thus:

I'm not sure how you're starting the macro.

I have a toolbar button assigned to the macro.

But it sounds like whatever object is used to invoke that macro is confused.
Depending on what that object is, you could just reassign the correct macro (in
the correct workbook) to that object.

Personally, I find building the object -- either a toolbar icon or an additional
option on a builtin toolbar -- in code makes my life much simpler.

Now I don't really understand what you mean by "object" here. Yes,
something is confused here (besides me, that is!): Excel thinks the
macro lives in the CSV file after I do the "save as", when it's really
in the Excel file.

Even when it "breaks", I can still run the macro by drilling down in the
"Tools" menu and starting the macro that way; I'm trying to make this
easy for others, so I much prefer the toolbar button.

Also, I noted your earlier suggestion that I run the macro against the
current worksheet, rather than naming the sheet or the workbook. The
macro does select a sheet ("work") in the current workbook, but doesn't
name the workbook. Could this be causing the problem? Again, I would
prefer to do things this way (explicitly naming the sheet) to further
"idiot-proof" the process.

In your case, the object that I meant is the button that you used on the
toolbar.

You could have used a button on a worksheet. You could have used an object from
the drawing toolbar. You could have used an object from the Forms toolbar.

I still think that if you build that toolbar icon using one of those links,
you'll have lots fewer problems.

David Nebenzahl wrote:

On 12/13/2007 11:39 AM Dave Peterson spake thus:

I'm not sure how you're starting the macro.

I have a toolbar button assigned to the macro.

But it sounds like whatever object is used to invoke that macro is confused.
Depending on what that object is, you could just reassign the correct macro (in
the correct workbook) to that object.

Personally, I find building the object -- either a toolbar icon or an additional
option on a builtin toolbar -- in code makes my life much simpler.

Now I don't really understand what you mean by "object" here. Yes,
something is confused here (besides me, that is!): Excel thinks the
macro lives in the CSV file after I do the "save as", when it's really
in the Excel file.

Even when it "breaks", I can still run the macro by drilling down in the
"Tools" menu and starting the macro that way; I'm trying to make this
easy for others, so I much prefer the toolbar button.

Also, I noted your earlier suggestion that I run the macro against the
current worksheet, rather than naming the sheet or the workbook. The
macro does select a sheet ("work") in the current workbook, but doesn't
name the workbook. Could this be causing the problem? Again, I would
prefer to do things this way (explicitly naming the sheet) to further
"idiot-proof" the process.

--

Dave Peterson