Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help on macro that reads a layout and opens a fixedwidth text file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File opens another file runs macro and closes | Excel Programming | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) | |||
how to get a macro to run when the file opens | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
I'm looking for a macro that opens directly to the Pivot Table layout | Excel Programming |