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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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