Macro Open File Stops (9.0.4402 SR-1)

Topic

New Reply

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…?

Reply | Quote

Replies

Can you post the code?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

http://support.microsoft.com/default.aspx?…kb;en-us;555263%5B/url%5D

Reply | Quote

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.

Reply | Quote