Posts by temposix

Re: Sheet Copy Limit

I would have thought opening the .xlt first (and keeping it open throughout the process) would solve or mitigate the problem but it seems as though Excel expects to open the template every time the operation is invoked and will not open another file with the same name.

I may have found a solution by selecting the second option in the 'Startup Prompt' button on the Edit->Links dialog box and resaving the template.

It would still be nice to get around the issue programmatically...

Hi, I have been running into the well known sheet copy limitations (Run-time error '1004': Copy Method of Worksheet Class failed) as per kb articles http://support.microsoft.com/kb/210684 and http://support.microsoft.com/d…spx?scid=kb;en-us;Q177634

My solution is to create a template (Example.xlt) and use the Sheets.Add Type:="Example.XLT" method rather than copying the template sheet within the workbook.

The template sheet is far too complicated to generate programmatically and needs to be flexible (changes every now and then).

The issue is that the template is required to link to cells already in the workbook and when I invoke Sheets.Add with the template, it opens every time and asks to update links - I can't work out how to suppress the dialog box 'Update links?'.

Does anyone have a nice solution for this?

Re: return a cell formula's parameters

yeah, ultimately we want to move them into an xll or something compiled but IT don't think business analysts need a visual studio license so i'm looking for a short term solution.

transparency (auditability) in the formulas is also a concern.

i'm nearly there with the parameter extraction through char searching so will persevere.

thanks dave.

Re: return a cell formula's parameters

thanks dave, appreciate the response.

let me give an example, if i have a udf that is a divide function that also checks for 0's and divides parameter 1 by parameter 2:
=DIVIDE(A1,A2)
'if a1 = 10 and a2 = 2 then function returns 5. if a2 = 0 then 0 is returned rather than div/0

and i have thousands within a workbook, it becomes very slow in a big file as you know. what i want to do is replace it with the corresponding if() function:

=IF(A2=0,0,A1/A2)

doing the find/replace will not relocate references or rejig the formula parameters in this fashion.

hope that makes sense, i will continue with my parameter extraction algorithm...

cheers,
paul

hi, i am hoping someone has a quick or pre-prepared way to do this.

i need to essentially find and replace udf's and the complication is rearranging the parameters in the udf's into a new formula structure.

so my question, is there an easy way to extract a parameter (or all parameters) from a function?

for example, if you had =SUM(A1,A2,A3:A6) you would return 'A1' as the first parameter, 'A2' as the second and 'A3:A6' as the third.

Obviously it gets complicated when you nest functions such as =SUM(IF(A2=1,0,A1),A3) etc and the if() function would be parameter 1.

any help appreciated,
cheers.

try this:

=if(and(or(a2="Fr.",a2="Sa",a2="Su"),e41<=$k$47),"","Closed")

does that do what you need?

then i have to convert all of the cells with the old style to the new one.

i have written code to do that but it can take around 15mins on the file i am using and i need to do it to 5 styles : D

thanks dave.

Does anyone know an easy way to simply rename a style?

haha i can't believe you didn't change my blah variable to something less meaningless!

keep the lazy legacy alive! : D

godfodder, have a look at the really basic file attached.

plug a number into the input and your $/employee will follow the lines.

mathematically, i think this may be the best you can hope for within the constraints, ie unless you have more points or a cost curve with an equation.

howzat?

Quote from Diane B.

So is there a formula that will do something like "determine X% of $X if $X is => $25.01 but <$1000.00"? And so on in the next columns for any balances that arise from the original transaction amount?

Diane B.

Diane,

assuming X is in cell A1 and your % is in A2 (change to suit)

this should get you on your way:

=IF(AND(A1>25.00,A1<1000),A1*A2,0)

you may also be better off putting the high and low bounds into cells (for example B1 and B2) so you could do this:

=IF(AND(A1>B1,A1<B2),A1*A2,0)

the syntax of the if statement is

IF(<condition>, then do this, else do this)

so you can put formulas into the THEN and ELSE parts also if you wish to construct a larger formula with all of your original conditions.

let me know if you need further guidance.

Lars, not sure if this will help, had a 2minute fiddle to see what was going on,

Dim blah As FileSearch


directory = "c:\temp"


Set blah = Application.FileSearch


With Application.FileSearch
    .NewSearch
    .LookIn = directory
    .LastModified = msoLastModifiedAnyTime
    .Execute
End With


For i = 1 To blah.FoundFiles.Count
    MsgBox blah.FoundFiles(i)
Next i

sorry it probably doesn't do what you want at this stage but maybe it will help you see a little of what is happening?

ok maybe forget that first point, i am not totally familiar with .FileSearch but i think you either want to set the fname variable before that statement or assign it to the .FileSearch.Filename property after the statement is executed.

my comment about the msgbox is still valid. : D

Quote from Turbothorup

I have this code below.......and I want it to return the filename on the last modified file in the folder I have defined. My messagebox in the end just returns "false".......and I had hoped it would return some kind of filename. Could anyone tell me how I can rewrite my code in order to make it show the filename?

Code

Sub hentUbs()


Findes




End Sub


Function Findes() As Boolean
'   Uses FileSerach object
    
    Dim fname As Variant
    
    
    Directory = "U:\FI-Euro\IG Corporates\HF\Tool Construction\UBS"
    
    With Application.FileSearch
        .NewSearch
        .Filename = fname
        .LookIn = Directory
        .LastModified = msoLastModifiedAnyTime
        .Execute
        Findes = fname
        
        
    End With
    MsgBox Findes
End Function

Display More

Thanks, Lars

hi Lars,

the biggest problem I can see there is that you have the variable assignment around the wrong way, where you have:

.Filename = fname

to do what I think you want to do, it should be:

fname = .Filename

the next problem is your function name 'Findes' is declared as a Boolean so the msgbox will only display TRUE or FALSE.

so, if fname actually gets assigned to a filename, you may want to try:

MsgBox fname

or make the necessary adjustments to the function return type.

Paul.

oh yeah, i forgot to mention that you need to tick a box in macro security called 'trust access to visual basic project'. I don't think there is a way around this.

be aware of the security implications before proceeding.

*this is probably all superfluous to Richie's link...

Phil, it is possible to copy modules, the procedure that I use actually exports the module and then imports it into the new workbook.

something like:

SourceWorkbook.VBProject.VBComponents(ModuleName).Export tempFileName
DestinationWorkbook.VBProject.VBComponents.Import tempFileName

not sure if you can copy individual sub-routines, if not you should just put the required ones into a new module and copy that.

unfortunately it needs to be more self-contained than that.

another option that i toyed with was saving the new wb, closing it and then reopening it.

but that must fire some kind of recalc and thats all i need so...

thanks for the tips, i did try setting volatile but it didn't fix the prob.

i need to try a combination of .CalculateFull; .CalculateFullRebuild and a few things like the replace "=" with "=" thingo.

one of the problems is in xl2003 the find/replace has more options and they are not backwards compatible with xl2000 (not such a big prob) but also the default settings are strange so i need to play with that a little more.

and dave, the reason i am copying the module second is because when i copy the sheets, i am copying to a new workbook (like when you right click a sheet and set the destination as new book and the copied sheets are the only ones there).

anyways, thanks for the help, will keep on and let you know.

I am looking for a general solution to this problem, hoping that there is some method I am missing.

When I copy some worksheets to a new workbook, and on these sheets there are cells who's formulas use user defined functions, in the new workbook the values in these cells will be #NAME?

So I can then copy over the module that has these UDF's into the new workbook.

To get the cells to display the correct value, the only thing I have been able to do is select the cell, press F2, then press ENTER.

Excel then recalcs all UDF's and we have harmony again.

--

This all happens in a macro, I have no problem copying the sheets, then copying the module (provided that security option is ticked) and then I can do a find/replace on one of the UDF's and swap the cell value out for "" before replacing it again.

I have also tried find/replace "=" on each worksheet but that doesn't seem to work either.

Is there a way to get excel to do this automatically?

Thanks for any insight,
Paul.

the way i sometimes do it is to put an '=' in front of the numbers.