Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Question Unanswered: Running code from Access using Excel Automation

    Running code from Access using Excel Automation
    I have the following code:


    Code:
    Sub MakeBold() 
        Dim Rng As Range 
        Dim c As Range 
        Dim Length As Integer 
        Dim LeftSide As Integer 
        Dim RightSide As Integer 
        Set Rng = Range("D6:D" & Range("D65536").End(xlUp).Row) 
        For Each c In Rng 
            LeftSide = InStr(c, "|") 
            RightSide = InStr(LeftSide + 1, c, "|") 
            Length = RightSide - LeftSide 
            If Length > 0 Then 
                c.Characters(Start:=LeftSide, Length:=Length).Font.FontStyle = "Bold" 
            End If 
        Next c 
    End Sub
    I have a report in Access that needs to export to Excel, in order to do this I am using Automation to export the report as an RTF document. I then open this document and select all, copy, then open excel and paste the data I need.

    There is an issue with Access and exporting whereby it doesn't export formatting. I need to run the code above on a column of data. Is there anyway to code this in automation to run, or do I have to have that macro in a worksheet and call it?

    Any ideas would be great!

    Ken

  2. #2
    Join Date
    Apr 2002
    Posts
    139

    Re: Running code from Access using Excel Automation

    Ken,

    Haven't the code readily available for you, but Yes, should be possible.
    Your strategy basically should be as follows:

    1) Use Automation to create an instance of Excel;
    See Access Help on the topic "CreateObject"
    If you are new to Creating objects, this will require some study.
    2) Export your report to the Excel instance;
    3) Next modify your make-up code, normally involves the same
    statements, but with a prefix to your Excel-object you created.

    hth

Posting Permissions

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