+ Reply to Thread
Results 1 to 5 of 5

vba code to edit a csv file in notepad and replace data

  1. #1
    Registered User
    Join Date
    03-21-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2008
    Posts
    16

    vba code to edit a csv file in notepad and replace data

    Hi,

    I create a .csv file via a macro using multiple excel files. Once created (file is used for import to a system) I need to edit the file in notepad, replacing commas with semi colons. The file then needs to be re saved as a .txt file.

    I can open the file in notepad via vba (Workbooks.OpenText Shell("Notepad.exe " & filename,1) , but am unsure on how to replace the data.

    Any help will be greatly appreciated!

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: vba code to edit a csv file in notepad and replace data

    Why not just save the file as a txt file to begin with?
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    03-21-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2008
    Posts
    16

    Re: vba code to edit a csv file in notepad and replace data

    Hi Mike,

    I need both a csv file and a text file for input to different systems. Saving as a text file does not give me semi colon separated columns.
    Any other suggestions?

    Thanks

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: vba code to edit a csv file in notepad and replace data

    Can you upload a small sample of your workbook with sample data with exact data structure.

  5. #5
    Registered User
    Join Date
    03-21-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2008
    Posts
    16

    Re: vba code to edit a csv file in notepad and replace data

    The data structure is actually variable, month on month, hence I just need to do a simple find and replace all.

    Example lines of data from csv file:
    TT10,Dave,1.67,0.00,590.62,0.00,0.00,0.00,0.00
    TA10,Sid,767,0.00,25,0.00,0.00,85.04,0.00


    Anticipated outcome for text file
    TT10;Dave;1.67;0.00;590.62;0.00;0.00;0.00;0.00
    TA10;Sid;767;0.00;25;0.00;0.00;85.04;0.00

+ 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. VB Code to change format, save in notepad file
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2016, 03:24 PM
  2. [SOLVED] Copy Excel data to Notepad file
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-19-2015, 11:01 AM
  3. Getting data from a notepad file
    By Jenkins27 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 03-01-2015, 10:37 AM
  4. Opening Notepad a saved notepad file and copying the data on it
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2014, 06:18 PM
  5. problem with pasting data from notepad file
    By hunterage2000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-13-2014, 06:00 AM
  6. Extract Data from Notepad file into excel
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-21-2012, 01:31 PM
  7. [SOLVED] Analyze & Filter data in a notepad file
    By Rudodoo in forum Excel General
    Replies: 1
    Last Post: 12-01-2005, 06:00 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