Any VBA Excel Gurus?
the_Grinch
Member Posts: 4,165 ■■■■■■■■■■
in Off-Topic
Ok, we are migrating from a Unix based ERP to Microsoft Dynamics. The reports are text files and I get to help convert them into Excel files. While not difficult (for the most part) it is time consuming and amongst my other duties it would be nice to let things just run themselves. I picked up a couple of books on VBA, but I wanted to see if what I want is possible or if I should just continue the work manually (I will still look to learn VBA either way).
Basically, I have 3 Excel workbooks. One is basically a budget type one with old codes, one is a list of old codes and what their new codes should be, and one is the one that I am creating. I put the old codes on the one I am creating as a reference so I don't lose my place. What I want to happen is this: copy old code from my sheet, search for it in the workbook with old codes and what the new codes should be, find old code then copy and paste 4 columns on the same row (I, J, K, L), then go to my sheet and paste those in the row (using the old code as a reference) in columns A, B, C, and D. From there search the new code in the budget sheet and copy three columns in that row (don't remember what letter the columns are) and paste them back into my sheet in the row (old code as reference) in E, F, and G. I did do some of it, was able to copy the active cell (old code), search it in the workbook, and copy column I in that row.
Thanks, in advance!
Basically, I have 3 Excel workbooks. One is basically a budget type one with old codes, one is a list of old codes and what their new codes should be, and one is the one that I am creating. I put the old codes on the one I am creating as a reference so I don't lose my place. What I want to happen is this: copy old code from my sheet, search for it in the workbook with old codes and what the new codes should be, find old code then copy and paste 4 columns on the same row (I, J, K, L), then go to my sheet and paste those in the row (using the old code as a reference) in columns A, B, C, and D. From there search the new code in the budget sheet and copy three columns in that row (don't remember what letter the columns are) and paste them back into my sheet in the row (old code as reference) in E, F, and G. I did do some of it, was able to copy the active cell (old code), search it in the workbook, and copy column I in that row.
Thanks, in advance!
WIP:
PHP
Kotlin
Intro to Discrete Math
Programming Languages
Work stuff
PHP
Kotlin
Intro to Discrete Math
Programming Languages
Work stuff
Comments
-
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
Grinch,
I assume that you then need to **** this into the ERP system after you are done processing. If you are a Unix guy I would also assume you might already have some Perl skills. Why not user Perl to work with these files? There are now Perl modules for Excel, ParseExcel and WriteExcel. I don't know, might be faster than teaching yourself VBA as you go.