VBS implementation saves excel table as TXT text

Time:2022-5-28

I hope to find a VBS code that can save excel tables as TXT. Although saving as is optional, it still needs to be saved as TXT directly by VBS. How should I write the code?

If excel is installed, it will be easier. The following is a common one that can run without office, as follows:
VBScript code:

Copy codeThe codes are as follows:


Set oShell = CreateObject(“Shell.Application”)
Set odir = oshell Browseforfolder (0, “select directory”, 0)
For Each x In oDir.Items
If LCase(Right(x.Path,4)) = “.xls” Then
XLS2TXT x.Path
End If
Next
‘****************************************************************************************
‘Start conversion
‘****************************************************************************************
Sub XLS2TXT(strFileName)
‘if excel is installed, just
‘oExcel.ActiveWorkbook.SaveAs strFileName & “.txt“, -4158
‘the following method is suitable for systems without Office
On Error Resume Next
Dim oConn,oAdox,oRecordSet
Set oConn = CreateObject(“Adodb.Connection”)
Set oAdox = CreateObject(“Adox.Catalog”)
sConn = “Provider = Microsoft.Jet.Oledb.4.0;” & _
“Data Source = ” & strFileName & “;” & _
“Extended Properties = “”Excel 8.0; HDR=No””;”
sSQL = “Select * From “
oConn.Open sConn
if Err Then
Msgbox “error code:” & err Number & vbcrlf & err Description
Err.Clear
else
oAdox.ActiveConnection = oConn
Ssql = ssql & “[” & oadox.tables (0).Name & “]” ‘for simplicity, only the first worksheet will be processed
Set oRecordSet = oConn.Execute(sSQL)
if Err Then
Msgbox “error code:” & err Number & vbcrlf & err Description
Err.Clear
else
Write strFileName & “.txt”,oRecordSet.GetString
end if
end If
oRecordSet.Close
oConn.Close
Set oRecordSet = Nothing
Set oAdox = Nothing
Set oConn = Nothing
End Sub
‘****************************************************************************************
‘write file, overwrite with the same name, create if none
‘****************************************************************************************
Sub Write(strName,str)
Dim oFSO,oFile
Set oFSO = CreateObject(“Scripting.FileSystemObject”)
Set ofile = ofso Opentextfile (strname, 2, true) ‘is created if it does not exist, and forced to overwrite
oFile.Write str
oFile.Close
Set oFile = Nothing
Set oFSO = Nothing
End Sub