Excel Automation

[W. Baumann]

Excel Automation Problem

(Excel 2000, Automation from Access 2000, Windows 2000 Professional)

Via Automation I format column X as following:

...
objActiveWkb.Worksheets(1).Range("X:X").Select
objXL.Application.Selection.NumberFormat = "#,##0.00"
objXL.Application.Selection.HorizontalAlignment = xlRight
...

Adjusting the horizontal alignment worked fine.
Setting the thousand separator does not work,
neither does the decimal separator completely.

The result looks like:
Saldo-EUR
-224107,39
-14243,97
-13030,46
-3228,9
-45136,95
-3648,51
-4971,6
-3404,26
...

Note the missing thousand separators ("." in some parts of Europe).
Note the missing "0" in eg -3228,9 (should be: -3228,90).

Manually checking the format within Excel shows that
setting the format did happen, but Excel fails to display
the new format.

Exactly the same lines of code worked in other instances.

Pressing "F2" and then "Enter" causes the cell to be displayed
correctly!

I would be very grateful for any suggestions.
Thanks in advance, Wilfried Baumann

[Tom Schreiner]

Maybe I'm mistaken, but it seems you are working with seperate objects?

objActiveWkb

objXL

Please clarify.

Thanks,
Tom

[ This Message was edited by: TsTom on 2002-05-13 03:28 ]

[W. Baumann]

You are right, the most recent version is:
(It should be the same object)

...
objActiveWkb.Worksheets(1).Range("X:X").NumberFormat = "#,##0.00"
objActiveWkb.Worksheets(1).Range("X:X").HorizontalAlignment = xlRight
...

[Tom Schreiner]

Hi W
This may or may not make a difference...
I could not duplicate the problem on my machine.
Can't hurt to try it out.

With objActiveWkb.Worksheets(1).Columns("X:X")

    .NumberFormat = "#,##0.00;#,##0.00"

    .HorizontalAlignment = xlRight

End With

Let me know if this helps or not.
If not, please post the complete procedure.
Thanks,
Tom


[ This Message was edited by: TsTom on 2002-05-13 03:50 ]

[W. Baumann]

'-------------------------------------------------------
Function FinalizeExcel(strWorkdir As String, _
strFilename As String, _
iNumTotal As Integer, _
iNumCustomers As Integer) As Boolean
'Examples:
' FinalizeExcel("c:", "Input.xls", 10, 5)
' FinalizeExcel("c:", "Test.xls", 20, 20)
Dim objXL As Object 'Object variable referencing Excel
Dim ExcelNotActive As Boolean
Dim objActiveWkb As Object
Dim i As Integer
Dim j As Integer
Dim iRes As Integer
Dim strRes As String
Dim strLine As String
Dim blRes As Boolean
Dim fs As Variant 'FileSystemObject
On Error Resume Next
'GetObject-Call without first argument returns a reference to
'an instance of the application. Otherwise an error is thrown.
'Debug.Print strFilename
Set objXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelNotActive = True
Err.Clear 'Delete Err-Objekt
On Error GoTo Err_FinalizeExcel
'Check for Excel:
DetectExcel
'Let object variables point to the correct file:
Set objXL = GetObject(strWorkdir & strFilename)
'Display Excel:
objXL.Application.Visible = True
objXL.Parent.Windows(1).Visible = True
'Process Excel file:
'Debug.Print objXL.Application.Name
'Debug.Print objXL.ActiveWorkBook.Name
Set objActiveWkb = objXL.Application.ActiveWorkbook
'Do some formatting:
objActiveWkb.Worksheets(1).Cells.Select
objXL.Application.Selection.Columns.AutoFit
objActiveWkb.Worksheets(1).Range("A:A").NumberFormat = "0"
objActiveWkb.Worksheets(1).Range("U:U").NumberFormat = "0"
objActiveWkb.Worksheets(1).Range("W:W").NumberFormat = "0"
objActiveWkb.Worksheets(1).Range("W:W").HorizontalAlignment = xlRight
objActiveWkb.Worksheets(1).Range("X:X").NumberFormat = "#,##0.00"
objActiveWkb.Worksheets(1).Range("X:X").HorizontalAlignment = xlRight
objActiveWkb.Worksheets(1).Range("X:X").Copy
objActiveWkb.Worksheets(1).Paste Destination:=objActiveWkb.Worksheets(1).Range("X:X")
objActiveWkb.Worksheets(1).Range("Y:Y").NumberFormat = "#,##0"
objActiveWkb.Worksheets(1).Range("Y:Y").HorizontalAlignment = xlRight
objActiveWkb.Worksheets(1).Range("Z:Z").NumberFormat = "#,##0"
objActiveWkb.Worksheets(1).Range("Z:Z").HorizontalAlignment = xlRight
objActiveWkb.Worksheets(1).Range("A2").Select
objActiveWkb.Worksheets(1).Name = "Customers"
objActiveWkb.Worksheets(1).Select
objActiveWkb.Sheets.Add after:=objActiveWkb.Worksheets(objActiveWkb.Worksheets.Count)
objActiveWkb.Worksheets(2).Name = "Non-Customers"
objActiveWkb.Worksheets(1).Select
'The first row contains the headers:
objActiveWkb.Worksheets(1).Rows("1:1").Select
objXL.Application.Selection.Copy
'Copy headers to second sheet:
objActiveWkb.Worksheets(2).Paste
'The customers should remain on sheet 1
'Non-customers should be copied to sheet 2:
If (iNumTotal - iNumCustomers > 0) Then
'If there are non-customers:
objActiveWkb.Worksheets(1).Rows(CStr(iNumCustomers + 2) & ":" & CStr(iNumTotal + 1)).Select
objXL.Application.Selection.Cut
objActiveWkb.Worksheets(1).Range("A2").Select
objActiveWkb.Worksheets(2).Select
objActiveWkb.Worksheets(2).Range("A2").Select
objActiveWkb.Worksheets(2).Paste
End If
objActiveWkb.Worksheets(2).Select
objActiveWkb.Worksheets(2).Cells.Select
objXL.Application.Selection.Columns.AutoFit
objActiveWkb.Worksheets(2).Range("A2").Select
objActiveWkb.Worksheets(1).Select
objXL.Application.ActiveWindow.TabRatio = 0.307
'Save to new temporary file:
objActiveWkb.SaveAs Filename:=strWorkdir & "tmp.xls", FileFormat:=xlExcel9795
objActiveWkb.Close savechanges:=True
Set fs = CreateObject("Scripting.FileSystemObject")
'Copy temporary file to original file:
fs.CopyFile strWorkdir & "tmp.xls", strWorkdir & strFilename, True
'Delete temporary file:
fs.DeleteFile (strWorkdir & "tmp.xls")
'Debug.Print "Done"
Set objActiveWkb = Nothing
Set objXL = Nothing
FinalizeExcel = True
Exit Function
Err_FinalizeExcel:
MsgBox ("Error: " & Err.Number & " " & Err.Description)
End Function

[W. Baumann]

Problem solved!

Adding the following line solved the problem:
...
objActiveWkb.Worksheets(1).Range("X:X").FormulaR1C1 = objActiveWkb.Worksheets(1).Range("X:X").Value
...

The whole block now looks like:
...
objActiveWkb.Worksheets(1).Range("X:X").NumberFormat = "#,##0.00"
objActiveWkb.Worksheets(1).Range("X:X").HorizontalAlignment = xlRight
objActiveWkb.Worksheets(1).Range("X:X").FormulaR1C1 = objActiveWkb.Worksheets(1).Range("X:X").Value
...

Thanks for the comments, they helped me to
specify the problem better.

Regards, Wilfried