Background: I have a bunch of data that I need to transform, and I was hoping you all could help me automate this process as much as possible.

Data: ~20 Excel workbooks with two tabs: (edges) = an adjacency matrix and (vertices) = attributes about those vertices from the matrix.

Goal: Run a VBA script on each workbook that does two things: (1) converts the matrix to a weighted edgelist and save output as a new CSV, (2) find-and-replace attribute values in the vertices tab and save output as a new CSV.

Challenge: I was able to easily write the code to do the second part, and I was able to find and adapt some code to do the first part, but my challenge is that I don't know how redirect the output of these two separate macros to two separate CSV files. I would also like to save these files if possible. Even further, if I could somehow append "_edges.csv" and "_vertices.csv" to the file names, that would be awesome. Otherwise, saving each one as just "edges.csv" and "vertices.csv" will be fine, as each persons data will be held in their own folder. Also, if there is some way to filter out relationships without a weight (blank) in the edgelist CSV, that would be amazing!!

I've attached a workbook with sample data as well as my existing code.

Thank you for your help!

JohnA..xlsm

Code:
Please Login or Register  to view this content.
Please Login or Register  to view this content.