Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Open File Stops (9.0.4402 SR-1)

    I have an Excel macro (VBA) in workbook 'A'. It opens a text file into another Excel workbook ('B') so that it can copy the data into a worksheet in workbook 'A'. The macro uses Workbooks.OpenText, which brings the textfile through the wizard. The routine works fine when I STEP (F8) through it; but when I RUN it, it just stops after opening the text file.
    Help...?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    Can you post the code?

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    Sub GetNewData()
    'Import data from 'CompLists.txt' into new Excel file
    Workbooks.OpenText Filename:="C:TempCompList.txt", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
    Array(10, 1), Array(11, 1))

    MsgBox "CompList.txt opened", , "GetNewData"

    This MsgBox doesn't display when the macro is run, but it does if the macro is stepped through.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    See what happens if you add an error handler:

    Sub GetNewData()
    On Error GoTo ErrHandler

    'Import data from 'CompLists.txt' into new Excel file
    Workbooks.OpenText Filename:="C:TempCompList.txt", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
    Array(10, 1), Array(11, 1))

    MsgBox "CompList.txt opened", , "GetNewData"
    ... ' other code goes here

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    I used an error handler, and absolutely no error occurs. Everything just stops with the newly opened workbook containing the text file data.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    When I run the code with a text file that exists on my PC, the message box does get displayed. Could you attach a zip file with stripped down copies of the workbook and of the text file? Remove everything that is not relevant to this problem, and replace sensitive data with dummy data.

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

    Re: Macro Open File Stops (9.0.4402 SR-1)

    This wouldn't have something to do with this KB article?

    http://support.microsoft.com/default.aspx?...kb;en-us;555263
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Open File Stops (9.0.4402 SR-1)

    The symptoms described in this KB article are almost exactly what I experience, except there is no shift-key being pressed.
    I'm going to try-out the code anyway (detecting shift-key), and see what happens.

Posting Permissions

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