Unable to get the SpecialCells property of the Range class error

[n3tw0rkadm1n1strat0r]

I am getting this error when running my script: "Unable to get the SpecialCells property of the Range class error"

Here is my code in vbs:

    Set objExcel = CreateObject("Excel.Application")
	objExcel.Application.DisplayAlerts = False
	objExcel.Visible = False

    Set objWorkbook = objExcel.Workbooks.Open("C:\temp.csv")
	objExcel.Range("A1:I1").Select
	objExcel.Selection.Font.Bold = True
	objExcel.Selection.Font.Size = 11
	objExcel.Selection.Interior.ColorIndex = 11 
	objExcel.Selection.Interior.Pattern = 1 'xlSolid 
	objExcel.Selection.Font.ColorIndex = 2
	objExcel.Selection.WrapText = True

	objExcel.Columns("A:I").Select
	objExcel.Selection.HorizontalAlignment = 3 'xlCenter
	objExcel.Selection.Borders.LineStyle = 1 '= xlSolid

	objExcel.Columns("A:A").Delete

	objExcel.Cells(1,1).Value = "Name"
	objExcel.Cells(1,2).Value = "Email"
	objExcel.Cells(1,3).Value = "City"
	objExcel.Cells(1,4).Value = "State"
	objExcel.Cells(1,5).Value = "Job Title"
	objExcel.Cells(1,6).Value = "Phone Number"
	objExcel.Cells(1,7).Value = "Department"
	objExcel.Cells(1,8).Value = "Company"

	objExcel.Columns("A:AH").EntireColumn.AutoFit

	objExcel.Range("B:B").Select
	objExcel.Selection.Specialcells(xlCellTypeBlanks).Entirerow.Delete
	
    objExcel.ActiveWorkbook.SaveAs("C:\List.xls"), 43
    objExcel.Quit

Thanks to anyone who can help.

 

[SkipVought]

Hi,

objExcel is the Excel Appliction Object.

A Range object is not directly related to the application object

A range object is on a Worksheet.

    Dim objWorkbook As Excel.Workbook, objExcel As Excel.Application
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Application.DisplayAlerts = False
    objExcel.Visible = False

    Set objWorkbook = objExcel.Workbooks.Open("C:\temp.csv")
    
    With objWorkbook.Sheets(1)
        .Range("A1:I1").Select
        .Selection.Font.Bold = True
        .Selection.Font.Size = 11
        .Selection.Interior.ColorIndex = 11
        .Selection.Interior.Pattern = 1 'xlSolid
        .Selection.Font.ColorIndex = 2
        .Selection.WrapText = True
    
        .Columns("A:I").Select
        .Selection.HorizontalAlignment = 3 'xlCenter
        .Selection.Borders.LineStyle = 1 '= xlSolid
    
        .Columns("A:A").Delete
    
        .Cells(1, 1).Value = "Name"
        .Cells(1, 2).Value = "Email"
        .Cells(1, 3).Value = "City"
        .Cells(1, 4).Value = "State"
        .Cells(1, 5).Value = "Job Title"
        .Cells(1, 6).Value = "Phone Number"
        .Cells(1, 7).Value = "Department"
        .Cells(1, 8).Value = "Company"
    
        .Columns("A:AH").EntireColumn.AutoFit
    
        .Range("B:B").Select
        .Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        
        .ActiveWorkbook.SaveAs ("C:\List.xls"), 43
    End With
    
    Set objWorkbook = Nothing
    Set objExcel = Nothing
    
    objExcel.Quit

Skip,

_[red][/red]

 

[PHV]

And what about this ?
objExcel.Selection.Specialcells(4).Entirerow.Del' 4 = xlCellTypeBlanks

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[n3tw0rkadm1n1strat0r]

PHV your way worked!!! Except its this...

objExcel.Selection.Specialcells(4).Entirerow.Delete' 4 = xlCellTypeBlanks

You just didn't write out Delete. Thanks.

 

[PHV]

OOps, sorry for the typo (in fact incomplete cut'n'paste).