I have this code which a report is generated and it is not a constant name except the beginning of the file name which is "_ttv-".The problem is that it does not close the other Workbook - can someone please help me!?
Please Login or Register to view this content.
1.Instead of "Dim", use:Originally Posted by Jamesraywebber85
... is not a constant name except the beginning of the file name which is "_ttv-".
Please Login or Register to view this content.
2. If "_ttv-" is the beginning of the file, then what is "MyWorkbook2015" ?Please Login or Register to view this content.
3.Where did "Wb2" come from, and what is it?Please Login or Register to view this content.
4.All cells ? 1,048,576 rows x 16,384 columns = 17,179,869,184 cells ?Please Login or Register to view this content.
5. Instead of:
use:Please Login or Register to view this content.
6.Please Login or Register to view this content.7.Please Login or Register to view this content.Where did "Wb2" come from, and what is it ?Please Login or Register to view this content.
So I have edited my code, but it does not copy the data over like it supposed to - can you take a look at this and let me know what I might be doing wrong?
Please Login or Register to view this content.
With such code you are using any file ...!Please Login or Register to view this content.
Paste is done in Worksheets("IEX Data - CT Set 20") does exist in the active workbook or in which workbook ??Please Login or Register to view this content.
Could you explain step by step what you want to do ?
- Battle without fear gives no glory - Just try
With such code you are using any file ...!Please Login or Register to view this content.
Is it
you need ??Please Login or Register to view this content.
Paste is done in Worksheets("IEX Data - CT Set 20") does exist in the active workbook or in which workbook ??Please Login or Register to view this content.
Could you explain step by step what you want to do ?
So I have included my files along with the report that runs each hour with a different file name that begins with "_ttv-". That workbook has sheet("Interaday") which needs to be copied over the "IEX Data - CT Set 20") in the workbook called "SSC Hourly Update Template".
What about
Please Login or Register to view this content.
Last edited by PCI; 05-21-2018 at 12:26 PM. Reason: Code updated
It does not do anything either
Are you using the last code (I made some adjustments)
Last edited by PCI; 05-21-2018 at 12:46 PM.
Yes using the latest code from post #7! The macro is in the SSC Hourly update Template which will copy the data from this "_ttv-" file and paste it into the worksheet ("IEX Data - CT Set 20")
Is it OK now ??
Just to finish, a little cleaning
Please Login or Register to view this content.
It looks good so far - thank you so much for your help!
You are welcome
Enjoy Excel
So after a day of playing around with the code you provided. It pulls the data once and the next hour when it updates, it does not work - any ideas on this?
Try
Code already sent in another thread ...!
Do we agree that original data is cleaned up each time !!!
Please Login or Register to view this content.
Last edited by PCI; 05-22-2018 at 11:18 AM.
Yes it is cleaned up each time.
Oh I see, in your first macro is used only files loaded in Excel then the same is done in the macro sent, is it what you want ???
I'm not sure - the file will be loaded into excel and open when we run the macro.
As you mentioned "It pulls the data once and the next hour when it updates, it does not work"
It does not work if the file is not loaded in Excel else copy/Paste is done and file is closed
Can you detail step by step the issue you see
So here is 2 of the hourly reports & my SSC template that runs the macro, run the macro once and then load the next hourly report and try to run the macro again - you will see that it does not pull the data like I need it to.
Yes it is working but there is a certainly a confusion about the understanding of the job to.
What is the job today:
Clean up sheet "IEX Data - CT Set 20"
For all files loaded in Excel starting with "_ttv-"
Copy and paste data from sheet "Intraday" to sheet "IEX Data - CT Set 20" at cell A1
=> it means the last file 's values will erase the previous one
=> there is no cleaning done
=> it means you will see only data for the last one and a part of the previous files if they were larger
What is the final result you need?
Here another code where will be seen in sheet "IEX Data - CT Set 20"
only data of the last input file sheet "Intraday"
Is it what you want ?
Please Login or Register to view this content.
The report is ran every hour so i want it to copy the data from that report and paste it into the CT Set 20 sheet overwriting all the data there because the report from the previous hour will be on the new report.Here another code where will be seen in sheet "IEX Data - CT Set 20"
only data of the last input file sheet "Intraday"
Is it what you want ?
Please Login or Register to view this content.
This is what the last macro is doing as far as the file is loaded in ExcelPHP Code:"The report is ran every hour so i want it to copy the data from that report and paste it into the CT Set 20 sheet overwriting all the data there because the report from the previous hour will be on the new report."
It means it is up to you to load the file and trigger the macro every hour ...!
If the report runs and excel said there was an error with the data and repairs the information, would it not work?
If Excel repaired it, it means it should reusable by Excel so it will work.PHP Code:If the report runs and excel said there was an error with the data and repairs the information, would it not work?
Now if there is some difficulties, it is possible to manage the error:
Do a display to the user, launch another job ...
I am still having trouble with this one - is there a way that we could import it based on the last updated file at this location? This is the path inwhich the file saves once we run it (C:\Users\Rwebber\WFMData\_ttv-5534267313106029378.xlsx). I was wondering could we have a code where it will look in this location and find the most recent workbook and import the data that way and then tell it to close that workbook?
OK, you want to use the last file recorded in "C:\Users\Rwebber\WFMData\", with beginning like "_ttv-"
It means you miss to review some records: if there is several files only the last one will used ...!
By the way, if the file where is the macro is already recorded in "C:\Users\Rwebber\WFMData\" we could use the path of this file without to mention explicitely the path "C:\Users\Rwebber\WFMData\"...!
See next and comment
Please Login or Register to view this content.
Last edited by PCI; 05-26-2018 at 05:23 AM. Reason: Code added
By the way, if your data files like "_ttv-" have in their name some time reference with could use it.
Let's say that "_ttv-1643454077149903120.xlsx" is old than "_ttv-8659680448621142670.xlsx" and we can see it because threre is a particular inside the name, it will be very simple and clear.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks