Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    New Jersey, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a task where I download a .CSV file, manipulate it in Excel, save it as a .CSV, upload it to a different system, manipulate it again, save it again as a .CSV and then do some more uploading.

    Doing this manually takes me 20 minutes, which isn't bad. I wrote out the steps, (seven typed pages of the steps to take in Excel.) trained someone else to do it and it took them hours to complete. The other person rightly said "I'm not doing this every day!"

    OK. I can see the other person's point. So, I can record macros to do the grunt work. That is (perhaps) not a problem (I don't know, cause I haven't tried to do it yet, but it really shouldn't be). But, before I begin recording macros, would someone please tell me how to manage this overall task.

    Specifically, I download a .CSV file from a system. How do I get my macros into that sheet? How do I set up a series of buttons to run each macro that is needed? (Ideally, it would be one button, but what if I need more than one button?)

    Or, am I thinking this backwards? Should I set up a workbook that contains the macros and have it import the .CSV file, process it and save the modified .CSV files? If I take this approach, how do I keep the macros from being erased, or the sheeet from being changed?

    I need this process to be reasonably simple, so that a person with very little Excel knowledge can do the processing.

    Any help or pointers in the right direction would be most appreciated.

    Thank you....

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,920
    Thanks
    191
    Thanked 719 Times in 655 Posts
    Or, am I thinking this backwards? Should I set up a workbook that contains the macros and have it import the .CSV file, process it and save the modified .CSV files? If I take this approach, how do I keep the macros from being erased, or the sheeet from being changed?
    Mark,

    Yes you should create a workbook that contains only the macros. This workbook will then open the .csv file process and save it.

    Here's a code snipit for opening the .csv file as a second workbook and setting an object reference to it for the other macro processes to use.

    Code:
     
    
    Public oNewWkBk  as Workbook
    Public oCurWkBk   as Workbook
    
    Sub OpenCSV()
    
       Dim zSheetName As String
       Dim zFileName  As String
    
        Set oCurWkBk = ActiveWorkbook
        
        zSheetName = InputBox("Enter the CSV file name" , "File Name Entry")
                              
        If zSheetName = "" Then Exit Sub
        
        Application.ScreenUpdating = False
        
        zFileName = ActiveWorkbook.Path & "\" & zSheetName & ".csv"
                              
        Set oNewWkBk = Workbooks.Open(Filename:=zFileName)
    
    End Sub
    This should give you a start...record away then modify the code so it uses oNewWkBk object.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,431
    Thanks
    7
    Thanked 212 Times in 201 Posts
    How do you download and upload the file? If it's via Internet Explorer this may prove tricky, if it's a copy it's simple.

    cheers, Paul

  5. #4
    New Lounger
    Join Date
    Dec 2010
    Location
    New Jersey, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To PT: The file is downloaded as e-mail. That creates its own special challenge since Outlook Web Access insists on calling the .CSV file an .XLS file. That is not a major problem.

    To Retired Geek: Given that I have done considerable programming back in the "procedural" days, is there a book or two that you would recommend that first is a reference to VBA and second will quickly teach me how to do the programming in the "new" OO way?

    Thanks,

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,920
    Thanks
    191
    Thanked 719 Times in 655 Posts
    Mark,

    For books see this thread. As for a reference use the Object Browser in the VBE {Visual Basic Editor}. You can also check out the MSDN pages.

    I hope this helps.

    I'm basically self taught using books & the internet, with a large dose of help from the lounge. I also have extensive experience in the procedural languages world and it took me a while to wrap my mind around the object oriented world. One thing I find it helpful to remember is that VBA provides the control while the Object Model provides the data end of things. Where VBA is the same across all office products each one has it's own Object Model.

    Good luck and post back when you need more help.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #6
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,431
    Thanks
    7
    Thanked 212 Times in 201 Posts
    Mark, you can use VBA to send email but opening a specific email and saving a file as CSV may be more difficult. I would concentrate on opening the downloaded file, manipulating it and saving it, then let the human handle email.

    Give us a feel for what you need to change and we will give you a start.

    cheers, Paul

  8. #7
    New Lounger
    Join Date
    Dec 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Try a different tool

    Look at the the open source software "R" (just type R into Google). It is very easy to read in a CSV file, process it and then write it out. Since the R package is made for doing statistical processing and graphics, it is a natural match for your problem. Much more intuitive than VBA.

    Quote Originally Posted by neidorff View Post
    I have a task where I download a .CSV file, manipulate it in Excel, save it as a .CSV, upload it to a different system, manipulate it again, save it again as a .CSV and then do some more uploading.

    Doing this manually takes me 20 minutes, which isn't bad. I wrote out the steps, (seven typed pages of the steps to take in Excel.) trained someone else to do it and it took them hours to complete. The other person rightly said "I'm not doing this every day!"

    OK. I can see the other person's point. So, I can record macros to do the grunt work. That is (perhaps) not a problem (I don't know, cause I haven't tried to do it yet, but it really shouldn't be). But, before I begin recording macros, would someone please tell me how to manage this overall task.

    Specifically, I download a .CSV file from a system. How do I get my macros into that sheet? How do I set up a series of buttons to run each macro that is needed? (Ideally, it would be one button, but what if I need more than one button?)

    Or, am I thinking this backwards? Should I set up a workbook that contains the macros and have it import the .CSV file, process it and save the modified .CSV files? If I take this approach, how do I keep the macros from being erased, or the sheeet from being changed?

    I need this process to be reasonably simple, so that a person with very little Excel knowledge can do the processing.

    Any help or pointers in the right direction would be most appreciated.

    Thank you....

  9. #8
    New Lounger
    Join Date
    Dec 2009
    Location
    Los Angeles, California, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    jholtman makes a very good suggestion to look at R. I have used R myself, but found it a painful process because I had trouble finding good documentation. I wonder if someone (perhaps jholtman) could suggest good sources of R documentation.
    Thanks.

  10. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    St Helens, Merseyside, England
    Posts
    23
    Thanks
    2
    Thanked 4 Times in 1 Post
    RetiredGeek is spot on with his advice... I had to do something similar some time ago using a reporting tool that was not capable of presenting the output in the required format so I started with a standard Windows "FileOpen" box to load in the CSV file then finished with the "SaveAs" function that saved the modified file and therby automatically left the one with the macros in it untouched.

    HOWEVER as you seem to be saving the file out as a CSV file as well it seems that formatting is not part of the taskset you want to perform, so I also would suggest looking at a different tool but my one of choice is (open sourse) AutoIT which I have used in the past to extract specific lines of text from a raw logfile and generate SQL statements ready to insert into an Oracle table. It has quite a big set of string mmanipulation functions in it and all the maths operators you need and lots of GUI stuff if you want to make it look pretty as well. I'd be happy to send you the source for the logfile manipulator to look through (assuming that like me -- and from your comments about recording macros it sounds like it -- you find it useful to learn programming languages by example). As an added bonus you can compile your efforts into a standalone exe file so your colleague would only need the executable to be able to process the CSV file.

    Good luck with whatever you decide to use!

    Regards,

    Jim.

Posting Permissions

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