Formatting and sorting cells with VBA

[news.sf.sbcglobal.net]

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).Column
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.

 

[Guest]

Are there any errors with the following:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range("A2:A" & lr).Select
End with

or

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range(.Cells(2, 1), .Cells(lr, lc)).Select
End with

If you have errors with the above then you are not identifying the ranges
correctly.

 

[deko]

Are there any errors with the following:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range("A2:A" & lr).Select
End with

or

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range(.Cells(2, 1), .Cells(lr, lc)).Select
End with

If you have errors with the above then you are not identifying the ranges
correctly.

news.sf.sbcglobal.net is me - deko...

Thanks for the reply - I got it working... but now I have other problems.

If the column is formatted as text, it appears correctly in the chart X-axis
labels - but after I format as date, only numbers (row numbers) appear in
the X-axis labels. Any idea why this would be?

 

[keepITcool]

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 :

 

[deko]

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
10-4

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.

Now that's something I've not heard before.

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

I've purposely avoided using object variables because that is known to cause
problems (in automation scenarios) of Excel not quitting.

I did get it working, but the problem is that now that the cells are
formatted as dates, the labels in my chart (created from the same data) do
not appear correctly. I get row numbers instead of the nicely formatted
string I had before. Still troubleshooting...

 

[deko]

My code broke again - or perhaps it was never fixed

But I think I know what's going on. I'm using a query to populate a series
of worksheets, like this:

strSql = "SELECT * INTO [Excel 8.0;Database=" & strXlsPath & "].[" & _
strSheetName & strTestType & "] FROM tblExcelData;"
db.Execute strSql, dbFailOnError

The field in tblExcelData that contains the date is text field (there is a
reason for this). For some reason, the cell in the worksheets contains
this:

'10/12/04 07:20

While the Access table field contains only this:

10/12/04 07:20

Why the leading single quote? How did it get there and how do I get rid of
it?

 

[deko]

By the way, the reason for the text field (rather than date) is because the
chart that is created from this data gets it X-axis labels from the column
with the date values - and only the date (10/12/2004) shows in the X-axis
rather than the date and time (10/12/2004 13:23) when the columns is
formatted as date. With a string, I get the time as well as the date.

 

[deko]

will repost this as new question...