EXCEL VBA question: Import TEXT file, convert Text to Column

[Michel]

G'day,

I'm trying:
1- Import a TEXT file
2- Convert the first column "Text -> Column
3- Save file as EXCEL.

Below is the script that that currently have.

I having problems with the VBA call...
I KNOW I got something wrong with the VBA call to EXCEL but can't put my finger on it.

Help would be appreciated-!

Michel
ottawa

Code: Select all

//

VBSTART

Dim xlApp
Dim xlBook


Sub OpenXL(file)
  Set xlApp = CreateObject("Excel.Application")
  'uncomment next line to make Excel visible
  xlApp.visible = true
  Set xlBook = xlApp.Workbooks.Open(file)
  xlBook.ActiveSheet.Range("A:A").Select ' Select Col A
  xlApp.Selection.TextToColumns
  xlApp.Selection.Destination = Range("A1")
  xlApp.Selection.DataType = 1
  xlApp.Selection.TextQualifier = 1
  xlApp.Selection.ConsecutiveDelimiter = False
  xlApp.Selection.Tab = True
  xlApp.Selection.Semicolon = False
  xlApp.Selection.Comma = False
  xlApp.Selection.Space = False
  xlApp.Selection.Other = True
  xlApp.Selection.OtherChar = "|"
  xlApp.Selection.FieldInfo = Array(1, 1)
  'xlApp.Selection.DecimalSeparator = "Period"
  'xlApp.Selection.ThousandsSeparator = "Comma"
  xlApp.Selection.TrailingMinusNumbers = True

 
End Sub


Sub SaveXL(file)
  xlBook.SaveAs file
End Sub


Sub CloseXL
    xlApp.quit
End Sub


VBEND



// Open, save, close the EXCEL.
VBRun>OpenXL,%PathFileName%.txt
VBRun>SaveXL,%PathFileName%.xls
VBRun>CloseXL

[Michel]

SOLUTION below:

Thanx to MJTNet support for solution

Code: Select all

//
// This script exists to convert TEXT files located in the "Curent" folder to EXCEL
// This cript focuses on SAP reports which are TEXTs and the deliminator is "|"
//



VBSTART

Dim xlApp
Dim xlBook


Sub OpenXL(file)
  Set xlApp = CreateObject("Excel.Application")
  'uncomment next line to make Excel visible
  'xlApp.visible = true
  Set xlBook = xlApp.Workbooks.Open(file)
  xlBook.ActiveSheet.Range("A:A").Select ' Select Col A
  xlBook.ActiveSheet.Range("A:A").TextToColumns xlBook.ActiveSheet.Range("A1"),1,1,False,False,False,False,False,True,"|",Array(1,1),".",",", True

End Sub


Sub SaveXL(file)
    xlBook.SaveAs file, FileFormat = xlExcel8
End Sub


Sub CloseXL
    xlBook.Close false
    xlApp.quit
End Sub


VBEND




// Open TEXT file via EXCEL appliaction
VBRun>OpenXL,%PathFileName%.txt


//
// Before saving check to see if EXCEL version exists. If file exists delete first
//
IfFileExists>%PathFileName%.xls
DeleteFile>%PathFileName%.xls
ELSE
endif

//
// Save TEXT file as EXCEL
//
VBRun>SaveXL,%PathFileName%.xls

//
// Close TEXT file without saving.
//
VBRun>CloseXL



Label>end