convert excel to tab delimiter text

[e***@gmail.com]

I am trying to run this statement but not to know how to fix it :

oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20


Here is the full script :
-------------------------

Dim oBook
Dim Sheet
Dim FileName

Set oBook = WScript.CreateObject("Excel.Application")


'oBook.Workbooks.Open("D:\shared\programs\vbscript\test.xls")
oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
FileName=(Wscript.Arguments.Item(1))
Folder=(Wscript.Arguments.Item(0))

MsgBox FileName
MsgBox Folder


oBook.Visible = false
oBook.DisplayAlerts = false

For Each Sheet In oBook.Worksheets


oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20

MsgBox FileName+Sheet.Name


Next

oBook.Workbooks.Close
oBook.DisplayAlerts = True
oBook.Quit

[Michael Bednarek]

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20

[snip]

1) I don't think the method ".SaveAs" can be applied to sheets, only to workbooks.

2) I don't understand what the ", 20" is supposed to do, but have you tried ", FileFormat:=xlText" ?

[e***@gmail.com]

The script works fine with sheets except that I save to fixed folder instead of variable folder like this :
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs "D:\shared\programs\vbscript\output\" &FileName &"_" &Sheet.Name &".txt", 20

I need to substitute that fixed folder : "D:\shared\programs\vbscript\output\"
with variable folder
& Folder

HOW ???????

Post by Michael Bednarek

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20

[snip]
1) I don't think the method ".SaveAs" can be applied to sheets, only to workbooks.
2) I don't understand what the ", 20" is supposed to do, but have you tried ", FileFormat:=xlText" ?
--
Michael Bednarek, Brisbane "ONWARD"

[GS]

I use this...


Function GetDirectory$(Optional OpenAt, Optional Title$)
' Returns the path of a user selected folder
' Note: By default, dialog opens at 'Desktop'
' Args:
' OpenAt Optional: Path to the dialog's top level folder
' Title Optional: The dialog's title

If Title = "" Then Title = "Please choose a folder"
On Error Resume Next '//if user cancels
'Display a NewStyleDialog (&H40) with an EditBox (&H10)
GetDirectory = CreateObject("Shell.Application").BrowseForFolder(0,
Title, &H40 Or &H10, OpenAt).Self.Path
End Function 'GetDirectory()

..to let users choose the folder!

[Michael Bednarek]

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs "D:\shared\programs\vbscript\output\" &FileName &"_" &Sheet.Name &".txt", 20
I need to substitute that fixed folder : "D:\shared\programs\vbscript\output\"
with variable folder
& Folder
HOW ???????

Have you tried
strMyFolder="E:\some\other\folder\"
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs strMyFolder & FileName & "_" & Sheet.Name & ".txt", 20

If you need user input to determine the folder, there are a number
of directory pickers out there. My favourite is, except in Outlook,
With Application.FileDialog(msoFileDialogFolderPicker)
.[customize]
.Show
.[get user selection]
End With

[snip]

[e***@gmail.com]

I compared the output of 2 text files . One is generated from the inside Excel application save as tab delimited text file and the other is generated from the below script .
There was a difference between the two text files . Any help ?
Why that I can not use the format -4158 with the below script :

Dim oBook
Dim Sheet
Dim Folder
Dim FileName
Const xlCurrentPlatformText = -4158

Set objArgs = WScript.Arguments

FullName = objArgs(I)
'FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )

Set oBook = WScript.CreateObject("Excel.application")
oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))

FileName=(Wscript.Arguments.Item(1))
Folder=(Wscript.Arguments.Item(0))
MsgBox FileName

oBook.application.visible=false
oBook.application.displayalerts=false

For Each Sheet In oBook.Worksheets
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Folder &"\" &FileName &"_" &Sheet.Name &".txt", xlCurrentPlatformText
'-4158
MsgBox FileName+Sheet.Name
Next
MsgBox "Finishing creations of "+FileName+".txt"

oBook.Application.Quit
oBook.Quit
Set oBook = Nothing
set oBookOpen = Nothing

[GS]

Post by Michael Bednarek

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder
&"\" &FileName &"_" &Sheet.Name &".txt", 20

[snip]
1) I don't think the method ".SaveAs" can be applied to sheets, only to workbooks.

The 'Worksheet' object has a 'SaveAs' method!

Post by Michael Bednarek
2) I don't understand what the ", 20" is supposed to do, but have you
tried ", FileFormat:=xlText" ?

The ", 20" is the value being passed for the 'FileFormat' arg. It's
enum is 'xlTextWindows', but the SaveAs method does not support
specifying a delimiter. The simple way I do this is to dump UsedRange
into an array, Join() each row with vbTab (or whatever delimiter I
choose) while loading the result into a 1D array, then write
Join(array, vbCrLf) to a text file.

[GS]

Post by GS
The simple way I do this is to dump UsedRange
into an array, Join() each row with vbTab (or whatever delimiter I
choose) while loading the result into a 1D array, then write
Join(array, vbCrLf) to a text file.

Here's how...

Option Explicit

Sub WksToTabFile(Optional Wks As Worksheet)
' Loads worksheet data into a tab-delimited text file
' Requires WriteTextFile() to create the file

Dim vData, n&, sFile$

sFile = Application.GetSaveAsFilename
If sFile = "" Then Exit Sub

If Wks Is Nothing Then Set Wks = ActiveSheet
vData = Wks.UsedRange
Dim vTmp(1 To UBound(vData))

'Load data from 2D array to 1D array
For n = LBound(vData) To UBound(vData)
vTmp(n) = Join(Application.Index(vData, n, 0), vbTab)
Next 'n

'Write data to text file
WriteTextFile Join(vTmp, vbCrLf), sFile
End Sub

Sub WriteTextFile(TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Sub 'WriteTextFile()

[GS]

Note that my sample code is cut/paste from a VBA project and so needs
to be converted to VBScript. Though, I do have a VBS FSO version of
WriteTextFile()...

Sub FSO_WriteTextFile(TextOut, FileOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile, TxtOut

'On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenForA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenForA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenForW)
oFile.Write (TextOut)
End If

'ErrHandler:
oFile.Close
Set oFSO = Nothing
Set oFile = Nothing
End Sub

..which can be found with its companion 'read' function under the topic
"VBScript Array" posted last month in this NG.

To convert the WksToTabFile routine requires removing the datatype
characters from variables...

As Worksheet
as well as the following symbols
$, &

..so how the routine should otherwise code for VBS needs someone better
skilled than I in this language. I'm hoping you can incorporate the
gist of it into your own routine with some help!<g>

[e***@gmail.com]

I tried the value of 20 and 21 but with difference from the output of inside Excel application save as .

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20
-------------------------
Dim oBook
Dim Sheet
Dim FileName
Set oBook = WScript.CreateObject("Excel.Application")
'oBook.Workbooks.Open("D:\shared\programs\vbscript\test.xls")
oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
FileName=(Wscript.Arguments.Item(1))
Folder=(Wscript.Arguments.Item(0))
MsgBox FileName
MsgBox Folder
oBook.Visible = false
oBook.DisplayAlerts = false
For Each Sheet In oBook.Worksheets
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20
MsgBox FileName+Sheet.Name
Next
oBook.Workbooks.Close
oBook.DisplayAlerts = True
oBook.Quit

[e***@gmail.com]

Dear Mr GS,
Kindly Please send me full code to my email :
***@gmail.com
because I am new to vbscript .
Thanks
Ehab

Post by e***@gmail.com
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20
-------------------------
Dim oBook
Dim Sheet
Dim FileName
Set oBook = WScript.CreateObject("Excel.Application")
'oBook.Workbooks.Open("D:\shared\programs\vbscript\test.xls")
oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
FileName=(Wscript.Arguments.Item(1))
Folder=(Wscript.Arguments.Item(0))
MsgBox FileName
MsgBox Folder
oBook.Visible = false
oBook.DisplayAlerts = false
For Each Sheet In oBook.Worksheets
oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Chr(34) &Folder &"\" &FileName &"_" &Sheet.Name &".txt", 20
MsgBox FileName+Sheet.Name
Next
oBook.Workbooks.Close
oBook.DisplayAlerts = True
oBook.Quit

Mr

[GS]

Post by e***@gmail.com
Dear Mr GS,
because I am new to vbscript .
Thanks
Ehab

I'm relatively new to VBS myself, my forte being VB6/VBA. The code
example "WksToTabFile" is cut/paste from an existing VBA project and so
I would have to research how to do similar in VBS.

Not be disrespectful, if you have interest in learning VBS then better
you "invest" the time/energy to that end. Since I have Lou Gehrig's, my
time/energy would be "spent" not invested! That said, here's how I'd
approach this scenario:

1. Prompt the user for the source filename;
(Abort if filename not provided)
2. If source filename is provided, prompt user for the target path;
(Abort if target path not provided)

3. If you get here, create an instance of Excel;
3.1
Open the file and dump the source sheet's UsedRange into an array
(Reading how VBS handles arrays, this will be zero based)
(Knowing how Excel does this, the array should be 2D)

3.2
Join each row of the array into a temp array's corresponding
element using the 'Tab' character as the delimiter.

4. Write the temp array to file using "FSO_WriteTextFile", and
pass the TextOut arg as shown in my example code.

[GS]

Ok.., I see nobody jumped in to convert my VBA code so I wrote/tested
the following script:

Option Explicit

Const lOpenR = 1 '//for reading
Const lOpenW = 2 '//for writing
Const lOpenA = 8 '//for appending

Dim appXL, oWkb, Wks, sFile, vData
Dim sOut, ndx, fld

sFile = SelectFile
If sFile <> "" Then
Set appXL = WScript.CreateObject("Excel.Application")
Set oWkb = appXL.Workbooks.Open(sFile)

For Each Wks In oWkb.Worksheets
vData = Wks.UsedRange
ReDim vTmp(UBound(vData))
For ndx = LBound(vData) To UBound(vData)
For fld = LBound(vData, 2) To UBound(vData, 2)
sOut = sOut & "," & vData(ndx,fld)
Next 'fld
vTmp(ndx-1) = Join(Split(Mid(sOut, 2), ","), vbTab)
sOut = ""
Next 'ndx
FSO_WriteTextFile Join(vTmp, vbCrLf), sFile & "_" & Wks.Name &
".txt", lOpenW
Next 'Wks

oWkb.Close False
Set oWkb = Nothing
appXL.Quit
set appXL = Nothing
End If 'sFile <> ""

Function SelectFile( )
' File Browser via HTA
' Author: Rudi Degrande, modifications by Denis St-Pierre and Rob van
der Woude
' Features: Works in Windows Vista and up (Should also work in XP).
' Fairly fast.
' All native code/controls (No 3rd party DLL/ XP DLL).
' Caveats: Cannot define default starting folder.
' Uses last folder used with MSHTA.EXE stored in Binary in
[HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\ComDlg32].
' Dialog title says "Choose file to upload".
' Source:
http://social.technet.microsoft.com/Forums/scriptcenter/en-US/a3b358e8-15&ælig;-4ba3-bca5-ec349df65ef6

Dim objExec, strMSHTA, wshShell

SelectFile = ""

' For use in HTAs as well as "plain" VBScript:
strMSHTA = "mshta.exe ""about:" & "<" & "input type=file id=FILE>" _
& "<" & "script>FILE.click();new
ActiveXObject('Scripting.FileSystemObject')" _
&
".GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);" &
"<" & "/script>"""
' For use in "plain" VBScript only:
' strMSHTA = "mshta.exe ""about:<input type=file id=FILE>" _
' & "<script>FILE.click();new
ActiveXObject('Scripting.FileSystemObject')" _
' &
".GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>"""

Set wshShell = CreateObject( "WScript.Shell" )
Set objExec = wshShell.Exec( strMSHTA )

SelectFile = objExec.StdOut.ReadLine( )

Set objExec = Nothing
Set wshShell = Nothing
End Function

Sub FSO_WriteTextFile(TextOut, FileOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile, TxtOut

Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file contents
Set oFile = oFSO.CreateTextFile(FileOut, lOpenW)
oFile.Write (TextOut)
End If

oFile.Close
Set oFSO = Nothing
Set oFile = Nothing
End Sub

[GS]

You do know that this can be done directly from within Excel, right?...