Execute VBA on Excel File From Access VBA

[LaCour]

I am creating an Excel report via VBA in Access. Then, to remove the new line characters in the Excel file, I run:

Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

from Excel.

Is it possible to run this line of code against the *.xls file from the Access VBA?

Let me know if you have thoughts on this.

Thanks
Blair

 

[DougP]

Not sure if this will work but...
Did you look at DDE?
=DDESend("Excel", "Sheet1", "R1C1", "Some text")

there are a bunch of DDE commands

DougP, MCP, A+

 

[Elysium]

Blair,

By setting a reference to the Excel object library in Access, you have the ability to use any of the Excel objects within Access. For example:

Dim xlAPP as Excel.Application
Dim xlWBK as Excel.Workbook
Dim xlSHT as Excel.Sheet

Set xlAPP = New Excel.Application
Set xlWBK = xlAPP.Workbooks.Open( ... workbook path/name ... )
Set xlSHT = xlWBK.Worksheets( ... worksheet name ... )

     xlSHT.Cells.Replace What:=vbCr, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Set xlSHT = Nothing
Set xlWBK = Nothing
Set xlAPP = Nothing

I believe that should work.

Randy