Replies

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

RDO,

You can add a reference to the XLA or call it this way
application.Run “OtherWorkbookName.xla!Macro1”

I know this works with .xls files but don’t know if it will transfer to
.xla’s.

RG

Thanks RG.

But from tests so far and based on the documentation, I don’t think this method will work. From below extract from the documentation .run only passes parameters as values.

“Remarks
You cannot use named arguments with this method. Arguments must be passed by position.

The Run method returns whatever the called macro returns. Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method.”

OK, so this is only when the date is 31-12-yy. Then by adding 1 day the year comparison actually gets “out of sync”, regardless that you are actually comparing the value to itself… – I like that – clever solution.

Thanks.

Hi Don,

Thanks for sharing the insights. It’s a refreshing different approach and a lot more simple than my initial one. So I think it looks really good.

A single question to the formula in Row 15 though (taken from cell F15):

=F11&” “&IF(YEAR(I10+1)YEAR(I10),
TEXT(YEAR(I10),”0000″),
TEXT(MOD(YEAR(I10)-1,100),”00″)&”/”&TEXT(MOD(YEAR(I10),100),”00”))

The test in the IF condition (IF(YEAR(I10+1)YEAR(I10)…) – when will that ever be true, and if it won’t, why is it there in the first place??

I’ve tried to modify your suggestion further, but keep ending up in incorrect situations. So – so far what you’ve provided seems to be the most stable solution.

Thanks,

Depending on how you are moving the files, you may be able to run a BAT file that uses cacls or Icals to modify the file permissions after you have moved them.

Hi Stu,

Thanks for the quick reply. I’m not quite familiar with cacls and Icals, can you elaborate a bit on that?? – please.

In fact a BAT that applies “Full control” to all files found in the “Main” folder would be sufficient

;o)

No, not that I know of, if you are talking about number formats?

hmmmmm
– thanks for the quick answers. Guess I’ll have to sleep on that one then.

I think the point is that there is no auto creation of named ranges (hence the lack of setting). (other than built-in ones like querytable ranges, print areas etc.)

Maybe I’m confusing this with auto creation of styles in Excel as well…??
– is there a setting that turns this off?

I’m not aware of a setting that automatically creates defined names…

Hi hans,

I was more thinking it the other way around – a setting that could turn off the auto creation of defined names.

Does this look like a “safe” place to get it? This fella’s a MS MVP, so I would think it’s OK:

Download Free Windows 2000 Resource Kit Tools

Hi Al,

Any particular reason why you chose Win2000 and not eg. Win XP or Vista??
– are there no resource kits there or is FORFILES not available for those?

(Haven’t had time to look further into this – so apologies if it sounds like a dumb question)

TIA