How to automate the format change work using VBA?

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    https://chandoo.org/forum/thre…ke-my-task-simpler.46933/

    Hi All,


    Please help me in atomizing a task which i do frequently which is just killing my time. I have a system generated file NIFTY21SEPFUT (CSV file). When u open the file u will see dsta starting with the date which includes the time, then NIFTY21SEPFUT O (Open), NIFTY21SEPFUT H (High), NIFTY21SEPFUT L (Low), NIFTY21SEPFUT C (Close) & NIFTY21SEPFUT V (Volume). I use this file for backfilling my data in my charting software so that my data is updated.


    In order to backfill the data, the data contents and the headers should be arranged in the below order.


    Trading Symbol Date Time Open High Low Close/Price Volume

    NIFTY21SEPFUT 16-09-2021 15:29:00 17610.35 17612.6 17607.6 17608.05 39000

    NIFTY21SEPFUT 16-09-2021 15:28:00 17612 17614.7 17610 17612 40200

    NIFTY21SEPFUT 16-09-2021 15:27:00 17613.45 17613.45 17610 17610.3 36450

    NIFTY21SEPFUT 16-09-2021 15:26:00 17613.05 17614.1 17612.75 17613.5 30400

    NIFTY21SEPFUT 16-09-2021 15:25:00 17613.85 17614.6 17611.65 17612.85 29000

    NIFTY21SEPFUT 16-09-2021 15:24:00 17609.55 17615.5 17609.55 17614 39500

    NIFTY21SEPFUT 16-09-2021 15:23:00 17609.95 17611.6 17609.3 17609.75 18900

    NIFTY21SEPFUT 16-09-2021 15:22:00 17608.6 17610 17606.45 17609.95 18250

    NIFTY21SEPFUT 16-09-2021 15:21:00 17606.85 17609.25 17605.6 17608.5 26500


    In the BFU file which I'm attaching along with this message when the user types the format of the trading symbol mentioned in the yellow area and as soon the user clicks the "Insert file" Button. A new csv file has to be created with the name of the file being mentioned in the yellow area. In this eg; NIFTY21SEPFUT (CSV File). As soon as the file is opened the data format should be as above. Note: There should be no alterations done in the system generated file NIFTY21SEPFUT (CSV). file). Hope u all got the idea.


    Along with this message I'm attaching the files system generated file NIFTY21SEPFUT (CSV file) which is a system generated file and BFU were the vba has to be coded so that i get the desired result.


    In case of any queries do message me ok... I'm not able to attach the system generated file NIFTY21SEPFUT.csv since its telling "The File extension is invalid."BFU.xlsm


    Thanks & Regards

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you! Read this to understand why we ask you to do this https://www.excelguru.ca/content.php?184v