TM1 Forum

Code: Select all

Option Explicit

'variables
Dim objXl, objWb

'automate excel
Set objXl = CreateObject("Excel.Application")

'open and save as text
Set objWb = objXl.Workbooks.Open("D:\DVD List.xlsx")
objWb.SaveAs "D:\DVD List.txt", -4158

'close wb
objWb.Close (False)

'quit excel
objXl.Quit

'clean up
Set objWb = Nothing
Set objXl = Nothing

Code: Select all

With CreateObject("Excel.Application")
  With .Workbooks.Open("D:\file.xlsx")
      .SaveAs "D:\file.txt", -4158
      .Close 0
  End With
  .Quit
End With

Code: Select all

Option Explicit

'constant for text file
Const xlCurrentPlatformText = -4158

'variables
Dim strInputExcelFile, strOutputTextFile

'retrieve and validate inputs
With WScript
  If .Arguments.Count <> 2 Then
    .Echo "Missing arguments"
    .Quit		
  Else
    strInputExcelFile = .Arguments(0)
    strOutputTextFile = .Arguments(1)
  End If
End With

'perform save as tab delimited text
With CreateObject("Excel.Application")
  With .Workbooks.Open(strInputExcelFile)
    .SaveAs strOutputTextFile, xlCurrentPlatformText
    .Close 0
  End With
  .Quit
End With

Code: Select all

sCommand = 'cscript.exe "c:\yourscripts\convert.vbs" "c:\yourexcel\sheet.xlsx" "c:\yourtext\converted.txt"';
ExecuteCommand ( sCommand, 1 );

Code: Select all

  With .Workbooks.Open(strInputExcelFile)
    .Worksheets(1).UsedRange.NumberFormat = "General"
    .SaveAs strOutputTextFile, xlCurrentPlatformText
    .Close 0
  End With

Code: Select all

""
Option Explicit
Const xlCurrentPlatformText = -4158
Dim strInputExcelFile, strOutputTextFile
With WScript
  If .Arguments.Count <> 2 Then
    .Echo "Missing_parameters"
    .Quit
  Else
    strInputExcelFile = .Arguments(0)
    strOutputTextFile = .Arguments(1)
  End If
End With
With CreateObject("Excel.Application")
  With .Workbooks.Open(strInputExcelFile)
    .Worksheets(1).UsedRange.NumberFormat = "General"
    .SaveAs strOutputTextFile, xlCurrentPlatformText
    .Close 0
  End With
  .Quit
End With
""

Code: Select all

sScript = '
"
' | Char(10) | '
Option Explicit
' | Char(10) | '
Const xlCurrentPlatformText = -4158
' | Char(10) | '
Dim strInputExcelFile, strOutputTextFile
' | Char(10) | '
With WScript
' | Char(10) | '
  If .Arguments.Count <> 2 Then
' | Char(10) | '
    .Echo "Missing_parameters"
' | Char(10) | '
    .Quit
' | Char(10) | '
  Else
' | Char(10) | '
    strInputExcelFile = .Arguments(0)
' | Char(10) | '
    strOutputTextFile = .Arguments(1)
' | Char(10) | '
  End If
' | Char(10) | '
End With
' | Char(10) | '
With CreateObject("Excel.Application")
' | Char(10) | '
  With .Workbooks.Open(strInputExcelFile)
' | Char(10) | '
    .Worksheets(1).UsedRange.NumberFormat = "General"
' | Char(10) | '
    .SaveAs strOutputTextFile, xlCurrentPlatformText
' | Char(10) | '
    .Close 0
' | Char(10) | '
  End With
' | Char(10) | '
  .Quit
' | Char(10) | '
End With
' | Char(10) | '
"
';

ASCIIOutput(csScriptPath, sScript);

Code: Select all

#===============================================================================================
# Define lines
#===============================================================================================

sLine1  = 'This is the first line of your output';
sLine2  = '';
sLine3  = 'This is the third line';
sLine4  = '';
sLine5  = 'and so on !';
sLine6  = '';
sLine7  = '';
sLine8  = '';
sLine9  = '';
sLine10 = '';
sLine11 = '';
sLine12 = '';
sLine13 = '';
sLine14 = '';
sLine15 = '';
sLine16 = '';
sLine17 = '';
sLine18 = '';
sLine19 = '';
sLine20 = '';

#===============================================================================================
# Use a loop to generate the AsciiOutput (Change nMax if needed)
#===============================================================================================

k = 1;
nMax = 20;
sStaticString = 'sLine';
While( k <= nMax );
	sCurrentLine = Expand( '%' | sStaticString | NumberToString( k ) |  '%' );
	AsciiOutput( sFileName, sCurrentLine );
	k = k + 1;
END;

Code: Select all

DatasourceASCIIQuoteCharacter = '';

Code: Select all

#*** Prolog: Start

#<...>
#<description etc.>
#<...>


### Constants
#----------------------------------------------------------------------------------------------#
csFolder = <...YOUR_FOLDER...>;

csScriptAppPath = 'C:\Windows\System32\cscript.exe';
csScriptName = 'ConvertExcelToTXT.vbs';
csScriptPath = csFolder | csScriptName;



### Config ASCIIOutput (for removing quotes from ascii-file with script)
#----------------------------------------------------------------------------------------------#
DatasourceASCIIQuoteCharacter = '';



### Create VB-Script (*.vbs)
#----------------------------------------------------------------------------------------------#
# Define lines
sLine1  = 'Option Explicit';
sLine2  = 'Const xlCurrentPlatformText = -4158';
sLine3  = 'Dim strInputExcelFile, strOutputTextFile';
sLine4  = 'With WScript';
sLine5  = '  If .Arguments.Count <> 2 Then';
sLine6  = '    .Echo "Missing_parameters"';
sLine7  = '    .Quit';
sLine8  = '  Else';
sLine9 = '    strInputExcelFile = .Arguments(0)';
sLine10 = '    strOutputTextFile = .Arguments(1)';
sLine11 = '  End If';
sLine12 = 'End With';
sLine13 = 'With CreateObject("Excel.Application")';
sLine14 = '  With .Workbooks.Open(strInputExcelFile)';
sLine15 = '    .Worksheets(1).UsedRange.NumberFormat = "General"';
sLine16 = '    .SaveAs strOutputTextFile, xlCurrentPlatformText';
sLine17 = '    .Close 0';
sLine18 = '  End With';
sLine19 = '  .Quit';
sLine20 = 'End With';

# Generate the ASCIIOutput (check "nMaxLines")
i = 1;
nMaxLines = 20;
sLineCaption = 'sLine';
While( i <= nMaxLines );
	sCurrentLine = Expand( '%' | sLineCaption | NumberToString( i ) |  '%' );
	ASCIIOutput(csScriptPath, sCurrentLine);
	i = i + 1;
END;


#*** Prolog: End

Code: Select all

#*** Epilog: Start

sFileName = <...YOUR_FILE...>;
sFileFormat = '.xlsx';
sFullFileName = sFileName | sFileFormat;

sFilePath = csFolder | sFullFileName;
sExportFilePostfix = '_Exported';
sExportFileName = sFileName | sExportFilePostfix;
sExportFileFormat = '.txt';
sExportFullFileName = sExportFileName | sExportFileFormat;
sExportFilePath = csFolder | sExportFullFileName;

sCommand = csScriptAppPath | ' "'| csScriptPath | '" "'| sFilePath |'" "' | sExportFilePath |'"';
ExecuteCommand(sCommand, 1);

ASCIIDelete(csScriptPath);

#*** Epilog: End