Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post

    Inserting file name in an excel file

    I have several .csv files in a folder NEW

    EQ200116
    EQ210116
    EQ220116
    EQ230116
    EQ250116
    EQ270116
    EQ280116
    EQ290116

    basically the filename consists of "EQ & date DDMMYY".


    I want to make a VBA excel that would put the date part in entire column N of the respective files.

    attaching excel file for your reference
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    bhushanvshah,

    In your sample, the sheet name is EQ290116 which is also one of the names of the files. I will assume that the data on the sheet is all the data contained in one file and that each file has its own sheet...correct? Click the button and column N will be filled with the date part of the sheet name. You did not specify that the macro include code to import the files.

    HTH,
    Maud

    Code:
    Public Sub AddColumnN()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    nme = Mid(ActiveSheet.Name, 3, Len(ActiveSheet.Name) - 2)
    For I = 2 To LastRow
        Cells(I, 14) = nme
    Next I
    End Sub
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    20
    Thanks
    5
    Thanked 1 Time in 1 Post
    Dear Maudibe,

    Thanks for your reply..

    i want to make it more simple.

    all 30 csv files
    EQ200116
    EQ210116
    EQ220116
    EQ230116
    EQ250116
    EQ270116
    EQ280116
    EQ290116 etc are in the folder "C:\NEW"


    once i run the macro from a seperate excel file,
    in each file i want to add the date component of the file name in column N (columns A to M contains data upto several rows)

    thus it will become a fully automatic macro

    rgds

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by bhushanvshah View Post
    thus it will become a fully automatic macro
    Since you can't store macros in CSV files, that's not going to happen. In any event, you don't really need to open the files in Excel to add the 'date' part to each line - that could be done via a vbscript that just processes the CSV files as text files (which is all they really are).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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