Exporting a NumberFormat

[Guest]

This is what I have and It will not select the Columns. I must be missing
something?

Sub Format()


' Excel stuff

Dim xlObj As Excel.Application
Dim wks As Excel.Worksheet
Dim r As Long, c As Long, x As Integer

' DAO stuff
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim Range As Excel.Range
Dim aCell As Excel.Range

Dim db1 As Database
Set db1 = CurrentDb
Dim StrTab As String


Set xlObj = CreateObject("excel.application")
xlObj.Visible = True


xlObj.Workbooks.Open "P:\MTAG5\Corning\CustomizedReport\Format
Database\Reports\Corning Rollforward.xls"
xlWb = ("Rollforward Report")
On Error Resume Next

With xlWb
'Range("C", Range("0").End(xlDown)).Select

For Each cell In xlWb
cell.Range("C", Range("0").End(xlDown)).Select
'cell.Font.Size = 10
'cell.Font.Name = "Arial"
'cell.Font.Bold = True
cell.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Next
End With

wks.Range("a1", wks.Range("d1000").End(xlDown)).Select



End Sub

 

[John Nurick]

xlWb = ("Rollforward Report")

'Assign the literal string "Rollforward Report" to the
'undeclared Variant variable xlWb

On Error Resume Next

'Ignore any errors

With xlWb

'Meaningless because xlWB contains a literal string
'and not an object or user-defined type

For Each cell In xlWb

'would rais run time error 424 "object required" if you
'hadn't turned off error handling

cell.Range("C", Range("0").End(xlDown)).Select
'cell.Font.Size = 10
'cell.Font.Name = "Arial"
'cell.Font.Bold = True
cell.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Next
End With

Apart from that, it's almost always safer, faster and better to use the
Excel Range object rather than the Selection object when you're
automating Excel.