Posts by p45cal

  • Format Multiple Listbox Columns

    Re: Format Multiple Listbox Columns


    Untested:Try replacing your blocks of code beginning:

    Code
    For ii = 1 To 10


    with either:

    Code
    If ii = 5 Or ii = 6 Or ii = 7 Or ii = 9 Then
  a(ii, i) = Format(.Cells(r.Row, ii), "dd/mmm/yyyy")
Else
  a(ii, i) = .Cells(r.Row, ii).Value
End If


    or:

    Code
    Select Case ii
  Case 5, 6, 7, 9
    a(ii, i) = Format(.Cells(r.Row, ii), "dd/mmm/yyyy")
  Case Else
    a(ii, i) = .Cells(r.Row, ii).Value
End Select


    p45cal

  • Delete Rows Based On Condition Of 1 Column

    Re: Delete Rows Based On Condition Of 1 Column


    Examining the file in notepad shows it to be comma delimited BUT each field also has a fixed length, padded out with spaces.


    The closest I got to replicating your file structure (by bringing the file into a sheet) was by changing the extension to .txt as parsnip suggested and opening it in Excel, choosing ',' as the delimiter (a comma), making sure all columns were imported as Text. Then I deleted a few rows and used Save As to save the file as a csv file, (the first csv option in the list).


    The saved file might be usable in your Analyser, but the differences included:
    1. Where your original file had no data beyond the MYCALC field it would have nothing. In the saved file there was a string of commas one per missing field datum.
    2. There was an extra field on most lines of the new file where there wasn't one on the original file. Further investigation reveals that your original file's header has an extra comma with no field heading.
    So I reckon only a 40% chance of it being readable and it would need verifying even if it were readable. However, this method would be the easiest for you to delete the lines you need to delete as you can use all the facilities available to you that a spreadsheet offers to help in that. Another difficulty encountered was writing a macro to automate the importing to a sheet; there are so many fields per record that Excel complained that the lines of code were too long or used "too many line continuations", it crashed Excel a couple of times. So I doubt I could do much to automate the process using this method.


    I had much better results with just using Excel to read the original file one line at a time and write that line to a new file if it didn't match your criterion. The resultant files were identical to the original, bar the skipped rows. Now to hope that your Analyser doesn't throw a wobbly if there are fewer lines than it expects in a file, otherwise I'm more than 99% sure it will be readable.


    Could you run this macro for me and test the resultant file in the Analyser? If it likes it, I/you can develop it further, principally to make it more user fiendly (oops, friendly). You'll have to change the lines with the file names to suit your folder structure and file names. The bit which skips the writing of the lines you want skipped is:

    Code
    If Val(Flds(35)) <> 5 Then


    p45cal

  • Prevent Worksheet Scrolling

    Re: Prevent Worksheet Scrolling


    I've just tried this and found that the sheet_activate event is not triggered when the sheet in powerpoint is double-clicked to edit it. However, the selection_change event is triggered on changing the selection. When I tried it, this worked:

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Me.ScrollArea = "A1:F15"
End Sub

    That and removing the scroll bars should force the users to change the selection and thus trigger the event.
    For the scrollarea no longer to be restricted to users outside powerpoint you might get away with a Me.ScrollArea="" in the sheet_activate event!


    p45cal

  • Delete Rows Based On Condition Of 1 Column

    Re: Delete Rows Based On Condition Of 1 Column


    Post small samples of before and after versions of the same file. I'm fairly sure I could make the file save correcly. You may not even have to import the file into Excel, but just use Excel to parse it and create a new one.
    Also include the criteria for deletion.
    p45cal

  • Prevent Worksheet Scrolling

    Re: Prevent Worksheet Scrolling


    Do you have to embed the whole worksheet in Powerpoint? It makes for a big .ppt file.
    If it's just an area you want to display, without the ability to edit, instead of normal copy/pasting, after selecting the area to copy, try holding the shift key while choosing Edit from the drop down menus and a new option will appear - 'Copy picture...' which will open a dialogue box with a variety of options to experiment with. The results are good in Powerpoint and I've found Powerpoint responds/runs more quickly too.
    p45cal

  • Proper Case Text By Condition

    Re: Proper Case Text But Not Specified Words


    Quote from Dave Hawley

    p45cal, I don't think Google have anything to do with David McRitchies site?


    I did say 'A Google'; any noun can be verbed (or should that be 'any verb can be nouned'?).:smile:
    p45cal[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from Reafidy

    Hi,
    You could tye it correctly: P.O. Box 333


    So could you!:smile: p45cal

  • Eliminate Save Prompt In Macro

    Re: Eliminate Save Prompt In Macro


    Please post your code (or if you want to go the whole hog, a cut down version of your excel file and a mini iif file), including the code which opens the file, indicating where it is when you get the message. I've been trying to duplicate your problem but without luck. If there are any variables or objects in the code, please tell us the type and value etc.


    p45cal

  • Eliminate Save Prompt In Macro

    Re: Eliminating The Save Command In A Macro


    Try

    Code
    activeworkbook.Close True


    or

    Code
    activeworkbook.Close False


    to close and save, and to close and not save, respectively.


    p45cal


    ps. it doesn't have to be 'activeworkbook', you can specify the workbook:

    Code
    Workbooks("myfilename.xls").close True

  • Eliminate Save Prompt In Macro

    Re: Eliminating The Save Command In A Macro


    Not verified:


    I suspect

    Code
    Application.DisplayAlerts = False before the line with save/close


    and

    Code
    Application.DisplayAlerts = True


    on the line after..



    OR


    if you don't want to save it:

    Code
    ThisWorkbook.Saved = True


    before the line with close.


    p45cal

  • Run Code To Fill Cells On Drop-down Change

    Re: &quot;auto-completion' Of Data Cells


    By "the auto-fill function without" I'll assume you mean your code as opposed to Excel's autofill method.


    What kind of combo-box is it? One from the Forms toolbar, or one from the Control Toolbox toolbar? Neither of which appears to trigger the worksheet change event when changed and linked to a cell on the sheet.


    Won't Data|Validation with a list not do? This does trigger the Worksheet change event.


    Otherwise, if it's a combobox from the Control Toolbox toolbar then it has its own change event into which you should place your code.


    p45cal

  • Calculate Dimmensions Where All Are In The Same Cell

    Re: Extract Dimensions From Single Cell Entry


    You already have a few (handy references, that is).
    1. Type the single character into cell A1, in cell B1 enter this formula:
    =CODE(A1)
    change the character in cell A1 for more results
    2. Through the menus:Insert|Symbol... and in the dialogue box that shows up, select the character you want to know the code of, then look at the bottom of the dialogue box and the code is displayed there.
    3. Make your own paper copy:
    In cell A1 enter =ROW()
    In cell B1 enter =CHAR(ROW())
    Copy down to row 255
    Copy PasteSpecial Values
    Reorganise for your printer. (see attached)


    p45cal

  • Calculate Dimmensions Where All Are In The Same Cell

    Re: Extract Dimensions From Single Cell Entry


    How has that data got into excel? It might be a lot easier to import the data in a different way from how you're currently doing it so that the values are in separate cells to start with.
    Anyway, if this is a one-off here's one way of going about it:
    In cell B1 enter this formula:
    =SUBSTITUTE(A1,CHAR(34),)
    and copy it down as far as you need. It gets rid of the " characters, leaving:



    Now select all the cells from column B and then press Ctrl+C (or via the menus:Edit|Copy)
    Now select C1 and via the menus:Edit|PasteSpecial... , in the dialogue box check the 'Values' radio button, then OK. This will leave in column C the same as in column B but without the formula behind it.
    Now select the cells in column C and via the menus:
    Data|Text To Columns... , in the dialogue box choose 'Delimited', click 'Next'.
    Here, check the 'Other' checkbox and put a lower case 'x' in the field next to it. You should see a preview. Just click 'Finish'.
    You should now have the three values in columns C, D and E.
    In cell F1, put this formula in to multiply them:
    =E1*D1*C1
    and copy down.


    You could Copy and PasteSpecial Values the result which would mean you could delete all the intervening columns.


    p45cal