Posts by Mark

    Can anyone help me to update the formulae in a graph-data-range so that I can change the range definitions according to values in other cells.

    for example one cell in the graph data range contains the formula
    ...........(big-compare, little compare, sumif)

    column B contains dates from b6 to b950
    column D contains values from d6 to d950
    so I am reporting on a sub section of all dates.

    I can alter the value of little-compare by changing the value in cell b7
    but I have failed when trying to set the anchor points of the big-compare range in the same way

    I have tried experimenting with macros, but I can't help thinking that I am missinga simple way to do this

    I attach a cut down example

    thanks for any help.

    Hello Derk

    Thank you so much for your time and expertise.
    Everything now works, and in a much more comprehensive way than I could imagine when starting out. From studying your macros I now also have sone new snippets that I can use for other projects.

    Unless you have anything to add (?) I think this thread if finished.



    Yes this works, also it doesn't put the *.prn extension on which is an added bonus.

    I had to run the macro 4 times before I got it to go all the way through because I ran into "out of memory" crashes. This was not due to hard drive storage, and I am not sure why on the 4th try it completed properly/fully............

    Is there a way to get the script to start part way through? for example one time it successfully completed 419 items before crashing, and there are only 430 in my test alltogether. If I could get it to start from 420 that would be useful.

    One other thought - If I run the program without having first deleted any previous *.iss files, I get asked if I want to replace or not. Is there a code setting which will automatically replace?

    Other than these 2 "icing on the cake" items I now have a system which
    1) Macro:
    * determines all possible combinations of components, and
    * writes the set-up program definition file.
    2) Copy list + formula
    * creates the content for a Batch file which runs the setup program for each set-up file/product
    * creates the content for a Batch file which runs the Product-file program for each product - ie tests that the product has been properly built

    Many thanks indeed for your help and persistence as we worked through the system.


    Hello Highly Valued Member, Derk!

    Thank you for the information & explanation.

    I had DELeded every non-A column, however I have double checked and ... a test of ctrl+end leaves the cursor in cell a130, so I geuss all non-col-A cells are empty.

    Despite thie the unwanted formatting is still appearing (not the tab-codes this time, only the extra "'s)

    I have experimented with the saveas command and discovered that the "Formatted TEXT (SPACE DELIMITED)(*.PRN)" format saves a file with the propper formatting.

    please could you tell me the appropriate macro command to replace
    .SaveAs s.Name & ".iss", -->xlTextWindows <--
    (I tried the help function in macro-visual-basic but it has Never been able to find all its files so no luck this time either)

    Saving the files in this format manually insists on appending .prn to the .iss file, ie I end up with *.iss.prn
    Using the macro to saveas is it possible to save the file in prn format without the .prn extension?
    (if not I can always use the product list to create the content for a batch file which renames *.iss.prn as *.iss; but it would be nice to be more elegant - and it is very informative to see how you have solved the problems along the way.)

    macro to write out text file from worksheet

    Hello Derk


    Quote from Derk

    The new sub should create and save an ISS sheet for each line. ...

    yes it works as suggested wirting a new .iss file for each product.

    However the .iss files contain formatting which interfers with the operation of the ISS program. Specifically Tab-code is included and there are many extra " " " symbols. I think you may need to refer to the original XL attachment file*iss worksheet to see that many of these are extra to requirements. Unfortunately the extra formatting does not show up if I copy the output into XL to do the comparison

    I wonder if there is a way to specify a file save-as type which does not add extra "'s and tabs?

    Here is an example .iss file (I hope it displays online the way it shows up locally )
    ; Script generated by the Inno Setup Script Wizard.
    ; Files for
    ; INNO setup Speech file name list.xls / INNO.bat /

    "Source: ""Read-me-first.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""offline-*.*""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""favicon.ico""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""*File.txt""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-bm-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-bm-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-fob-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-fob-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-grm-a.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""speech-grm-b.htm""; DestDir: ""{app}""; Flags: ignoreversion"
    "Source: ""_Wedding_Jokes.html""; DestDir: ""{app}""; Flags: ignoreversion"

    "; NQTE: Don't use ""Flags: ignoreversion"" on any shared system files"


    SourceDir=D:\My Documents\\public\www\components\
    AppName=Fine Wedding Speeches
    AppVerName=Wedding Speech Pack Contents
    DefaultGroupName=Fine Wedding Speeches

    "Name: ""desktopicon""; Description: ""Create a &desktop icon""; GroupDescription: ""Additional icons:"""
    "Name: ""quicklaunchicon""; Description: ""Create a &Quick Launch icon""; GroupDescription: ""Additional icons:""; Flags: unchecked"

    "Filename: ""{app}\Read-me-first.url""; Section: ""InternetShortcut""; Key: ""URL""; String: """""

    "Name: ""{group}\1-Your Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm"""
    "Name: ""{group}\2-Fine Wedding Speeches Help on the Web""; Filename: ""{app}\Read-me-first.url"""
    "Name: ""{group}\4-Uninstall Fine Wedding Speeches""; Filename: ""{uninstallexe}"""
    "Name: ""{group}\3-Terms & Conditions""; Filename: ""{app}\LicenseFile.txt"""
    "Name: ""{userdesktop}\Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm""; Tasks: desktopicon"
    "Name: ""{userappdata}\Microsoft\Internet Explorer\Quick Launch\Fine Wedding Speeches""; Filename: ""{app}\Read-me-first.htm""; Tasks: quicklaunchicon"

    "Filename: ""{app}\Read-me-first.htm""; Description: ""Launch Fine Wedding Speeches""; Flags: shellexec postinstall skipifsilent"

    "Type: files; Name: ""{app}\Read-me-first.url"""

    Once again, many thanks for your help


    Combination -&gt; build product definition file

    Quote from Derk

    4) I don't understand what you want. Is it to take the horizontal list and make it vertical? Where would it got for each line. Can you give a fuller example?

    Hello Derk

    Glad to be back after the 'break' :)

    * your revised macro has worked brilliantly. I have tried it out and studdied it. It is still way beyond my coding ability but I have understood it enough to be able to add and subtract products from the combinatins. So for me now it is a very useful tool. Once again thanks.

    As regards point 4.

    Each of the product combinations we can now list tells me which components I want in the eventual product file. The Macro also defines for me the name of the file.

    Next I have to write product definition files (*.iss) for a setup.exe creation program called INNO setup. I need one *.ISS file for each product file I am building.

    Each line in the INNO setup *.iss file determines something about how the final product file will behave on the customers computer. Some of these lines determine which components will be built into the product setup file.

    To cut a long story short(er) for each product name defined by the macro I will make an *iss file which then produces the product file that the customer downloads and clicks on to start the setup.

    macro ---> b2+f5+g5+J+OT ---> b2+f5+g5+J+OT.iss ----> b2+f5+g5+J+OT.exe

    In the attached file I have an example iss file this includes all components in blue text, and the 'standard' text in black. Normally I would delete the unwanted component lines and then copy the text to notepad and save the text with a product-file-name.iss

    If there were some way to :-
    * for each product combination identified by macro
    * insert appropriate components to the "file.iss" sheet
    * then save sheet as text with file name = path/product-name.iss

    That would be ideal.

    Coplications i can foresee:-
    * in macro/product name I can say "2 best man speeches = b2"
    in the file.iss I need to specify each component as speech-bm-a.html, and speech-bm-b.html

    * the blue text in file.iss is not all in one block

    Anyway Derk,
    Thats what I was thinking of, once again thank for your interest, and any help is much appreciated.


    Hello Derk

    Thank you for your comments and macro. I have spent the last couple of hours trying it out - very clever! way beyond my ability. The results I can understand.

    Please may I ask you some follow on questions?

    [1] you say...there are 3 possibilities for the groom, 3 for the best man, 3 for the father, and 2 for the each of the others .. how did you determine this? I would have though there were 2 options ie groom2 or groom5 speeches? the start of your macro seems to depend upon this determination in order to work out the number of combinations.

    [2] I have fiddled with your macro so it puts "grm,fob,bn,J,QT" in the columns where the x's are. Is it possible to easily change the macro so it puts text such as "grm2,grm5,bm2,bm5,fob2,fob5,J,QT" in the relevant columns. In this way each column would contain a description of the component that I need to add to each 'product'.

    [3] in the attached file, when I try to build a file-name based on the components within the file I end up with all sort of spurious characters in positiions for null components.
    eg I get GRM2++BM5++FOB2++J+QT
    when I would like GRM2+BM5+FOB2+J+QT
    How could I get the macro to list the component names in a form like this?

    [4] Now that I have seen what is possible and fired with creative ideas I wonder if it is also possible to take the list in [3] and write a line by line bread down of each component
    eg for the above

    This way I could use each [4]list in the program I use to build the product.

    If asking all this is an imposition, my appologies, I am really appreciative of having now an accurate list component mixes which I can work with on paper; your help just seemed like the start of such good ideas <smile&gt;


    Hello & Thanks to all for your interest.

    Sorry my details were insufficiently clear if I discuss the context it may be explain what I am trying to do ....

    I wish to build "products" that will help wedding speakers. I want to offer various components which may be purchased individually or in combinations.

    * 2 speeches pack - Groom
    * 5 speeches pack - Groom
    * 2 speeches pack - Best Man
    * 5 speeches pack - Best Man
    * 2 speeches pack - Father of the bride
    * 5 speeches pack - Father of the bride
    * selected Jokes/Humour
    * Selected Toasts & Quotes

    (oops, that's 8)

    For example a customer may want just 2 example speeches,
    or a customer may want to buy speeches for themselves and the other speakers, with or without extra humour and or Quotes & Toasts.

    I feel sure excel can tell me how many combinations I am looking at, and a list of them, but I don't know how.

    Who knows when I see how big the list is I may decide to cut the number of components <wry grin&gt;

    Here's to hoping you can help

    I thought I knew how to do this, but I am stumped by what must be a basic issue. I have spent an hour searching this forum, but to no effect.

    I have seven "components" which I want to combine in different ways to make "products"

    I want to find out the number of ways I could combine the different components to make different products. The =COMBIN formula doesn't do it properly (ie for all possible combinations)

    Having found out this total it would also be nice if I could get a list of the possible combinations <g&gt;

    Help much appreciated


    Thank you all, Thank you all [Official repeat!!]

    I have just returned home after some days away over Easter & hadn't seen the messages for a while ... Thank You for the help and explanations. I haven't experimented with the onkey stuff yet, but will do so shortly. All other info. has been used!.

    As an untutored 'dabbler' in getting code snippets to do things, rather than a properly taught programmer I appreciate your help with stuff that might seem very simplistic.



    progress report:
    helped by Chris I have now solved how to Maximise the windows of open spreadsheets within the XL application...

    ActiveWindow.WindowState = xlMaximized

    I could not get it to work for a long while, because sheet/workbook protection was ON. By reordering the lines in the module so that Run-protection-on macro is after the above, it now works.

    So the answer to my first two problems have turned out to be ...
    Application.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMaximized
    but protection needs to be off, for the second line to work.

    Meanwhile:- Does anyone know the macro code to temporarily disable the use of keyboard-shortcuts-for- involking-macros?


    Hello Chris

    Thank you! for your excellent reply. Already tested and implemented!.

    I felt sure that these ideas must be of the "simple when you know how" variety, but could I find the answers elsewhere? NO I couldn't :)

    Any ideas about how I can maximise the current-window having maximised the XLapplication? For reasons unknown to me, one of my other macros causes the current sheet to 'cascade' within the application window, even though it is the only window open.

    Many Thanks


    I have looked in my reference book, and I have looked through the XL help file and this forum's posts, I have also found and used the search function, without success.

    Plese would someone tell me where (under my nose) I can find out how to:

    * Maximimise the XL application window
    * Maximise the windows of open spreadsheets.

    * Turn OFF the ability to press Ctrl+Break in the middle of a macro
    * Turn off keyboard shortcuts to run macros (but not to delete the shortcut as I'd like to be able use them at other times)


    Thank you

    I have had a look at the ExcelEverywhere site, what a useful and clever idea, to put excel on the web.

    My spreadsheet is now over 800kb big, and uses the largest number of macros that I have ever cobbled together, so I think it is inappropriate for ExcelEverywhere.

    It is looking as though I can't run a spreadsheet independently - it would have been nice/useful.

    ?? If we have established that there is no solution, do I still mark the thread as 'solved? ??


    Thank you for your prompt reply, much appreciated

    I hope you won't mind if I keep the thread open for a while to see if someone esle knows of a way.

    Meanwhile, looking through the messages I have found an asnwer to my NEXT question ... Talk About Service


    I have designed a spreadsheet that I want to make useable for people without MS-Excel.

    XLviewer is no good as it doesn't let peole enter data or run the macro buttons.

    Does anyone know if this is possible (& permissable)?

    I think this is possible because when searching the net I found references to a program XLSEXE which claimed to do this, although it was several years old. but I could not download it from any of the links.

    Any info welcome.