Hello All, This is my first post in this thread, and I am new to VBA as well so I need the help from all of you guys.
What I want to do is
I have one excel file name Result.xlsx, which contains two Sheets named Sheet1 and Sheet2.
What I want to do is the sheet2 should read all csv files from a particular location(one by one), and it should consolidate the data into single that is put all the csv's files data into Sheet2.
Please help
you can arrange this
Please Login or Register to view this content.
Last edited by patel45; 01-01-2013 at 10:02 AM.
If solved remember to mark Thread as solved
Another method. Should be speedy if you have a lot of files to consolidate.
Please Login or Register to view this content.
When I am executing this It shows the error, The error is
Run-time error '1004'
Application-defined or object-defined error
Sorry, This thread works But I am getting a issue- The data is the csv files are like this
ESP Client,ESP Engagement,Misc_Projects_120101,DEFAULT,HA,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected-Done,"she,joy.",200111,Unknown,Full Time,,Delivery_DONE AMO,Approved,2012-12-03,2012-12-06,2012-12-06,"Occupied Hours
(0)",0,"Approved Hours
(112)",8,"Pending Hours
(0)",0,"Pending and Approved Hours
(112)",8,
ESP Client,ESP Engagement,Misc Projects_120101,DEFAULT,HR,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected - Pending,"she, aj v.",200111,Unknown,Full Time,,Delivery_Pending AMO,Approved,2012-12-04,2012-12-14,2012-12-14,"Occupied Hours
(0)",0,"Approved Hours
(112)",8,"Pending Hours
(0)",0,"Pending and Approved Hours
(112)",8,
But I want the output to be written in the consolidated sheet is in a single line
ESP Client,ESP Engagement,Misc_Projects_120101,DEFAULT,HA,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected-Done,"she,joy.",200111,Unknown,Full Time,,Delivery_DONE AMO,Approved,2012-12-03,2012-12-06,2012-12-06,"Occupied Hours (0)",0,"Approved Hours(112)",8,"Pending Hours(0)",0,"Pending and Approved Hours(112)",8,
ESP Client,ESP Engagement,Misc Projects_120101,DEFAULT,HR,Unknown,No,Unknown,201704,4.1,Unknown,AAA,Collected - Pending,"she, aj v.",200111,Unknown,Full Time,,Delivery_Pending AMO,Approved,2012-12-04,2012-12-14,2012-12-14,"Occupied Hours(0)",0,"Approved Hours(112)",8,"Pending Hours(0)",0,"Pending and Approved Hours(112)",8,
Means to say this as single lines from ESP Client(1st column) till 8(last columns in a single columns)
Last edited by parthmittal2007; 01-02-2013 at 12:11 AM.
There are two macro codes. Which code errors and which code works but has the wrong format?
I don't understand what the output format should look like (post #5)?
Do you want all the consolidated text in one column and not separated into columns for each comma field?
I have manipulated my csv sheet. Can you please do me one more favour.
Does the macro can perform an action that is
In Sheet2 before consolidating the data from different csv files, if prior any data is present in sheet 2 it should delete all of that..
Thanks once again for your wonderful post
Please Login or Register to view this content.
Last edited by AlphaFrog; 01-02-2013 at 12:45 AM.
Can the location be fixed, means to say it should not ask the location, It should take location of csv's mentioned in the code.
And one more question Does this macro will run , when we open the excel sheet.??, If not then how we can do that..
You're welcome.
Change the fixed path to suit.
Please Login or Register to view this content.
Put this in the ThisWorkbook code module.Originally Posted by parthmittal2007
Please Login or Register to view this content.
When opening a work book, The macro does not run automatically, The data does not change.When I run the macro manually then only data changes
It gives error
Compile Error:
Sub or function not defined
Last edited by parthmittal2007; 01-02-2013 at 01:54 AM.
Try this. Put the two macros below in the same code module. Delete the previous Private Sub Workbook_Open macro.
Please Login or Register to view this content.
It is not giving error, But it also not refreshing, still I have to do it manually.
Thanks for your fantastic logic once again.
It works for me. Not sure what to tell you.
You cannot change the name of the Auto_Open macro: Private Sub Auto_Open
The only thing it does is call the other macro when you open the workbook.
Are both macros located in Module1?
its a superb code!
friend can you help me as well?
i want to open every file from selection to be opened in a new sheet.
for example:
if i open 10 files then ten sheets should be opened on my workbook.
Please Login or Register to view this content.
DONE Its completed
Last edited by parthmittal2007; 01-02-2013 at 09:31 AM.
Please Login or Register to view this content.
Thanks you so muchits working. But i my facing two hurdles in it.
1.when i am running this code its open only 1 sheet with below error.
Run time Error 91
Object variable or with block not set.
on next attempt it opens all selected.
2. its changing the dateformat of my data from dd-mm-yyyy to mm-dd-yyyy.
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks