Unanswered: Running code from Access using Excel Automation
Running code from Access using Excel Automation
I have the following code:
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"
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?
Re: Running code from Access using Excel Automation
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.