Convert .lis files to XML Spreadsheets

[navash]

Hi,

I want to open all the ".lis" files in a particular folder in Excel, format them and save them as "XML Spreadsheets" in the same folder with the same File Name.

e.g "FileName.lis" is opened in Excel, formatted and saved in the same directory with new name as "Filename.xml"

.lis files are ANSYS output files similar to .dat files

I have written the following macro but it doesn't seem to work right. Please guide me write this. I also attach a sample .lis file just in case you might need to check.

Code:

Sub Macro2()
'
Dim strFile As String
Dim strPath As String

With Application
.EnableEvents = False
'.DisplayAlerts = False
.ScreenUpdating = False
End With

strPath = "C:\Documents and Settings\Jhintak\Desktop\Macro Test\"
strFile = Dir(strPath & "*.lis")

Do While strFile <> ""
Workbooks.Open (strPath & strFile), Origin:= _
437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(8, 1), Array(21, 1), Array(33, 1), Array(46, 1), Array(55, 1), Array(68, 1)), _
TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:= _
xlXMLSpreadsheet, ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close True
strFile = Dir
Loop
End Sub

 

[johnwm]

Rather than writing a macro from scratch simply record a macro going through the entire process manually. Then open the recorded macro in the VB Editor and modify to suit. Without understanding what you want to do with the data, or what XML schema you want to use, I just recorded this to import the file into a new worksheet and parse out the data. It should get you started.

Sub Macro1()
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PRNSOL.lis", Destination:=Range( _
        "A1"))
        .Name = "PRNSOL"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(10, 11, 12, 13, 12)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting
Steam Engine enthusiasts