Posts by cytop

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


    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.


    Wouldn't that work...

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


    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?


    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.


    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.


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

    Yep - nothing complicated there.


    remember to enter ~ as Line End Indicator of each line

    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

    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

    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

    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

    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.


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

    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.


    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)