+ Reply to Thread
Results 1 to 13 of 13

Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrksht

  1. #1
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrksht

    First of all I have minimal experience with VBA. I'd like this to be a learning experience stepwise if this is possible with a single forum thread. So either I could be given chunks of code and learn that way as we go or the whole code and I could ask questions as to how each part works. I've grasped creating macros with simple tasks and with the files open on the desktop in front of me. I need to progress to these operations on whole folders without opening them and closing them manually. Thank you in advance. Sidenote: forums are awesome. I've worked on my car and learned to shave with a straight razor from forums.

    I've been writing small macros to copy/paste columns of data from one open .csv file to a common worksheet in a separate workbook. For example a single folder has a .csv file for each month of data. I open the Jan.csv file, copy column D, paste column D to open AllMonths.xlsx workbook sheet 1. Close Jan.csv file (keeping AllMonths.xlsx workbook open), open Feb.csv file, copy column D, ... ... ...

    Please inform me what you, the experts, need for me to start progressing on creating macros or will it be a VBA code to run operations on whole folders of data.

    I've attached an example .csv file and an example master workbook that I need all column Ds from all .csv files in a folder pasted to.

    Thank you tons in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Have you tried Recording Macros and then viewing the code to see what happens?

    Its a great way to learn VBA.

    Sometimes it will record and others it wont but Im fairly sure that recording one in this case will give you the basic code to do what you're after for a single file. Theres a load of threads on here to then move it on for cycling through all files in a directory etc (including this one from the weekend) > http://www.excelforum.com/excel-prog...ml#post3886832

    Its highly likely you can create a Sub using the Record Macro and then amend it to be used in conjunction with the code in there to do all these files in one go.

    Happy to help out with the next steps.

  3. #3
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    I tried the record macro function but it just recorded me opening each file separately, copy/pasting, closing the file, opening the next file, etc.

    I'll check the link you posted and see if that is a starting point for me.

    Thank you.

  4. #4
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    I tried the record macro function but it just recorded me opening each file separately, copy/pasting, closing the file, opening the next file, etc.

    I'll check the link you posted and see if that is a starting point for me.

    Thank you.

  5. #5
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Sorry for the repeat. New forum and new rules.

    When I tried recording macro I got the following:
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Record a macro starting at the point of opening a CSV file and end it when you close that file.

    Take the code it creates and amend it to receive a string in the brackets after the sub name eg

    Please Login or Register  to view this content.
    then find any references to the csv filename in your code and replace them with strFilename (or whatever youve called your variable)

    Youve now got the basis of a subroutine that can be called by the routine in that other thread, simply replace the Call macro in there with your Sub name

  7. #7
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    pjwhitfield,

    Thank you for the prompt replies.

    I have the following with a: "Compile error: Argument not optional" and the first line of the code is highlighted in yellow (my knowledge of MS VBA software is minimal).

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Apologies, you need to add a Reference to "Microsoft Scripting Runtime"

    In VBA Editor

    Select Tools> References from the drop-down menu
    Tick the check-box next to 'Microsoft Scripting Runtime'
    Click on the OK button

    ps you'll also need a \ on the end of your objFolder

  9. #9
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    As soon as I did that and tried to run it, a window popped up saying: The macros in this project are disabled...

  10. #10
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Fixed the macros issue. Sorry.

  11. #11
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Ran again with the same issue as before.

    "Compile error: Argument not optional" and the first line of the code is highlighted in yellow and "Test1" is highlighted in blue.

  12. #12
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    Replace Call Test1

    With

    Call Test1 objFile.Name

    This passes the name of the file to the subroutine

  13. #13
    Registered User
    Join Date
    02-05-2013
    Location
    Bangor, Maine
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: Novice: Copy/Paste Column D from all .csv files in single fldr and paste to single wrk

    pjwhitfield,
    Sorry for the delay. Awesome man. Thanks for your patience and willingness to do this in an iterative manner.

+ 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. Replies: 3
    Last Post: 10-23-2014, 01:58 PM
  2. Replies: 3
    Last Post: 04-03-2014, 08:14 PM
  3. Macro to copy and paste a single column into several different columns
    By Matt2014 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2014, 08:37 AM
  4. Replies: 5
    Last Post: 05-25-2013, 07:12 AM
  5. Open multiple text files and paste contents to single cell
    By bwalvoord@valuemedical.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2005, 12:05 PM

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