Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Macro behaves differently if attached to button (Excel97)

    As part of a larger project I need to open a *.CSV spreadsheet, and as I need to do this in Excel97 I am using
    Workbooks.Open FileName:=strfile 'Open file
    The file contains date-time strings with format "dd/mm/yy hh:mm", and my regional settings are configured to match this.

    If I run the macro manually (eg Alt/F8 and select), the file opens correctly, and dates are interpreted properly.

    However, if I attach this macro to a command button, and invoke it by clicking the button, Excel insists on treating the dates as if they were in the format "mm/dd/yy hh:mm". If this interpretation leads to an invalid date (eg 13/02/02), then the field is entered as a string.

    Can anyone please suggest how to force Excel to obey my settings when I use a command button? Any ideas what is happening here?

    Thanks.
    Geoff

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro behaves differently if attached to button (Excel97)

    I see you've stumbled on the infamous international issues of XL. Indeed when you run a macro, XL starts using the US date system and not the one you have in your regional settings. The only way I know around this is to modify the imported dates after the import, using the DATE function.

    I would expect you would have to process *all* dates, even the ones that did get recognized as a date, since probably they do have day and month mixed up.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro behaves differently if attached to button (Excel97)

    I had a similar problem copying from word and pasting to Excel by code. The way I worked around it was to use sendkeys "ctrl V" to do the pasting. Maybe you can do something similar with the macro?

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro behaves differently if attached to button (Excel97)

    A second thought on this is whether it is possible to get the format of the date in the .CSV file changed to either the number format that Excel uses for dates or to a Long date format that could not be misinterpreted. i.e.

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Macro behaves differently if attached to button (Excel97)

    Thanks for the suggestions. As the file is already pre-processed, the option of changing the month to a text string at that time may be the simplest and most practical.

    However, since I wrote yesterday my spreadsheet will no longer even open - causes both Excel97 and Excel2000 to crash on opening, with an invalid page fault - look as if I need to start again from scratch. But, as they say, that is a different question....

    Geoff

Posting Permissions

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