Change Imported Format (Excel)

[ebisabes]

I have a macro that changes the format of cells from general to Accounting with 2 decimals. However if my spreadsheet is imported, I can use my macro but still have to F2 every individual cell to actually change the format.

Is there any other way to do this.. or can I make a macro that will F2 a huge area at the same time? I have tons of records & it would take hours to F2 every cell.

 

[SkipVought]

Hi,

Do you have Tools/Options - Calcalation Tab -- set to AUTOMATIC

Changing the column cell format ought to happen virtually immediately.


Skip,

_[red][/red]

 

[ebisabes]

Yes the Automatic is on... & it doesnt re-format... even when I click (Calculate now F9)

 

[SkipVought]

How are you importing this into Excel?

Skip,

_[red][/red]

 

[ebisabes]

From Reportsmith. the numbers in Reportsmith are formatted as 0.00 'number'

 

[SkipVought]

What METHOD are you using to IMPORT into Excel. There is more than one way to do this.

Skip,

_[red][/red]

 

[ebisabes]

in Reportsmith I save the file as excel format

 

[SkipVought]

I'd use Data/IMPORT or Data/Get External Data/New Database Query. You'd probably have greater control on the FORMAT.

Skip,

_[red][/red]

 

[SkipVought]

Trouble is, you are not IMPORTING anything.

Reportsmith is EXPORTING as an Excel workbook. Some formats might be suspect.

Skip,

_[red][/red]

 

[ebisabes]

so 'NO'??????

there is no way to 'F2' mass amounts of cells at a time to change the format??? does anyone know??????

 

[SkipVought]

paste this into a module, select the column(s) and run...

Sub TickleData()
  Dim r As Range
  With Selection.Parent.UsedRange
    For Each r In Range(Cells(.Row, Selection.Column), Cells(.Rows.Count + .Row - 1, Selection.Columns.Count + Selection.Column - 1))
      With r
        .Value = .Value
      End With
    Next
  End With
End Sub

Skip,

_[red][/red]