J
jmn50
I have written a macro that will read a layout file and dynamically create
the value for the fieldinfo parameter.
The macro runs successfully, but leaves the Excel environment
unstable--usually causing Excel to crash afterwards. I have run this in
Office 2007 and 2003 (on XP Professional) on two different laptops. Same
problem.
Does anyone have any idea why Excel crashes after processing this macro?
Here is the macro code:
Option Explicit
Sub ImportFileWithLayout()
Dim LayoutFN, DataFN, ArrayVals() As Variant
Dim FieldNm(), FieldStart() As String, FieldCnt, iField As Integer
'
' Created by Jeremy Newkirk on Nov 2, 2009
'
'
' Get the filename of the layout
LayoutFN = Application.GetOpenFilename("All-files,*.*", _
1, "Select The LAYOUT FILE to Use", , False)
If TypeName(LayoutFN) = "Boolean" Then Exit Sub ' the user didn't select
a file
' Open the layout file
Workbooks.Open LayoutFN
Range("B1").Select
' Make sure the headings are where they're supposed to be.
If ActiveCell.Value <> "Field" Then
MsgBox ("Expected to find the heading ""Field"" in cell B1--ABORTING")
Exit Sub
End If
' Get the number of fields in the layout file
FieldCnt = Selection.End(xlDown).Row - 1
' Make sure the headings are where they're supposed to be.
Range("K1").Select
If ActiveCell.Value <> "Start" Then
MsgBox ("Expected to find the heading ""Start"" in cell K1--ABORTING")
Exit Sub
End If
' Redimension the arrays based on the number of field names
ReDim FieldNm(1 To FieldCnt), FieldStart(1 To FieldCnt), ArrayVals(1 To
FieldCnt)
' Load up the arrays--remember that the headings are on the first row so
you have to add 1 to the iField to get the row
' where you'll find the values.
For iField = 1 To FieldCnt
FieldNm(iField) = Cells(iField + 1, 2).Value
FieldStart(iField) = Cells(iField + 1, 11).Value - 1
' ArrayVals will be used for the FieldInfo, so each one has to be an
array and we want all the fields loaded as text
ArrayVals(iField) = Array(FieldStart(iField), xlTextFormat)
Next iField
' Get the filename for the text file.
DataFN = Application.GetOpenFilename("All-files,*.*", 1, "Select The
DATA FILE to Use", , False)
If TypeName(DataFN) = "Boolean" Then Exit Sub ' the user didn't select a
file
' Now we open the text file. The ArrayVals array has the "starting
column and data type" for each field.
Workbooks.OpenText Filename:=DataFN, origin:=437, startrow:=1,
DataType:=xlFixedWidth, fieldinfo:=ArrayVals
' Now we want to make a new row at the top for the headings and put in
the headings from the layout file.
Rows(1).Insert shift:=xlDown
For iField = 1 To FieldCnt
Cells(1, iField) = FieldNm(iField)
Next iField
' Lastly, we size the columns
Columns.EntireColumn.AutoFit
' Print a message for the user
MsgBox ("The text file has been opened based on the layout file. All
files are still open. Here's all the info:" _
& vbCrLf & vbCrLf & " Layout file: " & LayoutFN _
& vbCrLf & vbCrLf & " Data file: " & DataFN)
End Sub
the value for the fieldinfo parameter.
The macro runs successfully, but leaves the Excel environment
unstable--usually causing Excel to crash afterwards. I have run this in
Office 2007 and 2003 (on XP Professional) on two different laptops. Same
problem.
Does anyone have any idea why Excel crashes after processing this macro?
Here is the macro code:
Option Explicit
Sub ImportFileWithLayout()
Dim LayoutFN, DataFN, ArrayVals() As Variant
Dim FieldNm(), FieldStart() As String, FieldCnt, iField As Integer
'
' Created by Jeremy Newkirk on Nov 2, 2009
'
'
' Get the filename of the layout
LayoutFN = Application.GetOpenFilename("All-files,*.*", _
1, "Select The LAYOUT FILE to Use", , False)
If TypeName(LayoutFN) = "Boolean" Then Exit Sub ' the user didn't select
a file
' Open the layout file
Workbooks.Open LayoutFN
Range("B1").Select
' Make sure the headings are where they're supposed to be.
If ActiveCell.Value <> "Field" Then
MsgBox ("Expected to find the heading ""Field"" in cell B1--ABORTING")
Exit Sub
End If
' Get the number of fields in the layout file
FieldCnt = Selection.End(xlDown).Row - 1
' Make sure the headings are where they're supposed to be.
Range("K1").Select
If ActiveCell.Value <> "Start" Then
MsgBox ("Expected to find the heading ""Start"" in cell K1--ABORTING")
Exit Sub
End If
' Redimension the arrays based on the number of field names
ReDim FieldNm(1 To FieldCnt), FieldStart(1 To FieldCnt), ArrayVals(1 To
FieldCnt)
' Load up the arrays--remember that the headings are on the first row so
you have to add 1 to the iField to get the row
' where you'll find the values.
For iField = 1 To FieldCnt
FieldNm(iField) = Cells(iField + 1, 2).Value
FieldStart(iField) = Cells(iField + 1, 11).Value - 1
' ArrayVals will be used for the FieldInfo, so each one has to be an
array and we want all the fields loaded as text
ArrayVals(iField) = Array(FieldStart(iField), xlTextFormat)
Next iField
' Get the filename for the text file.
DataFN = Application.GetOpenFilename("All-files,*.*", 1, "Select The
DATA FILE to Use", , False)
If TypeName(DataFN) = "Boolean" Then Exit Sub ' the user didn't select a
file
' Now we open the text file. The ArrayVals array has the "starting
column and data type" for each field.
Workbooks.OpenText Filename:=DataFN, origin:=437, startrow:=1,
DataType:=xlFixedWidth, fieldinfo:=ArrayVals
' Now we want to make a new row at the top for the headings and put in
the headings from the layout file.
Rows(1).Insert shift:=xlDown
For iField = 1 To FieldCnt
Cells(1, iField) = FieldNm(iField)
Next iField
' Lastly, we size the columns
Columns.EntireColumn.AutoFit
' Print a message for the user
MsgBox ("The text file has been opened based on the layout file. All
files are still open. Here's all the info:" _
& vbCrLf & vbCrLf & " Layout file: " & LayoutFN _
& vbCrLf & vbCrLf & " Data file: " & DataFN)
End Sub