+ Reply to Thread
Results 1 to 3 of 3

Create CSV files from other CSVs and copying data

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    8

    Create CSV files from other CSVs and copying data

    Hi All
    I am very new to this stuff and would appreciate some help.

    I have a bunch of CSV files that I need to take certain bits of data from then create a new CSV fille.

    Here is my thought process
    1) Pick up a CSV with name beginning 'Per'
    2) Copy column A to Column A
    3) Copy column C to Column B
    4) Copy column E to Column L
    5) Copy column F to Column P
    6) Fill the blanks in between the filled columns with NA
    7) Create a new CSV called 'N_Per'

    Ideally I would like to run it as a batch file in a folder and that would send all the files into a new folder.

    Does this sound difficult?
    Thanks in advance
    D

  2. #2
    Registered User
    Join Date
    08-15-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Create CSV files from other CSVs and copying data

    Just to add a little complexity I just realised I need to add an additional step
    6a) Copy rows 1-7 into the new file (this would need to be done last so as not to be overwritten by step 3)
    Last edited by funkydim; 11-29-2012 at 11:41 AM.

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Create CSV files from other CSVs and copying data

    This is what I have so far, which does all the copying to the new worksheet and works well:
    Sub AddWB()

    Dim MyActiveSheet As String

    'grabs the active sheet's name in Support
    MyActiveSheet = ActiveSheet.Name

    'adds a new workbook
    'when you add a workbook it becomes the new active workbook
    Workbooks.Add

    'saves the new workbook as Summary.xls in the C drive
    ActiveWorkbook.SaveAs Filename:="C:\csv\output\Nper123.csv", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False

    'copies columns from per123.csv and pastes them
    Workbooks("per123.csv"). _
    Sheets(MyActiveSheet).Range _
    ("A:A").Copy _
    Sheets(1).Range("A1")

    Workbooks("per123.csv"). _
    Sheets(MyActiveSheet).Range _
    ("C:C").Copy _
    Sheets(1).Range("B1")

    Workbooks("per123.csv"). _
    Sheets(MyActiveSheet).Range _
    ("E:E").Copy _
    Sheets(1).Range("L1")

    Workbooks("per123.csv"). _
    Sheets(MyActiveSheet).Range _
    ("F:F").Copy _
    Sheets(1).Range("P1")

    Workbooks("per123.csv"). _
    Sheets(MyActiveSheet).Range _
    ("A1:Z6").Copy _
    Sheets(1).Range("A1")

    End Sub

    My main problem is how to make it generic/dynamic. i.e. in the above code i have to specify both the file i am looking up and the new files name. As i will need this in bulk I need it to work dynamically, maybe using cell B1 to create a new name.
    Once that is solved I need to figure out how to batch it and run en masse.

    Any tips or tricks would be very helpful.

    Thanks in advance

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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