Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Import Data date changes

    I have a macro which al.low me to select a file to open and import (copy) the data. For some unknown reason the dates are not imported in the correct format from the source data (dd/mm/yyyy)

    The source data is in sales ledger outstanding transactions.csv and the dates are in Col T


    When importing the dates the format for some unknown reson changes from dd/mm/yyy to mm/dd/yyyy in the workbook Warranty.xlsm

    I have manually copies the dates from ales ledger outstanding transactions.csv and pasted these in Col S in workbook Warranty and recalculated the days outstanding


    I would be appreciated if someone would kindly amend my Macro "Open workbook" so that the dates imported are 100% correct , otherwise my days outstanding are also incorrect
    Attached Files Attached Files
    Last edited by HowardC; 2015-07-13 at 10:48.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What's the macro?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the reply

    I have a workbook called "Auto_Update which runs several macros. I thinks that bthe problem is in the macro Open_Workbook, which first deletes the sheet "Imported Data" and then copies the data


    It would be appreciated if you would pleasxe look at my macro and amend

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Change this:
    Code:
    Set nb = Workbooks.Open(A)
    to this:
    Code:
    Set nb = Workbooks.Open(Filename:=A, local:=True)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks very much Rory for the help. Code works perfectly

    I guess "local:=True" keep the source data intact

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    VBA works with US locales whenever it can (i.e. if you don't tell it otherwise). Using Local:=True tells it to respect the regional settings.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Rory

    Thanks for the explanation. I now fully understand the logic pertaining to this

Posting Permissions

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