Sorry, forgot to change the csv location back!
Sorry, forgot to change the csv location back!
Semper in excretia sumus; solum profundum variat.
toWorkbooks.Open "C:\temp\prcbook.csv" '("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")
Workbooks.Open ("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")
Semper in excretia sumus; solum profundum variat.
With button:
Semper in excretia sumus; solum profundum variat.
The problem with a button is that this is an automated process on Sunday early morning. No humans are available.
Did the DoEvents do it? If not, try uncommenting the line in the Workbook_Open code and change the time in that to 00:00:10, if there is no one about then an extra 10 seconds shouldn't matter
Semper in excretia sumus; solum profundum variat.
Ok, just ran the DoEvents and it worked fine while observed. The real test will be this Sunday during normal operation.
So assuming that this current code is good I also have 2 other PriceBooks I'm trying to work with.
So the next question would be how to add an additional 15% onto the currency field, before or after it's been converted.
ar(i, 7) = (ar(i, 7) / 1000) * 1.15 Else ar(i, 7) = (ar(i, 7) / 100) * 1.15
Semper in excretia sumus; solum profundum variat.
Beautiful! Absolutely Beautiful!
As of now, all tests have worked flawlessly.
I have created different macro files for the different price books and this Sunday it will run per my auto scheduler and email out.
Just in case I do start having issues where do I put the
If not, try uncommenting the line in the Workbook_Open code and change the time in that to 00:00:10
Thank you very much for the assistance
In the ThisWorkbook code module:
67262.jpg
Private Sub Workbook_Open() Application.Wait (Now + TimeValue("00:00:10")) PRCBOOK_Open End Sub
Semper in excretia sumus; solum profundum variat.
Happy to help, hope it all goes okay for you
Semper in excretia sumus; solum profundum variat.
So far absolutely fantastic.
Just about done, working on the last part of the pricebook now.
How would I have it open another workbook called "BlankQuote" and copy sheet1 into the first workbook as the first sheet and then close the second workbook?
Sub CopySheet() Application.ScreenUpdating = False Workbooks.Open "C:\temp\blankquote.xlsx" Sheets("Sheet1").Copy Before:=ThisWorkbook.Sheets("Sheet1") Sheets("Sheet1 (2)").Name = "New Sheet" Workbooks("blankquote.xlsx").Close 0 End Sub
Semper in excretia sumus; solum profundum variat.
Ok, here is everything.
What seems to be happening is that it's taking the .xlsm as the active and pasting stuff to it?
QuoteSheet.xlsm
BlankQuote.xlsx
PRCBOOK.csv
I took it you wanted to put the blank quote sheet into the macro workbook. Do you want it to go into the csv? If so, it will have to be saved as a workbook first and then the sheet added (only one 'sheet' in a csv!)
Is that what is supposed to happen?
Semper in excretia sumus; solum profundum variat.
My apologies, yes, basically I'm attempting to output this as single document so that I can email it to Sales people to use daily.
Then we can move the save line to after the formatting then add the copy and paste. (Tried it, but still didn't work)
1 minute!
Semper in excretia sumus; solum profundum variat.
Please take your time! You have become my new best friend!
Try this.
I noticed the 15% wasn't in, just in case you've missed it out
Semper in excretia sumus; solum profundum variat.
So far perfect! This one doesn't use the 15% that was for the 2nd workbook. But great looking out!
I'm going to run this and double check all sheets tomorrow but the preliminaries look good!
Excellent! Let me know how it goes, stay safe!
Semper in excretia sumus; solum profundum variat.