+ Reply to Thread
Results 1 to 9 of 9

Improve my Macro!

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Improve my Macro!

    Hi all,

    Hope you winning around the web!

    I've written a macro - all works fine but wondering if it can be improved?

    My expectations are nothing more than the Borg Queen herself - perfection!

    In a nutshell the macro (1) clears a .csv file ready for data input (2) writes some data into that file and (3) saves the file - sounds simple enough right?

    Macro below:

    Please Login or Register  to view this content.

    Any input is greatly appreciated!


    Regards,


    Coeus.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Improve my Macro!

    Since you are writing out a new CSV each time, no need to "open" the CSV in Excel. Just delete the file, then your macro will recreate it. TO my eye this does the same thing without ever opening anything in Excel.
    Please Login or Register  to view this content.
    To be honest, I don't see the purpose of the TEMPLATE file at all. Seems like an unnecessary first step.
    Last edited by JBeaucaire; 08-15-2015 at 03:21 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Improve my Macro!

    This seems to be the same result without the unnecessary TEMPLATE file:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Improve my Macro!

    Hi,

    Many thanks for your reply.

    Id never seen the Kill or FileCopy commands before - learn something new every day?

    Do you recommend any website I could look for commands such as those?

    Otherwise what did you think of the programming? Im learning as I go ;-)


    Thanks,


    Coeus.

  5. #5
    Registered User
    Join Date
    01-22-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Improve my Macro!

    Hi JBeaucaire,

    Just noticed an issue.

    The original macro saved it as a CSV and through opening / saving it got rid of the "" marks.

    When I write directly to the CSV using your macro it contains "" marks - any suggestions please? I see these when I open the .CSV as a txt file.

    Example:

    DATA1,DATA2,DATA3 - old macro.

    "DATA1","DATA2","DATA3" - new macro.



    Thanks for your help!


    Coeus.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Improve my Macro!

    Interesting. Must be the Write command doing that. This is tighter, try this using the Print option.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-22-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Improve my Macro!

    JBeaucaire you are a legend!

    All hail JBeaucaire! All hail JBeaucaire!

    Could you explain the difference between the write and print sections for me please?

    TBH I only learned about that a few weeks ago rather than copy and paste. This method (write / print) seemed much more efficient! Just new with it.


    Thanks again,

    Coeus.

  8. #8
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Improve my Macro!

    Coeus said:
    Id never seen the Kill or FileCopy commands before - learn something new every day? Do you recommend any website I could look for commands such as those?
    Here's a tutorial on some useful commands: http://sourcedaddy.com/ms-excel/mani...d-folders.html

    You have to be very careful using some of the commands, because they may allow wild card characters. A missplaced '*' in a kill command could make you very unhappy, especially since the Recycle Bin is not used. Make sure the items you are working with are in the correct folder if you don't use explicit paths. A recent backup of your files is also very worthwhile.

    Lewis

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Improve my Macro!

    Write: There are two ways of writing data to a text file: you can either use Write or Print statement.

    The output of Write statement is a line in which the data values are separated by commas, while hash marks (#) are around dates and quotes (“) around strings.

    The output of Print statement is a line that is more suitable for printing, with data values being arranged in columns separated with tab or any other delimited character.



    Read: As with the write procedure there are also two ways of reading data from a text file: you can either use Input or Line Input statement.

    With the Input statement data values are read as list of variables. Input statement is used for data that were produced with Write statement.

    With the Line Input statement all the data are read as a single string, so Line Input is suitable for reading data values produced by Print statement.
    (source)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to improve this macro
    By davidm_uk in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-25-2014, 01:15 PM
  2. How to improve this macro to run faster
    By jerrybetie in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-22-2013, 08:50 PM
  3. Improve macro
    By ngkj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2012, 02:25 AM
  4. improve my macro efficiency
    By JieJenn in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-27-2010, 11:13 AM
  5. [SOLVED] Improve macro help
    By fullers in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2006, 06:00 AM
  6. Help to improve macro
    By unni5959 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-19-2005, 07:05 PM
  7. Can you help me to improve this macro?
    By Dario de Judicibus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-15-2005, 07:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1