Personal macro csv file

[BNT1 via OfficeKB.com]

Hi

I have recorded a personal macro, with a short cut key in excel. When I am
sent an e-mail with attachment, the attachment .csv file. I open the file on
the attachment, but the short cut key does not run the macro, unless, i copy
and paste into an excel doc

is it possible to get around having to copy and paste?

regards

Brian

 

[KC]

I do not fully understand your issue here, but you cannot save the macro in
..csv file (if that is what your trying to do).

-kc
*Click YES if this works

 

[Dave Peterson]

Macros live in excel workbooks--not CSV files.

So if you want the recipient to be able to use your macro, you could create a
separate workbook that contains that macro that processes the CSV data.

Then send both the workbook and the .csv file to the recipient. (Maybe just the
macro workbook one time and tell them to save it and re-open it when they need
it.)

Then tell them to open the workbook with the macro (and depending on what your
macro does), then tell them to save/open the csv file and run the macro
(tools|macro|macros...|run in xl2003 menus).

 

[Gord Dibben]

You do not have to copy/paste into an Excel workbook, simply open the *.csv
attachment with Excel.

You must code your macro in your Personal Macro Workbook to operate on
whichever *.csv file you have opened in Excel.

If you open the *.csv file in Notepad or Word, your Personal Macro Workbook
is not available.


Gord Dibben MS Excel MVP

 

[BNT1 via OfficeKB.com]

Thank you all for the input

this is the code i am using (recorded macro), copied it in CSV module
I open the email attachment and use the shortcut, however, it keeps asking to
update the file and shows all folders
Can / should this be run direct from the email or do i have to save first?

regards

You do not have to copy/paste into an Excel workbook, simply open the *.csv
attachment with Excel.

You must code your macro in your Personal Macro Workbook to operate on
whichever *.csv file you have opened in Excel.

If you open the *.csv file in Notepad or Word, your Personal Macro Workbook
is not available.

Gord Dibben MS Excel MVP

Hi

[quoted text clipped - 8 lines]

Brian

 

[Gord Dibben]

There are no modules in a CSV file so can't tell what you're doing.


Gord

Thank you all for the input

this is the code i am using (recorded macro), copied it in CSV module
I open the email attachment and use the shortcut, however, it keeps asking to
update the file and shows all folders
Can / should this be run direct from the email or do i have to save first?

regards

You do not have to copy/paste into an Excel workbook, simply open the *.csv
attachment with Excel.

You must code your macro in your Personal Macro Workbook to operate on
whichever *.csv file you have opened in Excel.

If you open the *.csv file in Notepad or Word, your Personal Macro Workbook
is not available.

Gord Dibben MS Excel MVP

Hi

[quoted text clipped - 8 lines]

Brian

 

[BNT1 via OfficeKB.com]

it has excel in the top right hand corner
will take another look at when at work in the morning and try and get more
info

regards

There are no modules in a CSV file so can't tell what you're doing.

Gord

Thank you all for the input

[quoted text clipped - 21 lines]

 

[BNT1 via OfficeKB.com]

Hi

I have misunderstood, and it is an excel file. When generating the report,
from AS400 system tne file is an attachement and a sheet name is allocated
each time it is run, ie, `OASISCSV(48). then next time `OASISCSV(49) etc.
This stopped the macro running owing to the sheet name when i defined a range.
It does run if i amend the sheet name to sheet1.
Is there an way of giving it any wild cards etc, say OASISCSV**** or
something, in the macro, to prevent it looking for the sheet1. Also the
shortkut cut does not operate, but selecting from tools/macro does?

All help is appriciated

Brian

it has excel in the top right hand corner
will take another look at when at work in the morning and try and get more
info

regards

There are no modules in a CSV file so can't tell what you're doing.

[quoted text clipped - 5 lines]

 

[Dave Peterson]

Before you do anything, make your file the way you normally do.
Add the macro. Save the file. Close excel.
Re-open excel. Reopen your file.
Do you still see you code in that workbook's module?

If you don't see that module, then something is going wrong--I'd guess that
you're not saving the file as a normal workbook (*.xls). You may be saving it
as a .csv and those .csv files are just plain old text files.

But since those plain old .csv files are "owned" by excel, they can still be
called excel files. (The lingo can be confusing.)

On the other hand, if you do see your module in the project, then you're doing
fine.

Maybe you can change the code so that it doesn't depend on the name of the
worksheet.

You may be able to do everything without naming a range (just use range object
variables).

Or your code could rely on the activesheet.

with activesheet
'for a global/workbook name
.range("A1:X99").name = "SomeNameHere"

'for a local/worksheet level name
.range("a1:x99").name = "'" & .name & "'!SomeNameHere"
end with

Hi

I have misunderstood, and it is an excel file. When generating the report,
from AS400 system tne file is an attachement and a sheet name is allocated
each time it is run, ie, `OASISCSV(48). then next time `OASISCSV(49) etc.
This stopped the macro running owing to the sheet name when i defined a range.
It does run if i amend the sheet name to sheet1.
Is there an way of giving it any wild cards etc, say OASISCSV**** or
something, in the macro, to prevent it looking for the sheet1. Also the
shortkut cut does not operate, but selecting from tools/macro does?

All help is appriciated

Brian

it has excel in the top right hand corner
will take another look at when at work in the morning and try and get more
info

regards

There are no modules in a CSV file so can't tell what you're doing.

[quoted text clipped - 5 lines]

Brian

 

[BNT1 via OfficeKB.com]

Thank you Dave, amended macro as suggested
Got the attachement in the email - tools/macro /run ( personal macro by the
way) and it worked a treat
I do notice that the shortcut key (Ctrl+W) just closes the file and not run
the macro, even when saved and re-opened
Perhaps this is something that I will have to live with ?

Thanks again
Brian

Before you do anything, make your file the way you normally do.
Add the macro. Save the file. Close excel.
Re-open excel. Reopen your file.
Do you still see you code in that workbook's module?

If you don't see that module, then something is going wrong--I'd guess that
you're not saving the file as a normal workbook (*.xls). You may be saving it
as a .csv and those .csv files are just plain old text files.

But since those plain old .csv files are "owned" by excel, they can still be
called excel files. (The lingo can be confusing.)

On the other hand, if you do see your module in the project, then you're doing
fine.

Maybe you can change the code so that it doesn't depend on the name of the
worksheet.

You may be able to do everything without naming a range (just use range object
variables).

Or your code could rely on the activesheet.

with activesheet
'for a global/workbook name
.range("A1:X99").name = "SomeNameHere"

'for a local/worksheet level name
.range("a1:x99").name = "'" & .name & "'!SomeNameHere"
end with

Hi

[quoted text clipped - 26 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200910/1

 

[Dave Peterson]

If you used ctrl-W make sure you hit ctrl-shift-w (uppercase w).

ctrl-w (lower case) is the shortcut to close the activewindow. And if the
workbook only has a single window, you'd be trying to close the workbook.

Thank you Dave, amended macro as suggested
Got the attachement in the email - tools/macro /run ( personal macro by the
way) and it worked a treat
I do notice that the shortcut key (Ctrl+W) just closes the file and not run
the macro, even when saved and re-opened
Perhaps this is something that I will have to live with ?

Thanks again
Brian

Before you do anything, make your file the way you normally do.
Add the macro. Save the file. Close excel.
Re-open excel. Reopen your file.
Do you still see you code in that workbook's module?

If you don't see that module, then something is going wrong--I'd guess that
you're not saving the file as a normal workbook (*.xls). You may be saving it
as a .csv and those .csv files are just plain old text files.

But since those plain old .csv files are "owned" by excel, they can still be
called excel files. (The lingo can be confusing.)

On the other hand, if you do see your module in the project, then you're doing
fine.

Maybe you can change the code so that it doesn't depend on the name of the
worksheet.

You may be able to do everything without naming a range (just use range object
variables).

Or your code could rely on the activesheet.

with activesheet
'for a global/workbook name
.range("A1:X99").name = "SomeNameHere"

'for a local/worksheet level name
.range("a1:x99").name = "'" & .name & "'!SomeNameHere"
end with

Hi

[quoted text clipped - 26 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200910/1

 

[BNT1 via OfficeKB.com]

Well, that explains why it kept closing the workbook !

I will try this at work in the morning, however, do I have to set the macro
lowercase w to capital W, secondly, is this Ctrl - Shift - always used when
setting your own shortcut, or do you have to remember the ones already set in
excel?

Regards

If you used ctrl-W make sure you hit ctrl-shift-w (uppercase w).

ctrl-w (lower case) is the shortcut to close the activewindow. And if the
workbook only has a single window, you'd be trying to close the workbook.

Thank you Dave, amended macro as suggested
Got the attachement in the email - tools/macro /run ( personal macro by the

[quoted text clipped - 46 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200910/1

 

[Dave Peterson]

If you're using tools|macro|macros (select the macro) and click Options

You'll see that that label for the shortcut key will change from Ctrl to
Ctrl-Shift when you type an uppercase key.

If you want to use an excel shortcut (I wouldn't!), it'll be up to use one that
you don't use very often. There are lots I'd stay away from.

A few years ago, I was interested in what letter keys were available (not
in-use) for shortcuts. (I think in the xl97 days...)

L, Q, T were available in excel
and
E, M, J were used in the VBE, but not in excel proper.

But I don't like using shortcut keys. Once the number of utility macros you
have gets more than a couple, it's difficult to remember (for me, at least).

I'd use a toolbar or modify the worksheet menubar (xl2003 choices)...

======
(Saved from a previous post)

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)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

Well, that explains why it kept closing the workbook !

I will try this at work in the morning, however, do I have to set the macro
lowercase w to capital W, secondly, is this Ctrl - Shift - always used when
setting your own shortcut, or do you have to remember the ones already set in
excel?

Regards

If you used ctrl-W make sure you hit ctrl-shift-w (uppercase w).

ctrl-w (lower case) is the shortcut to close the activewindow. And if the
workbook only has a single window, you'd be trying to close the workbook.

Thank you Dave, amended macro as suggested
Got the attachement in the email - tools/macro /run ( personal macro by the

[quoted text clipped - 46 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200910/1

 

[BNT1 via OfficeKB.com]

thank you Dave - took your advise - we are not getting any younger !. have
modified my tool bar and works perfect

If you're using tools|macro|macros (select the macro) and click Options

You'll see that that label for the shortcut key will change from Ctrl to
Ctrl-Shift when you type an uppercase key.

If you want to use an excel shortcut (I wouldn't!), it'll be up to use one that
you don't use very often. There are lots I'd stay away from.

A few years ago, I was interested in what letter keys were available (not
in-use) for shortcuts. (I think in the xl97 days...)

L, Q, T were available in excel
and
E, M, J were used in the VBE, but not in excel proper.

But I don't like using shortcut keys. Once the number of utility macros you
have gets more than a couple, it's difficult to remember (for me, at least).

I'd use a toolbar or modify the worksheet menubar (xl2003 choices)...

======
(Saved from a previous post)

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)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

Well, that explains why it kept closing the workbook !

[quoted text clipped - 18 lines]