Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 69

Thread: How to set currency with different starting values

  1. #21
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Sorry, forgot to change the csv location back!
    Semper in excretia sumus; solum profundum variat.

  2. #22
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
        Workbooks.Open "C:\temp\prcbook.csv" '("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")
    to
        Workbooks.Open ("C:\Users\Administrator\Desktop\W\Reports\PRCBOOK.CSV")
    Semper in excretia sumus; solum profundum variat.

  3. #23
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    With button:
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  4. #24
    The problem with a button is that this is an automated process on Sunday early morning. No humans are available.

  5. #25
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location

    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.

  6. #26
    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.

  7. #27
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
                    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.

  8. #28
    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

  9. #29
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    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.

  10. #30
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Happy to help, hope it all goes okay for you
    Semper in excretia sumus; solum profundum variat.

  11. #31
    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?

  12. #32
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    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.

  13. #33
    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

  14. #34
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    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.

  15. #35
    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)

  16. #36
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    1 minute!
    Semper in excretia sumus; solum profundum variat.

  17. #37
    Please take your time! You have become my new best friend!

  18. #38
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Try this.

    I noticed the 15% wasn't in, just in case you've missed it out
    Attached Files Attached Files
    Semper in excretia sumus; solum profundum variat.

  19. #39
    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!

  20. #40
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,017
    Location
    Excellent! Let me know how it goes, stay safe!
    Semper in excretia sumus; solum profundum variat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •