Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Excel Automation

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    '-------------------------------------------------------
    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

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •