This is my first post in this forum. I am new to VBA programming. I have taken the VBA Classes offered at Chandoo.org and am trying to solve some real problems I have using VBA. I have lot of experience in programming other languages like Java and C++.
I have posted a similar question in Stackoverflow forum yesterday as I have used Stackoverflow for a long time. As per the posting etiquettes here, cross-posting is not good idea - please forgive me for violating this etiquette. I am not trying to post this question to get an answer quickly, but I really wanted to understand multiple perspectives from VBA experts and there seems to be a lot of them here in this forum. Only after posting in Stackoverflow, I realized that I could have posted here first. If I get solid responses here, then I know where to post my next question related to VBA.
Also, I am not doing a copy-and-paste from that question. I am taking the time to rewrite the question to make it more relevant for this forum as my starting point for this program was the lesson from Vijay Sharma, a teacher in the VBA classes.
Here is my scenario. Please know that I have changed the details to avoid posting confidential data. Also, I have tried to explain the problem using a common customer data set as that is something everyone can relate to easily. I have also tried to talk only about the essence of the problem I have. I can manage the rest of the details, which might add unnecessary complexity to this post.
I have a sheet called 'Details' which has sales details of each customer by product - product name, unit price, quantity, total cost, etc. CustomerID is stored in another sheet called 'Summary'. A number of these files are stored in a directory organised by subfoders based on the person responsible for the customer. I have to do merge data from all these sheets into one target sheet, with customer ID and the sales data by product merged together.
In my current program, I am walking through all the folders starting with the top-level folder and opening each workbook and reading data from the 'Details' sheet and then I paste the data into the destination workbook. After I finish pasting, I read the customerID and fill it in the destination range.
Now, I have to add some columns related to the customer (like country, currency, region, etc.) by using a customer master workbook which stores customer data by customer ID. For products, I have to use a product master workbook and use the name to lookup and get product category and subcategory.
What I need help is to figure out the best approach to solve this problem. I have already written the code for merging the data from multiple workbooks and now I have to add the lookup columns. Here is the pseudo-code of the approach I am about to take:
[pre]
[/pre]
Now, I would like to know what is the best way to solve this problem? This program will process around 500 workbooks and the target workbook is expected to have around 100K rows. So, I need a design which performs well.
I am interested in copying and pasting the values only. I need to do some formatting of the number columns, but I can do that in the VBA code after processing all source files rather than copying and pasting with formatting on.
And I am not sure whether it is good etiquette to include the link to the post in Stackoverflow. Here is the link - please correct me if this is not a good practice here.
http://stackoverflow.com/questions/12680552/how-to-add-some-lookup-data-while-merging-multiple-workbooks-into-one
I have posted a similar question in Stackoverflow forum yesterday as I have used Stackoverflow for a long time. As per the posting etiquettes here, cross-posting is not good idea - please forgive me for violating this etiquette. I am not trying to post this question to get an answer quickly, but I really wanted to understand multiple perspectives from VBA experts and there seems to be a lot of them here in this forum. Only after posting in Stackoverflow, I realized that I could have posted here first. If I get solid responses here, then I know where to post my next question related to VBA.
Also, I am not doing a copy-and-paste from that question. I am taking the time to rewrite the question to make it more relevant for this forum as my starting point for this program was the lesson from Vijay Sharma, a teacher in the VBA classes.
Here is my scenario. Please know that I have changed the details to avoid posting confidential data. Also, I have tried to explain the problem using a common customer data set as that is something everyone can relate to easily. I have also tried to talk only about the essence of the problem I have. I can manage the rest of the details, which might add unnecessary complexity to this post.
I have a sheet called 'Details' which has sales details of each customer by product - product name, unit price, quantity, total cost, etc. CustomerID is stored in another sheet called 'Summary'. A number of these files are stored in a directory organised by subfoders based on the person responsible for the customer. I have to do merge data from all these sheets into one target sheet, with customer ID and the sales data by product merged together.
In my current program, I am walking through all the folders starting with the top-level folder and opening each workbook and reading data from the 'Details' sheet and then I paste the data into the destination workbook. After I finish pasting, I read the customerID and fill it in the destination range.
Now, I have to add some columns related to the customer (like country, currency, region, etc.) by using a customer master workbook which stores customer data by customer ID. For products, I have to use a product master workbook and use the name to lookup and get product category and subcategory.
What I need help is to figure out the best approach to solve this problem. I have already written the code for merging the data from multiple workbooks and now I have to add the lookup columns. Here is the pseudo-code of the approach I am about to take:
[pre]
Code:
For each workbook in the source Folder
Open source workbook
Copy the range from details sheet
Copy the customerID from summary sheet
Open target workbook
Paste range from source.details in the target worksheet
Fill the range in the target worksheet with the first column as customerID
Open workbook with customer master data
Copy data based on customerID
Paste in the target worksheet using fill range
Open workbook with product master data
Copy data based on product name
Paste in the target worksheet using fill range
Process next source workbookNow, I would like to know what is the best way to solve this problem? This program will process around 500 workbooks and the target workbook is expected to have around 100K rows. So, I need a design which performs well.
I am interested in copying and pasting the values only. I need to do some formatting of the number columns, but I can do that in the VBA code after processing all source files rather than copying and pasting with formatting on.
And I am not sure whether it is good etiquette to include the link to the post in Stackoverflow. Here is the link - please correct me if this is not a good practice here.
http://stackoverflow.com/questions/12680552/how-to-add-some-lookup-data-while-merging-multiple-workbooks-into-one
