Export Range of (Part of) an Excel Sheet to CSV with VBScript

Question

Is there a way to export a range of cells from each Excel worksheet (in a workbook with multiple worksheets) to a CSV with VBScript? I don't need to skip blanks or do any re-formatting.

I tried the following. You can copy-paste this into notepad and save it as test.vbs:

    Dim oExcel
    Dim oBook
    Dim oWS
    
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Open("c:\test\test.xlsx")
    
    For Each oWS In oBook.Sheets  
    oWS.Range("A9:B32").copy
    oWS.clear
    oWS.Range("A9:B32").paste
    oWS.Copy  
    oExcel.ActiveWorkbook.SaveAs oBook.Path & "\" & oBook.name & "_" & oWS.Name & ".csv",6
    oExcel.ActiveWorkbook.Close False  
    Next 
    oBook.Close False
    oExcel.Quit
    Set oExcel = Nothing

I tried to copy the range of cells, clear the worksheet then paste into the worksheet again and save that as a CSV. It says "Error: Object doesn't support this property or method: 'oWS.clear'"

Thanks in Advance

Answer 1

Thanks for all the help. This works and gives what I was looking/asking for

For Each oWS In oBook.Sheets  
    Dim temp 
    temp = oWS.Range("A5:BY32").Value
    oWS.UsedRange.Clear
    oWS.Range("A1:BY29").Value = temp
    oWS.Copy  
    oExcel.ActiveWorkbook.SaveAs oBook.Path & "\" & oBook.name & "_" & oWS.Name & ".csv", outputFormat  
    oExcel.ActiveWorkbook.Close False  
Next 

The range is actually up to column "BY".