Problem in Copying data from one workbook to another using VBA
- Thread starter Saraah
- Start date
Marc L
Excel Ninja
Hi ! On my side, date column is in date format within Excel …
@vletm That's works well. Thanks a lot.
But Can you please elaborate the changes you made and how did it affect the code ?
Now I am trying the same code in my another workbook where I want to import the data. I made the changes in the code which think were required but its not working.
Can you please suggest me what other changes which may be required ?
I am attaching the two files- "Report form Dec 15_Test" where I want to import the data in Daily Base tab. And "291215_Daily" from where data has to be copied.
Please note I am attaching xlsb file here of "291215_Daily" but actual file to be imported will be in CSV format.
But Can you please elaborate the changes you made and how did it affect the code ?
Now I am trying the same code in my another workbook where I want to import the data. I made the changes in the code which think were required but its not working.
Can you please suggest me what other changes which may be required ?
I am attaching the two files- "Report form Dec 15_Test" where I want to import the data in Daily Base tab. And "291215_Daily" from where data has to be copied.
Please note I am attaching xlsb file here of "291215_Daily" but actual file to be imported will be in CSV format.
@Saraah
> Prev changes:
If data-files 'date-column' is 'General'-format then 'date'-columns values will change to dates. Those dates format change could/should do then normally open data-file the 1st time. Anyway, dates have to be dates (not only look like dates)!
> Your changes: WOW!
There are actually only ONE have to do modification.
Check line ~55, You took away that "ROB" condition...
Because You 'new' data file is different, date column is now ... 'A'.
Only here You skip column number 2, it have to be 1. { .Cells(i,1) = MaxDate }
... and the last part:
It's not necessary to compare R_max & S_max; You can take that part away.
Only MsgBox("Ready") would be good to left there.
>> Ideas ... Questions?
> Prev changes:
If data-files 'date-column' is 'General'-format then 'date'-columns values will change to dates. Those dates format change could/should do then normally open data-file the 1st time. Anyway, dates have to be dates (not only look like dates)!
> Your changes: WOW!
There are actually only ONE have to do modification.
Check line ~55, You took away that "ROB" condition...
Because You 'new' data file is different, date column is now ... 'A'.
Only here You skip column number 2, it have to be 1. { .Cells(i,1) = MaxDate }
... and the last part:
It's not necessary to compare R_max & S_max; You can take that part away.
Only MsgBox("Ready") would be good to left there.
>> Ideas ... Questions?
@vletm Thanks. It worked after I change it to column 1.
But now I have another problem. The actual data I need to import is like more than 20000 rows. The macro is taking too much time to import it. Approx (7-8 mins) or even more than that. This defeats the whole purpose of importing it rather than manually copying and pasting.
Is there a solution with which the macro will work like a quick ?
But now I have another problem. The actual data I need to import is like more than 20000 rows. The macro is taking too much time to import it. Approx (7-8 mins) or even more than that. This defeats the whole purpose of importing it rather than manually copying and pasting.
Is there a solution with which the macro will work like a quick ?
@Saraah
Yes! It would good to know as much details as possible.
Sometimes 'minor' changes could make 'huge' changes.
I tried to find one possible quick solution for this case.
This should be quicker, all depends how much memory You can use.
If You have enough memory, this works ... or it need a little adjust .. or
this could make quicker too.
Test and compare times ( Your older version and this, with same data. )
Yes! It would good to know as much details as possible.
Sometimes 'minor' changes could make 'huge' changes.
I tried to find one possible quick solution for this case.
This should be quicker, all depends how much memory You can use.
If You have enough memory, this works ... or it need a little adjust .. or
this could make quicker too.
Test and compare times ( Your older version and this, with same data. )
@vletm I tried the file sent by you but the its still taking lot of time to import.
I waited for 5mins but the data was not copied so I had to press ESC to end the process.
Is there any other solution to this ?
I have seen many automated files where data gets copied very quickly even when there are thousands of rows.
I waited for 5mins but the data was not copied so I had to press ESC to end the process.
Is there any other solution to this ?
I have seen many automated files where data gets copied very quickly even when there are thousands of rows.
@Saraah
Oh, oh, oh ... just it was over 5minutes ... now it suddenly is 2-3 minutes.
Of course, You want it in 2-3 secs --->
It's almost time to open that source file.
If there have to do any checking, ANY, that will take time ...
and if something have to copy to another file ... that will take time ...
So, You wrote that You can do it quicker manually,
hmmm ... 20'000 rows to check means ~111rows/sec
You gotta have good eyes.
I'll record Your checking - copying time now (19:15).
Let me know when You're ready!
Oh, oh, oh ... just it was over 5minutes ... now it suddenly is 2-3 minutes.
Of course, You want it in 2-3 secs --->
It's almost time to open that source file.
If there have to do any checking, ANY, that will take time ...
and if something have to copy to another file ... that will take time ...
So, You wrote that You can do it quicker manually,
hmmm ... 20'000 rows to check means ~111rows/sec
You gotta have good eyes.
I'll record Your checking - copying time now (19:15).
Let me know when You're ready!
@vletm sometimes its taking me 3 mins and sometimes more than that. If i do it manually it takes me 2 secs to open the file and 2-3 secs to copy the data. I want to automate this so we do not have to open source file and copy but if importing the file takes longer then nobody is going to accept this. I didnt understand what you mean by recording my copying time ?
@Saraah
Your previous timing was 2-3 secs, now You wrote that You'll use 4-5 secs.
Why You asked to make date checking at all and before that 'ROB'?
Why You don't use the original file at once? Rename it!
'Record timing' ...
Many people just say that they can do something in 1 minute.
But, then someone asks to to do it now, just now!
They say ... no no no or if they start it, it takes 15 minutes or what ever.
You wrote that You could do this quicker than 3 minutes ... but You cannot!
Of course, if You skip some parts of jobs that You asked (like checking dates ~20'000 rows) You could do it faster. Remember that is .cvs-file.
You asked to check dates (+'ROB') and copy - NOT just copy!
So copy that file with out open it! and after that Rename.
That would be the quickest method!
Your previous timing was 2-3 secs, now You wrote that You'll use 4-5 secs.
Why You asked to make date checking at all and before that 'ROB'?
Why You don't use the original file at once? Rename it!
'Record timing' ...
Many people just say that they can do something in 1 minute.
But, then someone asks to to do it now, just now!
They say ... no no no or if they start it, it takes 15 minutes or what ever.
You wrote that You could do this quicker than 3 minutes ... but You cannot!
Of course, if You skip some parts of jobs that You asked (like checking dates ~20'000 rows) You could do it faster. Remember that is .cvs-file.
You asked to check dates (+'ROB') and copy - NOT just copy!
So copy that file with out open it! and after that Rename.
That would be the quickest method!
Last edited:
Marc L
Excel Ninja
OK : just activate Macro Recorder and do it manually :
you will get your own free code base …
you will get your own free code base …
@Marc L
.cvs , I found it somewhere - hmm - and of course it was possible to change dates to real dates. Anyway, if not done, 'the code' will do it!
My the latest version has a loop like 'checks and loads to array (~10'000rows) and after that makes copy' until all rows has done; now it'll do this ~2 times.
Of course, this could done with one time, but sometimes there isn't enough memory. If enough memory then ~10k can set to ~20k or more, no problem.
And, if the first priority of this case was 'max 3 secs',
it would be good to know in the beginning.
.cvs , I found it somewhere - hmm - and of course it was possible to change dates to real dates. Anyway, if not done, 'the code' will do it!
My the latest version has a loop like 'checks and loads to array (~10'000rows) and after that makes copy' until all rows has done; now it'll do this ~2 times.
Of course, this could done with one time, but sometimes there isn't enough memory. If enough memory then ~10k can set to ~20k or more, no problem.
And, if the first priority of this case was 'max 3 secs',
it would be good to know in the beginning.
@vletm The prior file where we checked dates and the ROB condition was for another data file and that is working fine now. Thanks to you.
This is my new data file which I want to automate and I tried to apply the same code to this one as well but here we have a time issue.
We copy all of the data at once manually and it takes like few seconds as I mentioned. That's why if I want to import it directly I want it in few seconds only.
I tried to modify the code by removing the date condition as well now but still its taking 3 mins. Can you please suggest me changes which can help me achieve my target for this file too ?
I am attaching the file now.
This is my new data file which I want to automate and I tried to apply the same code to this one as well but here we have a time issue.
We copy all of the data at once manually and it takes like few seconds as I mentioned. That's why if I want to import it directly I want it in few seconds only.
I tried to modify the code by removing the date condition as well now but still its taking 3 mins. Can you please suggest me changes which can help me achieve my target for this file too ?
I am attaching the file now.
@Saraah
1) I checked You modified file.
You have modified too much, that code cannot work!
It needs to rebuild again and after that it will take over few seconds!
2) Did You read previous Replys? No!
3) Still You say that it takes like few seconds!
4) I could make the Macro which would copy 'everything' in one time,
this could work ONLY if You have enough memory!
but it would take over few seconds!
5) You should continue using Your 'Copy & Paste'-method.
1) I checked You modified file.
You have modified too much, that code cannot work!
It needs to rebuild again and after that it will take over few seconds!
2) Did You read previous Replys? No!
3) Still You say that it takes like few seconds!
4) I could make the Macro which would copy 'everything' in one time,
this could work ONLY if You have enough memory!
but it would take over few seconds!
5) You should continue using Your 'Copy & Paste'-method.
rrocker1405
Member
Hi Sarah,
The most easiest way to have this done is without a VBA and by using Power Query.
Step 1. I converted the data in databook as a table and saved the file in the desktop.
Step 2. In a new spreadsheet, PowerQuery > From File > (navigate to desktop) choose the file "Databook", selected 'Table1'
Step 3. Click "Edit" in the Pop-up Window and filter for "ROB"
Step 4. Click "close and load"
The next time just update the data book with updates and use the master file to get information by using just data > refresh. This should solve the issue.
Download Power Query from Microsoft here:
https://www.microsoft.com/en-gb/download/details.aspx?id=39379
Kind regards,
A!
@rrocker1405 - gotta comment - sorry
Saarah want it in few seconds only, max 2-3 sec!
How many seconds those four steps will take, few or more?
It gotta be very fast, very fast!
Saarah want it in few seconds only, max 2-3 sec!
How many seconds those four steps will take, few or more?
It gotta be very fast, very fast!
Last edited:
rrocker1405
Member
Once the file is created, it will take only that timeframe (worst case scenario less than a minute) as the source is going to be in the local pc of the user.