Saving a Fixed Width file as .csv w/VBScript



    Got this script from the scripting guy, but having problems getting it to save without any interaction.  I want the script to open up a fixed field spreadsheet and save it as a .csv.    Just need a statement to save.  Thanks!


    Const xlFixedWidth = 2

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.Workbooks.OpenText _
        "C:\Documents and Settings\User\Desktop\New Folder\Test1",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(12,1),Array(22,1))



    Monday, January 28, 2008 6:23 PM


  • Here is an example, which will convert all the sheets in the workbook to CSV format.

    If you need to convert only one sheet. For example Sheet1, you can modify accordingly
    Code Snippet

    Sub SAveWB_As_CSV()

    Dim oXL As Excel.Application
    Dim oWB As Workbook
    Dim oSht As Worksheet
    Dim sPath As String
    Dim sWB As String
    Dim sSht As String

    Set oXL = New Excel.Application
    oXL.DisplayAlerts = False
    sPath = "c:\temp\"

    Set oWB = Workbooks.Open("c:\myworkbook.xls")
        For Each oSht In oWB.Sheets

            sWB = Trim(Left(oWB.Name, 10))
            sSht = oSht.Name
            sTxtName = sPath & sWB & "_" & sSht & ".txt"
            oWB.SaveAs sTxtName, xlCSV
        Next oSht
        oWB.Close False

    oXL.DisplayAlerts = True
    Set oWB = Nothing
    Set oXL = Nothing


    End Sub


    Thursday, January 31, 2008 2:24 AM