Convert .lis files to XML Spreadsheets
Convert .lis files to XML Spreadsheets
(OP)
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
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





RE: Convert .lis files to XML Spreadsheets
CODE
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: Eng-Tips.com Forum Policies before posting
Steam Engine enthusiasts