no need to use select.

your code should work.

that is..
if xlapp is a valid excel instance
and .workbooks(strXLSfile) is a valid workbook
and .worksheets(sn(i)) is a valid worksheet.
and lr is a number between 1 and 2^16
and lc is a number between 1 and 2^8

also note that when you use automation
it is best to avoid with .. end with syntax
if you have problems quitting the instantiated excel
that may be the cause.

it maybe better to use object variables which you can check in the
locals window.. AND can explicitly set to nothing when done.

or combined with a few diagnostic msgboxes :)

dim wkb as object
dim wks as object
set wkb = workbooks(strxlsfile)
if wkb is nothing then msgbox "oops"
set wks = wkb.worksheets(sn(i))
if wks is nothing then msgbox "oops"
if lr<2 or lr 2^16 then msgbox "oops again"
wks.range("a2:a" & lr).numberformat = "dd/mm"
wks.usedrange.sort wks.cells(1), 1, header:=1

set wks = nothing
wkb.save
set wkb = nothing
xlapp.quit
set xlapp = nothing




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


news.sf.sbcglobal.net wrote :

I need to apply date formatting to column A and then sort the entire
worksheet by column A ascending. I don't know how many rows/columns
there will be, but row 1 is always the header row.

I calculate the last row and column like this:

lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
(no problem using SpecialCells here)

I've tried the following VBA code, but no luck:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range("A2:A" & lr).NumberFormat = "mm/dd/yyyy hh:mm;@"
End with

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range(.Cells(2, 1), .Cells(lr, lc)).NumberFormat = "mm/dd/yyyy
hh:mm;@" End with

Why is this not working? The macro recorder returns this:

Range("A2:A106").Select
Selection.NumberFormat = "mm/dd/yyyy hh:mm;@"

Do I need to select the range in VBA??

Thanks in advance.