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
Bookmarks