Posts by cytop

    Re: Unable to run the Private Sub Worksheet_Change(ByVal Target As Range) Macro



    Quote

    can this code be run from Module


    Not as Event code - you can add it to a module but you need to add code to the Event handler to call the procedure.

    Re: Pipe "|" Delimiter CSV?


    Opening a file 'For Output' will create the file if it doesn't exist, or truncate any existing file.


    Quote

    Wouldn't that work...


    Yes. You can do that if you wish, but there is no particular advantage doing it that way.


    Quote

    The "~" is already in the last column


    Just covering all bases. You suddenly mention something that is non-standard for a CSV file so tried to cover that. If the character is already in the cells then it will be included.

    Re: Pipe "|" Delimiter CSV?


    Quote

    can I edit this file name code to generate dynamically something like...


    Yes, I just used a simple fixed file name as an example. Just be aware that any existing file with the same name will be truncated (effectively overwritten, without warning) as soon as you open the file.


    Quote

    Will this work as is with a csv file


    No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.


    Quote

    this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file


    Yep - nothing complicated there.


    Quote

    remember to enter ~ as Line End Indicator of each line


    Code
    For i = 1 To UsedRows 
        For j = 1 To UsedColumns - 1 
            Print #1, .Cells(i, j); "|"; 
        Next j 
        Print #1, .Cells(i, UsedColumns) & "~"
    Next i


    One thing not clear is if a New Line character is added after that. If it is then no problem but if the other program interprets '~' as the end of the line then it will need a ";" as well

    Code
    Print #1, .Cells(i, UsedColumns) & "~";


    Note the semicolon is outside the double quotes.


    You can"Fill in the data in one cell" if you wish and saving that as a CSV file will work as there's only one cell to export so no field delimiters (',') are needed. Getting all the information into 1 cell, adding the pipe delimiters and the ~ line terminator would be a pain, though.

    Re: Macro running in every workbook


    Something to remember - Bold tags do not work inside of Code Tags...


    A 'more correct' way of writing it would be to use

    Code
    Set wks = ThisWorkBook.Worksheets("Charts")


    That way the code can only ever refer to a worksheet in the current workbook, not the Active workbook and will continue to work after you change the filename (Some day, you will).

    Re: Pipe "|" Delimiter CSV?


    Thank you again, then.


    Just to finish off your post, a generic procedure to replace all occurrences of 1 string with another in a file.


    Instead of just replacing delimiters it can also be used to replace all occurrences of 'Ford' (for example) with 'Chrysler' - although 'Chrysler Mustang' does not roll off the tongue too easily!



    Optionally, it creates a sequentially numbered backup of the original file.

    Re: Pipe "|" Delimiter CSV?


    Hardly... I labeled it 'Quick & Dirty' :) but Thank you.


    I will make 1 change. The lines

    Code
    For j = 1 To UsedColumns - 1 
                    Print #1, .Cells(i, j); "|"; 
                Next j 
                Print #1, .Cells(i, UsedColumns)


    Writes the values of the cells, but you can use

    Code
    For j = 1 To UsedColumns - 1 
                    Print #1, .Cells(i, j).Text; "|"; 
                Next j 
                Print #1, .Cells(i, UsedColumns).Text


    to write the formatted (as displayed) values rather than the underlying values.

    Re: Pipe "|" Delimiter CSV?


    You can't 'natively' save a file using delimiters other than a comma, tab or space.


    You can change your computer settings to use '|' as the text delimiter.


    Access can be used to export a table (as a linked table) using the '|' as the delimiter, although you may not have a copy of Access to hand. You can use ADO/DAO to open an Excel file as a database and export that but that means more code and more complication and the worksheet must be a simple list like a database.


    As infomage said (posts overlapped), you can save as a 'standard' CSV and edit the file. This can also be automated - again more code and more complication.


    The only other alternative is to use VBA to write to a file using '|' as the field delimiter



    (Quick & Dirty)

    Re: How to Call an Event in Modules


    Standard code modules cannot host Events. Any event code for an ActiveX control must be in the code 'module' associated with the object raising the event (Worksheet/Workbook class, 'Standard' class, UserForm).


    Shapes or Form Controls are something different. With these you define a Macro to run when the object is clicked (Right click the shape/Assign Macro). This macro must be a public Sub in a standard code module.


    It might be useful if you uploaded a copy of your workbook if you cannot work it from that.


    [sw]*[/sw]

    Re: Can someone please shorten this VBA code for me? Loop code - Should be simple


    Code
    For Each e In Sheets("Data").Range("A3:J3") 
            If e.Value < 5 Then 
                MsgBox ("Hi") & e.Offset(-1).Value 
            Next e 
        End If


    Putting code like that in the Calculate event is not a good idea, IMO. You should explain the logic, there will be a better way.

    Re: Insert row after specific text


    A simple edit of one of the 'Possible Answers' (insert page break in cell contains specific Text) below.



    Please take care to post in the correct sub-forum. You originally posted this in the 'Newletters' sub-forum but the thread was moved to the 'Excel/VBA Macros' forum which is slightly more relevant to your issue...

    Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error


    My language skills must be slipping - I didn't ask any questions in this thread. Not one. Oh, OK. Let's be pedantic. I asked 2 rhetorical questions, both answered by myself. But, the issue of me asking questions rather than providing a solution, is made out to be the problem.


    Apologies Kenneth, I do not accept this is a 'Language Barrier' - The OP, in my opinion (Note the my opinion, not that of OzGrid, its management, employees, associates or any volunteer, or indeed any other person, registered on this forum), is exactly the sort of poster described in that link.


    I will not withdraw that statement. If anyone has a problem with that, then this post will have to be edited to remove it.


    The thread has been closed. I don't expect another thread to continue this will be opened.

    Re: VBA environment variables


    You need to decide how these users who can save without filling in the data are to be identified.


    It could be as simple as asking for a password if the cell(s) are blank when the file is saved or as complex as you can dream up; but, in general, it would be best to avoid any hard-coded lists of users which have to be edited every time someone leaves or is hired.

    Re: Consolidate data from multiple workbooks to one Master Workbook, Code Error


    Last word on this... At times questions are inevitable because the post is not clear or does not give enough detail about the data. Compare your post #1 with post #1 in this thread Both are vague with no specific information.


    Now compare the results when [sensible] questions are answered in a sensible way. Result? Someone has working code, someone else doesn't.


    And the infraction is not 'revenge' - it is recorded in a private forum so anyone with the right permissions can see your posting history at a glance.


    This, also, is not 'revenge' - I have no interest in trying to resolve your problem for the simple reason that you have never explained it clearly enough. Your 'explanation' in post #5 was simply to refer to post #1 which contains no information other than "if i add more items (up to 5 or 6 items), produced wrong values at "MasterData" Sheet". How you ever expected anyone to answer that without the benefit of seeing the code or your workbook is beyond me.


    Quote

    if you not able to take challenge please dont waste my time for asking un useful questions


    Had to stop myself falling off the chair laughing at that - no one could "take challenge" based on the information in your first post so questions were inevitable.


    I also do want want to hear from any other members that you sent them private messages demanding to know why your issue has not been resolved.


    Goodbye, but welcome to my Ignore list.

    Re: Create &amp; Save filtered list into new workbook


    Quick and dirty...


    All the code goes in a module in the workbook to be copied. It could be speeded up a bit as it is mostly linear code without much optimisation but it seems to work.


    Assumptions have been made - the workbooks will be saved in the same directory as the 'parent' workbook; you will get prompted if a file already exists when saving, it will not overwrite.


    (The code formatting seems to have an error - that red line compiles OK in Excel)