Automate long task (Excel 2007)

Topic

New Reply

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….

Reply | Quote

Replies

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

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

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….

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote