Excel Formatting From Access

[Guirg]

Hey All,

Small bug that keeps popping up im trying to locate Cells with some title and select the whole column and reformat it:

heres what i have so far

For i = 1 To 40
If xlApp.Application.Cells(1, i).Value Like "H" Then
 xlApp.Application.Columns("i:i").Select
 xlApp.Application.Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
End If
 i = i + 1
Next i

not sure why its not working any help would be magic

 

[chergh]

Try this:

For i = 1 To 40
If xlApp.workbooks("BookName").worksheets("SheetName").Cells(1, i).Value Like "H*" Then
 xlApp.workbooks("BookName").worksheets("SheetName").Columns("i:i").NumberFormat = "[$-F400]h:mm:ss AM/PM"
 xlApp.workbooks("BookName").worksheets("SheetName").cells(1,i).numberformat = "General"
End If
 
Next i

This will only work if the column that you want to format title starts with the letter H, seems to be what you were trying to do.

If your formatting you should be referring to a worksheet object not the application object as you were.

When your using i = x to y you don't need to manually increment the value of i.

 

[Guirg]

Hey thanks for that i tried implementing it but its popped up with an error, ill have to disable the error handler to see what it was tho...

 

[Guirg]

This seems to be working but i cant get it to convert columns that have spaces in the headings so ie Cumulative H.... wont work....

For i = 1 To 40
If xlApp.Cells(1, i).Value Like "H*" Then
 xlApp.Columns(i).Select
 xlApp.Selection.numberformat = "[$-F400]h:mm:ss AM/PM"
 xlApp.Cells(1, i).numberformat = "General"
End If
Next i

 

[alktrigger]

if you're doing a lot of formatting, may i suggest the "With / End With" command?

With xlApp
          If .Cells(1,i).value Like "H*" Then
                     .Columns(i).Select
                     .Selection.numberformat =~~~
                     .Cells(1,i).num ~~~
         End If
End With

 

[Guirg]

Hmm that'll work, its not really a big difference beacuse theres only 5 or so colummns that need formatting but they can be in almost any position hence the search through the columns... any suggestions for the titles with spaces?