Results 1 to 2 of 2

How to use autofilter in vbscript

Threaded View

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    4

    Post How to use autofilter in vbscript

    Hello All
    I am stuck with my code. I am trying to auto-filter a worksheet on certain criteria(i.e. "desktop", "notebook", "tablet") - and get the output as you would get using filter in the excel sheet. And trying to delete all the columns in the excel sheet except two of them based on their column header (for example: if the column headers are "serial number" and "product id" I would like to delete all the columns except the two columns which has these column headers). Here is me code, could you please check the code and I appreciate if any one can guide me.

    Option Explicit
    On Error Resume Next 
    Dim oXL
    Dim oFolder
    Dim aFile
    Dim FSO
    Dim xTc
    
    Set oXL = CreateObject("Excel.Application")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    oXL.DefaultFilePath = "C:\Documents and Settings\NarahariSr\My Documents\Removal Reports\"   /*opens the folder with folder name removal report*/
    oXL.DisplayAlerts = False
    msgbox (" done")
    msgbox(oXL.DefaultFilePath)
    if FSO.FolderExists(oXL.DefaultFilePath) then
    Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)
    For each aFile in oFolder.Files
    If Right(LCase(aFile.Name), 4) = ".xls" Then
    oXL.Workbooks.Open(aFile.Name)   /*In the folder opens the file with .xls extension*/
    msgbox ("in loop")
    xTc=extract(oXl) /*calls the function*/
    
    oXL.Visible = True
    End If
    Next
    Set oFolder = Nothing
    end if
    oXL.DisplayAlerts = True
    oxl.workbook.close
    
    function extract(oXl)
    Dim ows
    Dim arCriteria
    Dim pdtname
    Dim i
    Dim xlbook
    Dim xlsheet
    i=0
    set xlbook = oXL.Workbooks.Open(aFile.Name)
    set xlsheet = xlbook.worksheets("sheet1")
    msgbox ("in function")       
    arcriteria = array("desktop","notebook","tablet")
    msgbox ("in function2")
    for each pdtname In arcriteria
    xlsheet.cells.autofilter 14,pdtname /*autofilter the above array*/
    i = i+1
    next
    set Rng = Range("A1:AJ1")
    For Each cell In Rng
    If ows.sht1.cells(1,1).Value = "serial number" Then ows.sht1.cell.EntireColumn.delete
    next
    msgbox ("in function1")
    end function
    The above code is not working!!!!!!!!
    Thankx
    Rashme
    Last edited by Rashme; 10-06-2011 at 01:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1