Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    CSV's don't import properly if a macro has been run

    Anyone seen this?

    Excel 2010. I have some extensive macros. I open a .xlsm file and run a macro. Then,. I double click on a .csv file to import it into a new instance of Excel. The data all appears in the first column rather than being split into columns as a .csv file should.

    If I close Excel off completely and then double click on the .csv file, it works fine. If I don't run a macro but have Excel open anyway, it also works fine. If I start a new copy of Excel and drag the CSV file into it, it works fine.

    Anyone know what is going on?

    cheers

    Alan

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Alan

    If I open a .xlsm file in Excel2010, run a macro, then double-click on a .csv file, the csv file opens as expected.
    To be accurate, the csv file is NOT opened in a new instance of Excel. It's opened in a new Excel workbook in the same instance of Excel that the .xlsm file is in.
    An instance of Excel is a separate running Excel session. Two (or more) instances of Excel are independent in Excel2010 (and lower).

    I suspect that something in the particular .xlsm file or macros you are running, are changing the Excel environment.
    To test this, quit Excel, start a new Excel session, create a new .xlsm file with a simple macro in it, e.g. to switch to sheet2.
    Then run the macro. Then double-click the csv file and see if you get the same behaviour.
    Report back.

    zeddy

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would guess that the macro changes the text import settings. In your macro you could fake a new text to columns and reset to the default settings or call a routinie like this:
    Code:
    Sub ResetTextToColumns()
      If IsEmpty(Range("A1")) Then Range("A1") = "ZZZZ"
      Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
      If Range("A1") = "ZZZZ" Then Range("A1").ClearContents
    End Sub
    Steve

  4. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Zeddy, I will try that - but probably not for a week or more as I'm just off on my hols.

    cheers

    Alan

    Quote Originally Posted by zeddy View Post
    Hi Alan

    If I open a .xlsm file in Excel2010, run a macro, then double-click on a .csv file, the csv file opens as expected.
    To be accurate, the csv file is NOT opened in a new instance of Excel. It's opened in a new Excel workbook in the same instance of Excel that the .xlsm file is in.
    An instance of Excel is a separate running Excel session. Two (or more) instances of Excel are independent in Excel2010 (and lower).

    I suspect that something in the particular .xlsm file or macros you are running, are changing the Excel environment.
    To test this, quit Excel, start a new Excel session, create a new .xlsm file with a simple macro in it, e.g. to switch to sheet2.
    Then run the macro. Then double-click the csv file and see if you get the same behaviour.
    Report back.

    zeddy

Posting Permissions

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