none
Saving a Fixed Width file as .csv w/VBScript

    Question

  •  

    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))

     

    objExcel.Quit

    Monday, January 28, 2008 6:23 PM

Answers

  • 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

            oSht.Activate
           
            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
    oXL.Quit
    Set oWB = Nothing
    Set oXL = Nothing

    Next

    End Sub





    Cheers
    Shasur

    Thursday, January 31, 2008 2:24 AM